erDiagram
CUSTOMER ||--o{ ORDER : places
CUSTOMER ||--o{ ADDRESS : has
ORDER ||--|{ ORDER_LINE : contains
ORDER }o--|| ADDRESS : "ships to"
ORDER ||--o| PAYMENT : "settled by"
PRODUCT ||--o{ ORDER_LINE : "appears in"
PRODUCT }o--|| CATEGORY : "belongs to"
CUSTOMER {
uuid id PK
string email UK
string status
timestamp created_at
}
ORDER {
uuid id PK
uuid customer_id FK
uuid ship_to_id FK
string status
numeric total_minor
string currency
}
ORDER_LINE {
uuid id PK
uuid order_id FK
uuid product_id FK
int quantity
numeric unit_price_minor
}
PAYMENT {
uuid id PK
uuid order_id FK
string provider_ref UK
string state
}What it is
Entities, their key attributes, and — the part that carries the information —
the cardinality and optionality on each end of every relationship. ||--o{ says
one customer has zero or more orders and every order has exactly one customer.
That single notation encodes a business rule that three paragraphs of prose
usually fail to state unambiguously.
When you produce it
Whenever a relational store is being designed or reverse-engineered, and always before a data migration, because the target's cardinality rules are what the source data will violate.
Who reads it
Engineers writing the schema. Data engineers writing the extract. Anyone migrating, who reads it as a list of constraints their legacy data will fail.
What good looks like
- Cardinality and optionality on both ends of every line. "One to many" without optionality is half a statement.
- Keys marked — primary, foreign, and unique business keys, which are the ones that actually cause migration failures.
- Only the attributes that matter to the reader. A full column list belongs in the DDL, not the diagram.
- Relationship labels read as a sentence in one direction.
Common mistakes
- Modelling the object graph instead of the tables. Inheritance and collections do not survive the trip; decide the mapping and draw the result.
- Omitting the join tables. A many-to-many with attributes is an entity and hiding it hides where the interesting data lives.
- Every attribute nullable. If the diagram cannot say what is required, it is not constraining anything.