The Story That Explains Cardinality
A database designer asks the very same two questions about every relationship. How are the two entities paired (one–to–one, one–to–many…) and how many occurrences are allowed at each end (minimum and maximum)? Answering them is the whole job of cardinality.
When you draw an Entity-Relationship diagram, the boxes and diamonds only tell you that two entities are related. Cardinality tells you the crucial extra detail: how many entities on one side may be linked to entities on the other. Get it wrong and your tables will store impossible data or refuse to store valid data.
The cardinality of a relationship is the actual number of related occurrences for each of the two entities. An ER diagram provides a way to mark this count directly on the edges connecting entities to a relationship.
Two Ideas Hiding in One Word
Textbooks often blur two related but distinct ideas. Keeping them separate makes everything click into place.
Connectivity answers "one or many?" — Cardinality answers "how many, at least and at most?" — Participation answers "is taking part compulsory?"
Connectivity — The Four Mapping Types
Because each side can be "one" or "many", there are exactly four ways two entities can be paired. Watch them build up one by one.
M:1 is simply 1:M viewed from the opposite direction — the same relationship, read the other way.
| Connectivity | Reads As | Everyday Example |
|---|---|---|
| 1 : 1 | One A ↔ one B | Person ↔ Passport |
| 1 : M | One A → many B | Customer → Loans |
| M : 1 | Many A → one B | Employees → Department |
| M : M | Many A ↔ many B | Suppliers ↔ Parts |
One-to-Many in Action — Customer & Loan
Connectivity is easiest to feel with real rows. In the bank below, one customer can take many loans, but each loan belongs to exactly one customer — a textbook 1:M relationship. The animated lines show how individual instances map.
Customer C1 (RAJ) maps to two loans — that is the "many" side. No loan ever points to two customers — that is the "one" side.
Pick one row on the left and count its arrows: C1 has two → the right side is "many". Pick one row on the right and count its arrows: L1 has one → the left side is "one". Two counts, and the pattern (1:M) reveals itself.
Many-to-Many in Action — Supplier & Part
The trickiest pattern is M:M. A supplier can supply many parts, and a part can be supplied by many suppliers. Notice the crossing lines — that tangle is the visual signature of a many-to-many relationship.
S1 supplies P1 and P3; P1 is supplied by S1 and S2. Both sides are "many", so this is M:M — and the quantity supplied (QTY) belongs to the link, not to either entity.
There is nowhere to store QTY in either the Supplier or the Part table — it depends on the pair. That is why every M:M relationship is implemented as a separate junction table. We show exactly how in Section 09.
Cardinality Notation — the l..h Range
Connectivity gives the rough shape; the l..h notation gives the precise numbers. An edge between an entity set and a relationship set can carry an associated minimum and maximum cardinality, written l..h, where l is the lowest and h the highest number of occurrences allowed.
Read each edge as "this entity participates a minimum of l and a maximum of h times". Here: a loan needs exactly one owner (1..1), a customer may have any number of loans (0..*).
| l..h Label | Minimum | Maximum | Meaning |
|---|---|---|---|
| 1..1 | 1 | 1 | Exactly one — mandatory and single |
| 0..1 | 0 | 1 | At most one — optional, single |
| 1..* | 1 | many | At least one — mandatory, many |
| 0..* | 0 | many | Any number — optional, many |
The symbol * (or N) in the high position means "no fixed upper limit". So 1..* reads as "one or more", and 0..* reads as "zero or more".
Connectivity vs Cardinality — Side by Side
Both describe a relationship, but at different levels of precision. This comparison settles the confusion for good.
| Describes the mapping of entity instances |
| Values are only "one" or "many" |
| Four types: 1:1, 1:M, M:1, M:M |
| Answers: "what shape is the link?" |
| The actual number of related occurrences |
| Expressed as a precise range |
| Written as l..h (min..max) on an edge |
| Answers: "exactly how many, at least and at most?" |
Connectivity is the headline; cardinality is the fine print. A "one-to-many" headline (connectivity) becomes precise when you add the ranges: the "one" side is 1..1 and the "many" side is 0..* (cardinality).
Cardinality & Participation
The minimum half of the l..h range carries a second meaning: it decides whether an entity's participation in the relationship is compulsory.
Cardinality (the count) and participation (optional vs mandatory) are decided separately — but both are read from the same l..h label.
How Cardinality Shapes Your Tables
Cardinality is not just decoration — it dictates exactly how a relationship becomes real tables. Two rules cover almost every case.
Cust_ID column, so the same customer can appear against
several loans.
QTY). This is the only way to store
a many-to-many fact.
1:M Mapping — the foreign key lands in LOAN
Because customer C1 has two loans, C1 simply appears twice in the foreign-key column — perfectly legal on the "many" side.
-- 1:M : foreign key Cust_ID added to the many side
CUSTOMER ( Cust_ID PK, Name, City )
LOAN ( Loan_No PK, Amount, Cust_ID FK )
-- LOAN rows: C1 may repeat, no loan repeats
-- Loan_No | Amount | Cust_ID
-- L1 | 10000 | C1
-- L2 | 20000 | C1 <- same customer, second loan
-- L3 | 10000 | C2M:M Mapping — a junction table for SUPPLIER–PART
-- M:M : a new table holds both keys plus the link attribute
SUPPLIER ( SNo PK, Name )
PART ( PNo PK, PName )
SUPPLIES ( SNo FK, PNo FK, QTY )
-- Primary key = (SNo, PNo)Common Mistakes to Avoid
In a 1:M, the "1" sits beside the entity on the one side, but it describes how the other side links to it. Always test with data: pick one row and count its partners on the opposite table.
You cannot. A foreign key column can hold only one value per row, so it can never represent "many on both sides". The moment both sides are "many", you need a junction table — no exceptions.
The maximum (1 or many) is the connectivity; the minimum (0 or 1) is the participation. A relationship can be 1:M and total at the same time — the two facts are independent.
Quick Reference
| Connectivity | "One" Side l..h | "Many" Side l..h | Maps To | Example |
|---|---|---|---|---|
| 1 : 1 | 0..1 / 1..1 | 0..1 / 1..1 | FK on either side | Person – Passport |
| 1 : M | 1..1 | 0..* | FK on many side | Customer – Loan |
| M : 1 | 0..* | 1..1 | FK on many side | Employee – Dept |
| M : M | 0..* | 0..* | Junction table | Supplier – Part |
| Symbol / Label | Means |
|---|---|
| 1 beside an edge | The "one" side of connectivity |
| M or * | The "many" side of connectivity |
| l..h | Minimum (l) and maximum (h) cardinality |
| Single line | Partial participation (min = 0) |
| Double line | Total participation (min = 1, i.e. 1..*) |