Cardinality: Crow's Foot Guide

This guide shows the four relationship patterns you will meet in almost every database: one-to-one, one-to-many, many-to-many (resolved with a junction table), and optional relationships. Each is drawn in crow's foot notation next to the plain-English rule and the SQL that enforces it.

1. The Diagram

Crow's Foot Cardinality: Four Patterns 1:1 user user_profile Every user has exactly one profile; every profile belongs to one user. user_profile.user_id is PK and FK (shares the key). 1:N department employee A department has zero or many employees; an employee has exactly one department. employee.department_id NOT NULL FK. M:N student enrollment course enrols has A student takes many courses; a course has many students. Resolved by enrollment(student_id, course_id) with a composite PK. 0..1 employee parking_space An employee may have one space; a space may be unassigned. parking_space.employee_id NULL-able UNIQUE FK.

Each row shows one pattern, the business sentence it encodes, and how it is enforced in SQL.

  1. 1:1 (row 1): two bars on both ends. Each user has exactly one profile. Usually built by giving user_profile the same primary key as user, which is also its foreign key.
  2. 1:N (row 2): bars on the department end, circle plus crow's foot on the employee end. Put the foreign key on the "many" side, never the "one" side.
  3. M:N (row 3): relational tables cannot hold a many-to-many directly, so a junction table enrollment (highlighted) sits in the middle. Each original entity has a 1:N line into it.
  4. Optional 1:1 (row 4): circles on both ends. A parking space can be unassigned and an employee can have no space. A nullable foreign key with a UNIQUE constraint enforces it.
  5. For every line, read entity, outer symbol, inner symbol, other entity: "each department has zero to many employees".

2. How to Read It

Crow's Foot Line Endings E exactly one (mandatory one) Mermaid: || E zero or one (optional one) Mermaid: o| E one or many (mandatory many) Mermaid: |{ E zero or many (optional many) Mermaid: o{

There are only four endings. The inner symbol (nearest the entity) is the maximum: bar for one, crow's foot for many. The outer symbol is the minimum: circle for zero (optional), bar for one (mandatory). Mermaid writes them as ||, o|, |{ and o{ (mirrored as }| and }o on the left side of a line).

3. Variant: Optional vs Mandatory

Optionality Changes the Schema department employee mandatory: every employee has one employee.department_id BIGINT NOT NULL department employee optional: contractors may have none employee.department_id BIGINT NULL Only the outer symbol on the department end changed: bar (mandatory) to circle (optional).

Optionality is not decoration: it decides whether a foreign key column can be NULL. Changing a single bar to a circle on the department end turns NOT NULL into NULL. Reviewing these minimums with the business catches many data-quality bugs before the first row is loaded.

4. Pros, Cons, and When to Use

Pros

Cons

When to use

When not to use

5. As Diagrams-as-Code

Mermaid erDiagram for the four patterns:

erDiagram
    user        ||--|| user_profile  : has
    department  ||--o{ employee      : employs
    student     ||--o{ enrollment    : enrols
    course      ||--o{ enrollment    : has
    employee    |o--o| parking_space : uses