How to Read an ER Diagram

An ER diagram is not read left to right. It is read in passes: boxes first, then keys, then edges, then cardinality at each end. The method takes minutes on a familiar schema and a bit longer on a new one, and it scales from six tables to sixty.

1. Start with the boxes

Each box is a table. Read the names without following lines. A box with no edge is either a lookup table waiting to be referenced or a table nobody wired up; both are worth noticing. Plural and singular names describe the same thing, so do not infer cardinality from grammar.

For each box, finish this sentence: one row is one ___. One row in orders is one order. One row in order_items is one product line on one order. That grain explains most of the confusion that follows.

2. Find the primary key

Look for the PK badge. One badged column means identity is that value. Two or three badged columns mean identity is the combination, and neither column is unique alone. In order_items, a product appears in many orders and an order holds many products, but the pair appears once.

If the primary key is an id column, identity is database-managed. If it is a natural value such as email, ask what happens when that value changes.

3. Follow each edge to the foreign key side

Edges are not symmetric. One end is referenced, the other holds the foreign key. Find the column badged FK; that table is the child, and the edge points at it. When there is no arrow, the FK column is still the tie-breaker.

Say the edge with the column in it: order_items.order_id references orders.id. If you cannot name the column pair, you have not finished reading the edge.

4. Read cardinality at both ends

Crow's foot markers, min-max labels and plain 1:N labels answer the same two questions at each end: maximum and minimum. A bar means at most one, a foot means many, a circle means zero allowed, and no circle means at least one.

On customers to orders, the orders end is zero or many and the customers end is exactly one. Read both directions: one customer places zero or many orders; each order is placed by exactly one customer. Every edge produces two sentences, and both must be true.

A bare N usually means zero or many, but a foreign key never forces a parent to have children, so a child-side minimum of one is a business rule the database does not enforce. A bare 1 is usually exactly one, enforced by NOT NULL.

5. Recognize join tables

A table whose primary key is made of two foreign keys is a join table. It implements a many-to-many the database cannot store directly. Its other columns are attributes of the relationship: quantity on an order line, role on a membership, grade on an enrollment.

The pattern is two FK badges, two edges leaving in different directions, and a PK badge on both FK columns. If the join table has its own id, pair uniqueness must still appear as a UNIQUE constraint.

6. Separate optional from mandatory

A nullable FK means a child may have no parent, so the parent end is zero or one. A NOT NULL FK means every child has a parent, so the parent end is exactly one. Nothing in an ordinary FK requires a parent to have children, which is why the child end is normally zero or many.

A UNIQUE constraint on the FK changes the maximum at the child end to one. That is the standard one-to-one: payments.order_id UNIQUE means an order has at most one payment, which may not exist yet.

7. Read PK, FK and U together

• PK alone: the table's identity.
• FK alone: a reference; many rows may point the same way.
• FK with U: at most one reference, so a one-to-one.
• PK with FK: the join-table signature.
• Several PK badges: one composite key, not independent uniques.

A walkthrough: six tables in an online shop

The schema below is enough to practise every step. Read the diagram first, then check it against the DDL.

CREATE TABLE customers (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id)
);

CREATE TABLE products (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE order_items (
  order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id BIGINT NOT NULL REFERENCES products(id),
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, product_id)
);

CREATE TABLE payments (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id BIGINT NOT NULL UNIQUE REFERENCES orders(id),
  paid_at TIMESTAMPTZ
);

CREATE TABLE reviews (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id),
  product_id BIGINT NOT NULL REFERENCES products(id),
  UNIQUE (customer_id, product_id)
);

customers to orders is 1:N through orders.customer_id. One customer places zero or many orders; each order has exactly one customer. NOT NULL makes the customer side mandatory. The U on email prevents duplicate accounts but does not touch the edge.

orders to order_items is 1:N through order_items.order_id, half the composite key. One order holds zero or many lines; each line belongs to exactly one order. ON DELETE CASCADE says the lines disappear with the order. The schema permits an empty order even if the application does not.

products to order_items is 1:N through product_id. One product appears in zero or many lines; each line points at one product. A price stored on the line is the price at purchase time, not the product's current price, and the edge does not say otherwise.

payments.order_id is NOT NULL and UNIQUE. UNIQUE caps the child at one, so this is one-to-one. Every payment belongs to one order, and an order has at most one payment; the order side is optional because the row may arrive later. A nullable paid_at records payment state, not a nullable relationship.

customers to reviews and products to reviews are two ordinary 1:N edges. A customer writes zero or many reviews, each with exactly one customer. A product collects zero or many reviews, each with exactly one product. UNIQUE (customer_id, product_id) allows at most one review per customer per product; that is a rule on the pair, not a third table.

There is no orders-to-products edge. Products reach orders through order_items. Adding orders.product_id would collapse a many-to-many and lose the ability to buy several products at once.

Practice checklist

• Name each box and state its grain in one sentence.
• Mark each PK, including composite ones, before following edges.
• Write the exact FK column pair on every edge.
• Read each edge in both directions.
• Check the minimum: a circle or a nullable FK means zero is allowed.
• Look for a U on an FK, and for a PK made of FKs.
• Note delete rules: CASCADE, SET NULL, RESTRICT.
• Compare the drawing with the DDL when both exist.

Common mistakes and misreadings

• Reading a foot as 'this table has many rows' instead of 'many rows here per one row there'.
• Assuming the FK is on the left-hand table. It is on the child, wherever the box sits.
• Treating a nullable FK as optional on the parent side; nullability describes the child.
• Concluding one-to-one from sample data. Only a UNIQUE constraint proves it.
• Missing composite keys and reading each half as independently unique.
• Treating an arrowhead as cardinality. It shows FK direction.
• Expecting a foreign key to enforce 'an order must contain at least one item'.

What if the diagram has no cardinality labels?

Reconstruct them from the schema: find the FK, check nullability, check for UNIQUE, and check whether the FK columns are part of the child's primary key. Ask for the DDL when certainty matters; an unlabeled diagram is a sketch.

How do I tell one-to-many from many-to-one?

They are the same edge viewed from opposite ends. What matters is which table stores the foreign key, and that does not change with the drawing direction.

What does a self-referencing edge mean?

A table referencing itself. employees.manager_id references employees.id: one manager has many direct reports, and each employee has zero or one manager. Trees such as categories and folders use the same shape.