6 min read

How to read Crow's Foot notation

Crow's Foot is the most widely used notation for Entity Relationship Diagrams (ERDs). Each entity is a box, each relationship is a line, and the symbols at the ends of the line tell you how many of one entity can be linked to the other.

The four symbols

Each end of a line carries two marks. The mark nearest the box is the maximum: a bar means one, a crow's foot means many. The outer mark is the minimum: a circle means zero (optional), a bar means one (mandatory). Together they give four combinations.

Zero or one (circle + bar): an employee is assigned at most one parking bay, and may have none.

Exactly one (bar + bar): every country has one capital city, no more and no fewer.

Zero or more (circle + crow's foot): a customer can place many orders, or none yet.

One or more (bar + crow's foot): a department always employs at least one employee.

Read every relationship in both directions

The symbol next to an entity answers the question "how many of this entity for one of the other?" So in the Customer and Order diagram above, read it twice:

• Each customer places zero or more orders (read the symbol at the Order end).
• Each order is placed by exactly one customer (read the symbol at the Customer end).

Students often read the symbol at the wrong end. Saying both sentences out loud is the quickest way to catch that.

One-to-many in a real database

This is the ERD of the sample store used in the SQL challenges. Every foreign key (FK) sits in the entity on the "many" side of its relationship: an order stores the customer_id of the one customer who placed it.

Many-to-many relationships

A student takes many courses, and a course has many students. At the conceptual level you can draw that as a crow's foot at both ends. A relational database can't store it directly, because neither table can hold a list of the other's keys.

Resolve it with an associative entity between the two. Here, Enrolment holds both foreign keys, and together they form its primary key. The many-to-many becomes two one-to-many relationships, and facts that belong to the pair, such as a grade, now have a home.

Common mistakes

• Making an attribute into an entity. If something only describes another thing (a venue's capacity, a patient's blood type), it's an attribute.
• Putting the foreign key on the wrong side. It always goes on the many side.
• Forgetting the minimum. "Many" is only half the answer: decide whether zero is allowed.
• Redundant lines. If two entities are already connected through a third, a direct line usually adds nothing.