Entity-relationship diagrams
A picture of the things a system stores and how they relate. Old, small, and still the fastest way to find out that two people mean different things by the word "order".
9 min readER - crow's foot1 of 3
01What it shows
An ER diagram describes the things a system stores and the connections between them. Three ingredients, and no more:
| Element | Notation | What it means |
|---|---|---|
| Entity | a named box | A kind of thing worth storing. Singular noun - Customer, not Customers - because a box represents the type and each row is one of them. |
| Attribute | a row inside the box | A fact about the entity. In crow's foot notation these live inside the box; in the older Chen notation they hang off it in ovals. |
| Relationship | A connection, with its cardinality drawn on each end. The symbols say how many of the entity at that end can take part. |
The value is almost entirely in the third one. Everybody can list the entities in their domain; the argument starts at "can an order exist without a customer?", and an ER diagram forces that question to be answered in a symbol rather than left comfortably vague in a document.
02Keys are the part that matters
Attributes are easy. Keys are where an ER model earns its place, because a key is a claim about identity - and identity is where domain misunderstandings hide.
| Element | Notation | What it means |
|---|---|---|
| Primary key (PK) | marked PK, listed first | The attribute, or set of attributes, that identifies one row. Every entity has exactly one. |
| Foreign key (FK) | marked FK | An attribute holding another entity's key. Every relationship line corresponds to a foreign key somewhere, on the "many" side. |
| Natural key | a real-world attribute | An ISBN, an IBAN, an email address. Meaningful, and hostage to the outside world changing its mind. |
| Surrogate key | generated id | A number or UUID with no meaning. Stable by construction, and the default choice in most systems for exactly that reason. |
| Composite key | two or more attributes | Identity that needs more than one column. Common on junction entities, where the pair of foreign keys is the identity. |
03Cardinality, briefly
Each end of a relationship carries two symbols: the outer one says how many and the inner one says whether it is optional. A bar is one, a crow's foot is many, and a circle is zero.
| Element | Notation | What it means |
|---|---|---|
| Exactly one | Bar, bar. Mandatory and single at both ends. | |
| Zero or one | Circle then bar. Optional and single. | |
| One or more | Bar then crow's foot. Mandatory and multiple. | |
| Zero or more | Circle then crow's foot. The most common end in practice. |
The symbol is read at the end nearest the entity it constrains, which is the opposite of what most people guess.
That last point catches nearly everyone, and it has an article of its own - crow's foot notation - covering which end is which, identifying versus non-identifying relationships, and how to resolve a many-to-many.
04Conceptual, logical, physical
Most confused ER diagrams are two levels wearing one coat. Deciding which you are drawing settles a dozen small arguments before they start.
| Element | Notation | What it means |
|---|---|---|
| Conceptual | boxes and lines only | Entities and relationships in the business's own words. No keys, no types, often no attributes. Fits on one page, and is the one a stakeholder reviews. |
| Logical | attributes and keys, no types | Normalized, keyed, and independent of any particular database. Many-to-many relationships resolved. This is the design. |
| Physical | tables, columns, types | Named the way the database names things, with types, indexes, and whatever denormalization the workload actually justifies. |
You do not always need all three. A small service can go straight to logical. What never works is showing a conceptual diagram to a developer who needs the physical one, or a physical one to a business stakeholder who wanted to check whether a customer can have two addresses.
05ER diagram or class diagram?
They look similar and mean different things. A class diagram describes types in a program: behaviour, inheritance, visibility, navigability. An ER diagram describes data at rest: keys, cardinality, and referential integrity. The overlap is real, and so is the divergence.
| Element | Notation | What it means |
|---|---|---|
| Operations | class diagram only | Entities have no methods. Rows do not do things. |
| Keys | ER only | A class diagram has object identity for free; a database has to be told what identity is. |
| Inheritance | class diagram natively | ER models it as a supertype-subtype structure, and the physical model has to pick one of three table layouts to implement it. |
| Many-to-many | drawable in both | A class diagram can leave it as one line forever. A logical ER model must resolve it into a junction entity, because a database cannot store it otherwise. |
Reach for it when
- The subject is what gets stored, and the audience includes a DBA
- You need to settle optionality and cardinality precisely
- The output is a schema, a migration, or a set of constraints
- A business stakeholder has to confirm the domain vocabulary
Reach for something else when
- The subject is behaviour or a type hierarchy - use a class diagram
- You are documenting an API's payloads rather than storage
- The store has no schema and the shape genuinely varies per document
- It is three tables and the DDL is shorter than the diagram
06Common mistakes
- Plural entity names.
Customeris the entity;customersis the table. Mixing them makes relationship sentences unreadable. - Unresolved many-to-many in a logical model. No database can store it. Resolve it and name the junction entity after what it means -
Enrolment, notStudentCourse. - Every relationship optional. A model where nothing is mandatory encodes no rules at all, and the constraints end up scattered through application code instead.
- Attributes that are really entities.If "address" needs five sub-fields and can occur twice, it is an entity.
- Modelling reporting tables. A denormalized read model belongs in the physical model with a note saying why, not in the logical one where it will be mistaken for the domain.
In one line each
- 01Entities, attributes, relationships - and the relationships are where the value is.
- 02A key is a claim about identity; prefer surrogate keys and constrain the natural one.
- 03Cardinality is read at the end nearest the entity it constrains.
- 04Conceptual, logical and physical answer different questions for different people.
- 05A class diagram describes types in a program; an ER diagram describes data at rest.
- 06Read every line aloud in both directions - the cheapest review that exists.
Related reading