Learn

Architecture & pattern guides

Read the idea, build it on a Grid, prove it in a challenge. Short, practical guides — illustrated with RodGrid's own nodes — on system design, design patterns, flow modeling, and how to get the most out of the app.

All articles

Entity-relationship modelling

Entities and their keys, weak entities, relationships, cardinality with its optional side, and the join entity of a many-to-many: the data half of low-level design.

Line 3 of what?

A row in a shop's database reads 991, 3, MUG-044, 2, 19.90. On its own it is a puzzle: it does not say what kind of thing it is, which part of it tells it apart from every other row, or what it belongs to. An entity-relationship (ER) diagram settles those questions for the whole schema before any table exists: which things the system stores, how it tells them apart, and how many of each go with how many of another.

Entities and the key that tells them apart

An entity is something the system stores and can identify: a Customer, an Order, a Product. Its identifying attribute or set of attributes, the key, is what makes two rows different. Two customers can share a name and a birthday; they cannot share an id, which is why the diagram marks it PK, the primary key. The other attributes are the facts stored about the entity, such as an email or a creation date. When one entity holds another's key, as an Order holds customer_id, that column is a foreign key (FK), and it is how a relationship ends up stored in a table.

Read the row again. 991 is an order's key. 3 is not the key of anything by itself, because every order with three lines has a line 3. The row is an OrderLine, and an OrderLine is a Weak Entity: it cannot be identified on its own. It is "line 3 of order 991", and without the order it means nothing, so its key is borrowed: the order's id plus its own line number.

An order and its lines

The double border marks the weak entity: its identity is borrowed from Order.

The relationship that lends a weak entity its key is its identifying relationship: Order → OrderLine above, where order_id is part of OrderLine's key. Every other relationship is non-identifying. Customer → Order is one: an order has an id of its own, and customer_id is a plain foreign key, outside that key.

How many, and whether at all

Cardinality is the point of the diagram, so make it visible. The crow's foot marks the many side; write 1, 0..1 or 1..* as a label when the exact bound matters. "A customer places many orders, an order belongs to exactly one customer" is a sentence the diagram should let a reader recover without asking.

Each end of a relationship answers two questions: at most how many, and at least how many. The second is the one people skip, and it is where the product decisions hide. Can a customer exist before placing an order? In a shop with sign-up, yes, so the order end is 0..*, not 1..*. Can an order exist without a customer? With guest checkout, yes: the customer end becomes 0..1 and customer_id must be nullable. Without guest checkout it is 1, and the column is NOT NULL.

Order → OrderLine is usually 1..*, since an order with no lines means nothing. Notice what enforces that, though. A foreign key keeps every line attached to a real order, but nothing in a plain schema stops an order from having no lines at all, so that minimum is a rule the application or a trigger has to keep. The diagram should still show it: it is how the next developer learns the rule exists.

Noteprice_at_purchase sits on the line rather than behind a join back to Product because it is a different fact, what was charged then; Normalisation & denormalisation covers telling such a fact from a copy.

Posts and tags: when the line needs a box

A post carries many tags, and a tag labels many posts. In a table-level diagram that many-to-many cannot stay a bare line, because no column on either side can hold "many": it needs a table of its own. Posts and tags meet in a join entity, PostTag, whose rows are pairs of keys (in a Chen diagram, where each Attribute is an oval, the Relationship diamond represents the many-to-many and maps to this join table).

Posts, tags and what joins them

PostTag's key is its two foreign keys together, so a post cannot carry the same tag twice.

The moment that join grows an attribute (tagged_at, tagged_by), it is plainly an entity in its own right: the attribute describes the pairing, so it lives on PostTag, not on Post or Tag. Both ends here are 0..*: a post may have no tags yet, and a tag may be in no post's list.

NoteOn a Challenge Grid there are no crow's feet and no compartments: write the cardinality in the connection's label (1 : 0..*), mark each key in the entity's name (Post (id PK)), and show the identifying relationship by drawing the dependent side as a Weak Entity and writing identifying in that connection's label.

Mistakes that cost a migration later

  • Leaving a many-to-many as a bare line in a table-level (crow's-foot) diagram — a relational schema cannot store it, so draw the join entity. In a conceptual Chen diagram the Relationship diamond represents the many-to-many and maps to that join table, and it may carry attributes of its own.
  • Writing a minimum the business does not have. A 1 where the answer is "possibly none" turns into a constraint, such as a NOT NULL customer_id, that rejects real data like the guest order; a 0 where a rule exists tells whoever builds the schema that there is no rule, so nothing gets built to enforce it.
  • Confusing an ER diagram with a class diagram. Entities have no operations — if you are reaching for a method, you are drawing the other diagram, the one The class box: name, attributes, operations describes.

Model the blog, then question it

Open Blog Schema and model Author, Post, Comment and Tag, with a join entity between posts and tags. Then label every connection with its minimum as well as its maximum, and check each 1 against a real case: an author with no posts yet, a post nobody has commented on, a tag no post uses. Wherever the honest answer is "possibly none", the minimum should be 0.

Practice this