DataslopeEntity Relationship Diagrams
Reading and drawing ER diagrams — the standard picture of a database's tables, keys, and the relationships between them.
Once you can spot entities, you need a way to draw them so the whole design is visible at a glance. The standard picture is the entity relationship diagram (ERD). It shows each entity as a box, its attributes inside, and lines for the relationships — the blueprint of a database.
Anatomy of an ER diagram
Every ER diagram has the same three ingredients:
- Boxes are entities (which become tables):
customers,orders. - Rows inside a box are attributes (columns), with their type
and any key marker —
PKfor primary key,FKfor foreign key. - The line between boxes is a relationship, labelled with a verb ("places").
Reading the crow's-foot symbols
The small shapes at the ends of a relationship line tell you how many rows take part. They are called crow's-foot notation because the "many" symbol looks like a bird's footprint.
Read each end separately:
||— exactly one (a straight bar).o{— zero or many (a circle plus the crow's foot).|{— one or many.
So customers ||--o{ orders reads: "one customer relates to
zero or many orders" — one-to-many, the most common kind.
Read the line from both ends
Always read a relationship in both directions. customers ||--o{ orders is "one customer has many orders" and "each order
belongs to exactly one customer." Saying both out loud is the
fastest way to confirm the diagram matches reality.
The three shapes of relationships
Every relationship you will model is one of three cardinalities:
- One-to-one: each row pairs with at most one row on the other side. Rare; often the two could be a single table.
- One-to-many: the workhorse. One parent, many children — handled by a foreign key on the "many" side.
- Many-to-many: both sides can have many — handled by a junction table, which an ERD shows as two one-to-many lines into a middle box.
From diagram to tables
An ERD maps directly onto CREATE TABLE statements. Here is the
customers/orders diagram above, turned into real tables and
queried:
The PK markers became PRIMARY KEY; the FK became REFERENCES;
the ||--o{ line became the customer_id column on the "many"
side. The diagram and the schema are two views of the same design.
A fuller model
Bringing several entities together, a small shop might look like this — products, customers, orders, and a junction table linking orders to products:
Trace the lines: a customer places many orders; each order contains
many order_items; each product appears in many order_items. The
many-to-many between orders and products is resolved by the
order_items junction in the middle — exactly the pattern from the
relationships pages, now drawn formally.
Check your understanding
In an ER diagram, what does a box represent?
A single row of data.
An entity, which typically becomes a table, with its attributes listed inside.
A relationship between two tables.
A query.
The notation customers ||--o{ orders means:
One customer relates to exactly one order.
One customer relates to zero or many orders, and each order belongs to exactly one customer.
Many customers relate to many orders.
Customers and orders have no relationship.
When a many-to-many relationship is turned into actual tables, how is it usually represented in the diagram and schema?
Hint: a foreign key can express "many" on only one side, so the database needs an extra table in the middle.
By merging the two entities into one combined box.
As two one-to-many relationships pointing into a junction (middle) entity.
By drawing a single line with no key columns on either entity.
By removing the relationship line entirely.