Entities and relations

An entity is a real-world object such as an employee, a project, a team, or a client. It refers to the concept itself rather than a row in a table, so the same employee appearing in both a participation record and a team roster is still one entity.

A relation holds how two entities connect. An employee joining a project, or a project working with a client, is a connection with a direction and a name. A join is a one-off link rewritten with every question, while a relation stays in the model and serves the next question as it is.

An entity is a concept, not a table

The properties you define on an entity are not the column list of one table but the qualities the concept has. If an employee entity has a team and a start date, which table those values come from is a later question.

The distinction shows itself when there are several sources. Employee information split across three tables still makes one entity, and each of the three tables loads into it. Move tables straight across into entities and you get as many concepts as tables, which brings the problem of counting the same employee three times into the model itself.

flowchart LR
    accTitle: Three source tables converging into one employee entity
    accDescr: An employee roster, an assignment history, and a certification record each load into a single employee entity. Three source tables still make one entity.
    T1[Employee roster] -->|load| E[Employee entity]
    T2[Assignment history] -->|load| E
    T3[Certification records] -->|load| E

A relation stays in the model

A join exists only inside a query. Whoever knows which columns line up writes it again each time, and that knowledge never leaves the query text.

A relation is stored in the model as a resource with a name and a direction. Give the connection "an employee joins a project" a name such as join_project and the next person does not have to work out which columns to match. Direction matters for the same reason: the connection between an employee and a team means something different depending on which side belongs and which side holds, and the model has to remember that difference.

Identifier keys make relations possible

An entity needs an identifier key that distinguishes its instances. The key is both the basis for not inserting the same object twice and the grounds on which a relation points at each end.

flowchart LR
    accTitle: How a relation points at both ends through identifier keys
    accDescr: The employee entity carries emp_no and the project entity carries prj_cd as identifier keys. The join_project relation receives both key values as reference columns to point at each end, while the relation row itself is distinguished by the structural column id that the system manages.
    S[Employee entity<br/>key emp_no] -->|source key| R[join_project relation<br/>row keyed by structural id]
    R -->|target key| T[Project entity<br/>key prj_cd]

• Entity instances are updated by identifier key, so the same key arriving again produces an update rather than a new row.
• A relation receives the identifier key values of its source and target entities through reference columns. If either side lacks an identifier key, the relation cannot be created at all.
• The relation row itself is distinguished by the structural column id that the system manages. The key you choose belongs to the entity, not to the relation.

This is why choosing an identifier key is the first place modelling tends to stall. Until you decide what determines that two records are the same object, neither loading nor relations can proceed.

Entity or property

Not every noun needs to be an entity. Two criteria help.

First, check whether the object is queried on its own. If the client a project serves is only ever used as a name, a property is enough; if people ask what else hangs off each client, an entity is right.

Second, check whether it connects to other objects. A value that never sits at the end of a relation is better kept as a property, which keeps the model light. More entities buy expressiveness at the cost of more to load and more identifier keys to manage.