Crow's foot notation, explained properly

Crow’s foot notation is how an ER diagram states how many records on one side of a relationship can relate to records on the other. It is the notation most teams read fluently, and it takes about five minutes to learn.

Read the end nearest the box

This is the part people get wrong. The symbols at the end of a line describe the entity that end is touching.

A line from Customer to Order with a crow’s foot at the Order end means: one customer has many orders. The mark is next to Order, so it is telling you how many Orders there can be.

The four marks

Each end carries two symbols: one for the maximum, one for the minimum.

Crow’s foot — the three-pronged fork. Many.

Single bar — one line across. One.

Circle — a small open circle. Zero. Optional.

Second bar — a second line across. At least one. Mandatory.

The four combinations you will actually use

Marks	Reads as
Bar, bar	Exactly one
Circle, bar	Zero or one
Circle, crow’s foot	Zero or more
Bar, crow’s foot	One or more

“Zero or more” is by far the most common. A customer may have no orders yet, and that has to be allowed or you cannot create a customer.

Reading a relationship out loud

A relationship should make a sentence in both directions. Take an order line between Customer and Order, with one at the Customer end and zero-or-more at the Order end:

• A customer places zero or more orders.
• An order is placed by exactly one customer.

Reading it both ways is the quickest way to catch an error. If one direction sounds wrong, the marks are wrong.

Many-to-many

A relationship with a crow’s foot at both ends is legitimate in an ER diagram but it cannot be built directly in a relational database. You need a join table.

Rather than draw the many-to-many and work it out later, draw the join table from the start:

Order — one to many — Order item — many to one — Product

The join table almost always turns out to need attributes of its own. In this example, the quantity belongs on Order item, and there is nowhere sensible to put it in a bare many-to-many. Drawing it explicitly finds that immediately.

Mistakes that get through review

Marks on the wrong end. Draw it, then read the sentence both ways. This catches nearly everything.

Everything mandatory. If every relationship says “one or more”, you cannot create anything, because each record needs the other to exist first. Ask what has to be true at the moment a record is first created.

Unlabelled relationships. A line with no verb makes the reader guess. “places”, “contains”, “belongs to” — one word each, and the diagram becomes readable prose.

A solid line with no key to explain it. A solid line is an identifying relationship: the parent’s key is part of the child’s key, as an order line is “line 3 of order 1042”. Show that, by marking the column both PK and FK in the child. Where the child has a key of its own, the line is dashed, even if the child cannot exist without its parent: an order always has a customer, but it is known by its own order number.

Optional versus mandatory is a design decision

The circle is not decoration. Deciding whether a relationship is optional is deciding whether your database will accept a half-complete record.

If an order must always have a customer, the bar goes on the Customer end and the foreign key is NOT NULL. If you might take a guest order with no customer record, it is a circle and the column is nullable. That single mark determines a real constraint, and settling it on the diagram is far cheaper than discovering it in a failing insert three weeks later.

A sheet to keep beside you

The four combinations, the two kinds of line, and the characters Mermaid uses for each are on the printable sheet below. The worked examples use all of them, in eight complete models.