DataslopeCardinality: One, Many, and How Many
Learn how cardinality describes the number of rows that may participate on each side of a relationship.
Two entities can be related, but that is not enough detail. A customer places orders — but how many orders can one customer place? Must every order have a customer? Can one product appear on many orders?
Cardinality answers: how many rows on one side may relate to how many rows on the other side?
Cardinality is about counts
When you read a relationship, ask both directions:
- For one row on the left, how many rows on the right can connect?
- For one row on the right, how many rows on the left can connect?
That gives us a one-to-many relationship: one customer, many orders.
One-to-many
A one-to-many relationship means one row on the first side can connect to many rows on the second side, while each row on the second side connects back to one row on the first side.
Read ||--o{ as "one to zero-or-many." The order side is optional
and many: a customer may have no orders yet, or many orders later.
The foreign key goes on the "many" side. Many order rows can store the
same customer_id.
One-to-one
A one-to-one relationship means one row on each side can connect to at most one row on the other side. A common example is a user and a profile.
The ||--|| line says each side is exactly one. In practice, some
one-to-one relationships are optional on one side.
Here an employee may have zero or one parking pass. A parking pass, if it exists, belongs to one employee.
Quick check
In a one-to-many relationship between customers and orders, where does the foreign key usually go?
On the customers table, storing a list of order ids.
On the orders table, because each order points to one customer.
On both tables, with each side copying all columns from the other.
On neither table, because one-to-many relationships cannot be stored.
Many-to-many
A many-to-many relationship means many rows on each side can connect to many rows on the other side. Students and courses are the classic example.
This diagram describes the real-world cardinality, but the relational schema needs a junction table to store the pairings.
The junction table turns many-to-many into two one-to-many relationships.
Optional vs mandatory participation
Cardinality also includes the minimum number of related rows. This is sometimes called participation.
- Optional means zero is allowed.
- Mandatory means at least one is required.
Crow's-foot symbols combine minimum and maximum. o{ means zero or
many. || means exactly one. o| means zero or one.
This says a customer can have zero or many orders, but an order has one
or more order items. The |{ end means "one or many."
Quick check
What does optional participation mean in a relationship?
The relationship is not important and should be ignored.
A row is allowed to have zero related rows on that side of the relationship.
Every row must have at least two related rows.
The foreign key must always be text.
Cardinality changes the schema
If one employee can have many devices, the devices table can store
employee_id. If one employee can have at most one active badge, the
badge table needs a uniqueness rule on employee_id.
The difference between o{ and o| is not decorative. It changes the
constraints you need.
PostgreSQL does not understand the picture directly. You translate the
picture into keys, NOT NULL, UNIQUE, and sometimes additional
constraints or application rules.
Check your understanding
What does cardinality describe?
The color used for a table in a diagram.
How many rows on each side of a relationship may relate to rows on the other side.
The exact order in which SQL statements must be typed.
Whether a column stores text or numbers.
Which relationship is best described as many-to-many?
One order is placed by one customer, and a customer may place many orders.
One user has one required profile.
A student can take many courses, and a course can contain many students.
One parking pass belongs to one employee, and an employee may have no pass.
In Crow's-foot notation, what does o{ usually mean at one end of a relationship?
Exactly one.
Zero or one.
Zero or many.
One or many, with zero forbidden.
Why does a one-to-zero-or-one relationship often need a UNIQUE constraint on the foreign key?
To make the foreign key store several ids in one cell.
To prevent many rows from pointing to the same parent row.
To remove the need for a primary key on the parent table.
To make optional rows mandatory.