What is an ER diagram?
Entities, attributes, keys, cardinality and how to read crow's-foot notation, explained without reference to any particular tool.
· updated
An entity-relationship diagram (an ER diagram, or ERD) is a map of the things a system stores and the connections between them. Draw one for an online shop and you get boxes labelled Customer, Order, Product and Payment, with lines saying that a customer places many orders, and that an order contains many products.
It is the usual way to describe a database before it exists, and to explain one that already does. The notation is small enough to learn in an afternoon, which is most of why it survived: a developer, a DBA and the person who actually understands the business all read it the same way.
If you would rather look at a finished one first, the ER diagram template is a
five-table shop schema — customers, orders, products, order_items and addresses — that you can
open and edit in the browser. The rest of this page is the notation it is drawn in.
Entities
An entity is a type of thing the system needs to remember. Customer, Order, Invoice, Vehicle, Booking.
Two questions separate an entity from a mere fact about one:
- Does it exist independently? A customer exists whether or not they have placed an order. A postcode does not; it is a fact about an address.
- Do you store more than one thing about it? If the only thing you will ever record is the value itself, it is an attribute, not an entity.
Keep the distinction between an entity and an instance of it. Customer is the entity; “customer 4471, Amina Yusuf” is one instance. An ER diagram only ever draws entity types. In a physical database, one entity is normally one table and one instance is one row.
Attributes
Attributes are the facts stored about an entity: email, created_at, total_amount. Each has a data
type, and two properties a reviewer will look for: whether it can be missing (nullable), and whether two
rows may share the same value (unique).
Two attribute shapes usually mean an entity is hiding. Repeating groups — phone_1, phone_2, phone_3 — mean you actually have a Phone entity with many rows per customer. Derived values such as order_total, when it is only the sum of the line items, are a calculation rather than a fact; store one
only if you have a reason, and say on the diagram that you did.
Keys
Keys turn a set of boxes into a connected model.
Primary keys
A primary key uniquely identifies one row of an entity. Every entity should have exactly one, and the values must be unique and never null.
A natural key already identifies the thing in the real world — an ISBN, an email address. A surrogate key is invented purely to be the key, usually an auto-incrementing integer or a UUID. Surrogates are far more common, because natural keys have a habit of changing, being reissued, or turning out not to be unique after all. A composite key is one made of several columns together, and shows up mostly on join tables.
Foreign keys
A foreign key is a column holding a primary key value that belongs to another entity. It is the mechanism a relationship is actually made of; the line on the diagram is a picture of it.
Direction matters, and it is what beginners most often get backwards. The foreign key lives on the “many”
side. A customer has many orders, so orders.customer_id points at customers.id. There is no customers.order_id, because there is no single order to point at.
That is exactly how the shop schema template is built: orders.customer_id is the
row marked FK, and its relationship is drawn from that row to customers.id rather than from the edge
of one box to the edge of the other. Open it and follow one line to see the rule rather than read it.
Relationships
A relationship is a named connection between two entities. Name it as a verb, read in one direction: a Customer places an Order; an Order contains a Line Item.
Most relationships are binary. Two others come up: unary, where a line leaves a box and comes back to it (an Employee reports to an Employee), and ternary, binding three entities at once. Ternary relationships are rare, hard to read, and almost always clearer broken out into an entity of their own.
Cardinality
Cardinality is how many instances on one side relate to one on the other. Each end of a line answers two questions: at most how many, and at least how many — the second is usually called optionality.
| Shape | Means | Typical example |
|---|---|---|
| One-to-one (1:1) | Each side has at most one of the other | Employee and Employment Contract |
| One-to-many (1:N) | One on the left, many on the right | Customer and Order |
| Many-to-many (M:N) | Many on both sides | Student and Course |
Optionality is the part that gets left off, and it carries real information. “An order must have a customer, but a customer need not have an order” is a different model from “every customer has an order”, and the second one makes signing up a new customer impossible.
Reading crow’s-foot notation
Crow’s foot is the most common ERD notation. It puts the cardinality symbols at the ends of each line, two per end: the one touching the box is the maximum, the one just behind it is the minimum.
| At the end of the line | Reads as |
|---|---|
| Two bars | Exactly one |
| A circle, then a bar | Zero or one — optional |
| A bar, then a crow’s foot | One or many |
| A circle, then a crow’s foot | Zero or many — optional |
The crow’s foot itself, the three-pronged fork that gives the notation its name, always means “many”.
The symbol at one end describes the other entity. On a Customer—Order line, the crow’s foot drawn at the Order end means one customer has many orders. Reading each marker against the near box instead of the far one inverts every relationship on the diagram, and it is the first thing to check when a model looks strange. Read aloud, a well-marked line is a sentence: one customer places zero or many orders; one order is placed by exactly one customer.
Many-to-many hides a table
A many-to-many relationship cannot be stored directly. Students enrol in many courses and courses have many students, but there is no column on either table that can hold the other side.
The answer is a join table — Enrolment — holding a foreign key to each side. Draw it explicitly, because
it almost always turns out to have attributes of its own: enrolled_on, grade, withdrawn_at. Those
facts belong to the relationship, not to the student or the course, and have nowhere to live until the join
table is on the diagram.
The ER diagram template has one drawn out: order_items sits between orders and products holding a foreign key to each, and carries quantity and unit_price_cents — facts about the
relationship rather than about an order or a product.
Three levels of detail
Say which one you are drawing: most arguments about ER diagrams are two people drawing at different levels.
| Level | Shows | For |
|---|---|---|
| Conceptual | Entities and relationships, no attributes | Agreeing the domain with non-technical people |
| Logical | Attributes, keys, cardinality | Designing the model |
| Physical | Real names, types, indexes | Whoever writes the migration |
How to read one quickly
- Read the box names. That is the domain vocabulary; if one surprises you, ask.
- Find the primary key in each box. A box without one is either not an entity, or not finished.
- Follow one foreign key: it should point from the many side to the one side. Read the cardinality at both ends aloud, as a sentence.
- Scan for the two smells — a many-to-many with no join table, and a box with no lines at all.
Drawing one
For a conceptual sketch, paper is fine. For anything that will be reviewed or edited again later, use a tool that keeps the diagram editable.
Almost every tool draws crow’s foot, so that is not what to choose on. What matters is how the schema gets in. If you already have one, or something you can convert to DBML — the text format dbdiagram.io popularised — pick a tool that imports it. Hand-typing forty tables is where an ER diagram stops being worth the effort and becomes a chore that quietly goes stale.
diagrams.info is one option: paste DBML, get tables with their keys and relationships drawn, edit them, and get DBML back out. It is free and needs no account. Any of the established tools will draw you a correct ERD too — the notation is the standard, not the software.
To start from a schema rather than an empty canvas, the ready-made ER diagram template opens with the five shop tables above already laid out, keyed and connected. The crow’s-foot markers are the one thing it leaves to you: cardinality is a choice about your domain, and no import can guess it.
Whichever you use, the diagram is only worth having while it is still true. An ERD that no longer matches the database is worse than none, because people believe it.