DiagramDB / Crow’s Foot Notation

Crow’s Foot Notation

Every line in an ER diagram carries two facts at each end: the most related rows there can be (one or many) and the fewest (zero or one). Crow’s foot notation draws both with three symbols.

The four ER diagram symbols

Exactly one (mandatory one)
Two bars. The related row must exist and there is only one. A NOT NULL foreign key points to it.
Zero or one (optional one)
Circle and bar. At most one related row, maybe none: a nullable foreign key.
One or many (mandatory many)
Bar and crow’s foot. At least one related row, possibly many.
Zero or many (optional many)
Circle and crow’s foot. The most common “many” end: a customer may have no orders yet.

Cardinality builder

Name two entities, pick each end, and read the relationship back in plain English and SQL.

one-to-many. Each customer has zero or more orders. Each order belongs to exactly one customer.

ALTER TABLE orders
  ADD COLUMN customer_id INTEGER NOT NULL REFERENCES customers(id);

How to read a relationship line

Stand at one entity and look across the line to the other end. The symbol touching the far entity is the maximum (bar = one, crow’s foot = many); the symbol just inside it is the minimum (circle = zero, bar = one). Then do the same from the other side. Two sentences, one per direction, describe the relationship completely.

From symbols to foreign keys

In a relational database, the foreign key always lives on the “many” side: orders.customer_id, never customers.order_id. The minimum at the parent end decides whether that column is NOT NULL (exactly one) or nullable (zero or one). A one-to-one relationship is a foreign key with a UNIQUE constraint, and a many-to-many relationship needs a junction table. The builder above generates exactly that.

The ER diagram generator draws these symbols from your SQL automatically, and the Mermaid ER diagram page shows the same ideas in Mermaid’s text syntax (||--o{).

Questions

What is crow’s foot notation?

A way of drawing entity relationship diagrams in which each end of a relationship line shows its cardinality with symbols: a three-pronged “crow’s foot” for many, a bar for one and a circle for zero. It is attributed to Gordon Everest (1976) and is the default in most database design tools.

How do I read a crow’s foot relationship?

Read from one entity, across the line, to the symbols at the far end. The symbol nearest the far entity is the maximum; the one just inside it is the minimum. “Each customer places zero or many orders” is read from customer to the circle-and-crow’s-foot at the orders end.

What is the difference between cardinality and ordinality?

Cardinality is the maximum number of related rows (one or many); ordinality, also called modality or optionality, is the minimum (zero or one). Each end of a crow’s foot line shows both.

How is crow’s foot different from Chen notation?

Chen notation, from Peter Chen’s 1976 paper, draws entities as rectangles, relationships as diamonds and attributes as ovals, with 1, N or M written on the lines. It is common in textbooks. Crow’s foot lists attributes inside the entity box and puts the cardinality on the line ends, which is more compact for real schemas.

How do I draw a many-to-many relationship?

Conceptually, with a crow’s foot at both ends. In a physical database diagram you resolve it into a junction (associative) table with two one-to-many relationships, e.g. students —< enrolments >— courses.

What do dashed lines mean?

In many tools a solid line is an identifying relationship (the child’s primary key includes the parent’s key) and a dashed line is non-identifying (the child has its own key and just stores the parent’s key as a foreign key).