Database systems · Chapter 3 · Cardinality · Worked examples

More cardinality examples: reading a business rule into a diagram

The Chapter 3 review covers how to read a Chen (min, max) pair once it is already on the page. The harder skill, and the one that actually trips people up when modeling a real database, is going the other direction: starting from a plain sentence and deciding what pair belongs on the diagram. This page is six more business rules, worked all the way from sentence to diagram, plus the mistakes that show up most often along the way.

Four questions, asked twice

Every business rule turns into cardinality the same way. Ask two questions about each entity in the relationship, once for each side, and the (min, max) pairs fall out.

Ask this about entity A, to find the pair that sits next to A on the diagram.
QuestionWhat it fixes
For one instance of B, could there be zero matching instances of A?The minimum: yes means 0 (optional), no means 1 (mandatory)
For one instance of B, could there be more than one matching instance of A?The maximum: no means 1, yes means N, M, or another "many" letter

Then swap the entities and ask the same two questions again, this time for B. A relationship between two entities always needs the questions asked twice, once from each direction, because the two answers are almost never the same.

Where the sentence hides the answer. Words like must, every, and has to signal a minimum of 1. Words like may, can, and optionally signal a minimum of 0. Words like one and exactly one signal a maximum of 1. Words like many, several, and any number of signal an unspecified maximum. Underlining these words in the business rule before drawing anything is worth the extra thirty seconds.

Example 1

PATIENT visits DOCTOR

Both sides optional, both sides many: the simplest shape a many to many relationship can take.

Business rule. "A patient may visit many doctors over time. A doctor may see many patients."

Neither sentence uses must, so both minimums are 0. Both sentences use many, so both maximums are unspecified.

Chen model: PATIENT visits DOCTOR, (0,N) and (0,M) PATIENT connects through a visits diamond to DOCTOR. Next to PATIENT the pair reads (0,N). Next to DOCTOR the pair reads (0,M). Both sides optional and many. PATIENT visits DOCTOR (0, N) for one doctor: zero to many patients (0, M) for one patient: zero to many doctors
N and M are different letters on purpose: a busy doctor and a healthy patient are not expected to reach the same count.

Example 2

PERSON has PASSPORT

A 1:1 relationship that is mandatory on one side only, the pairing that surprises people who assume every 1:1 means both sides are required.

Business rule. "A person may or may not have a passport. Every passport belongs to exactly one person."

The first sentence has no must, so PASSPORT's minimum, from PERSON's side, is 0. The second sentence says every, so PERSON's minimum, from PASSPORT's side, is 1. Both sentences cap the count at one, so both maximums are 1.

Chen model: PERSON has PASSPORT, (1,1) and (0,1) PERSON connects through a has diamond to PASSPORT. Next to PERSON the pair reads (1,1). Next to PASSPORT the pair reads (0,1). Mandatory on the person side, optional on the passport side. PERSON has PASSPORT (1, 1) for one passport: exactly one person, required (0, 1) for one person: zero or one passport
The maximums match, both capped at one, but the minimums do not. A 1:1 relationship is really shorthand for "the maximum on both sides is 1"; the minimums still have to be worked out separately.

The trap. It is tempting to see "1:1" and assume (1,1) belongs on both ends. Always re-run the two questions on each side individually; do not infer one side's minimum from the other's.

Example 3

COUNTRY has CAPITAL_CITY

The fully mandatory 1:1, placed right after Example 2 on purpose so the two sit side by side.

Business rule. "Every country must have exactly one capital city. Every capital city belongs to exactly one country."

Both sentences use every and must, so both minimums are 1. Both cap the count at one, so both maximums are 1.

Chen model: COUNTRY has CAPITAL_CITY, (1,1) and (1,1) COUNTRY connects through a has diamond to CAPITAL_CITY. Both sides read (1,1). Fully mandatory on both ends, unlike the PERSON and PASSPORT example above. COUNTRY has CAPITAL_CITY (1, 1) for one capital: exactly one country, required (1, 1) for one country: exactly one capital, required
Same relationship shape as PERSON and PASSPORT, both capped at one, but every minimum flips to mandatory. Reading the sentence, not the shape, is what decides this.

Example 4

ORDER contains PRODUCT

A many to many relationship where the two minimums do not match, a very common real pattern that still catches people off guard.

Business rule. "An order must include at least one product. A product may appear on many orders, or none yet."

An order without a product is not really an order, so PRODUCT's minimum, from ORDER's side, is 1. A brand new product that has not sold yet has zero orders, so ORDER's minimum, from PRODUCT's side, is 0.

Chen model: ORDER contains PRODUCT, (0,M) and (1,N) ORDER connects through a contains diamond to PRODUCT. Next to ORDER the pair reads (0,M). Next to PRODUCT the pair reads (1,N). Optional on the order side, mandatory on the product side. ORDER contains PRODUCT (0, M) for one product: zero to many orders (1, N) for one order: one to many products, required
This is the ORDER_LINE bridge entity relationship, the same shape covered in the composite entity section: a many to many relationship, one side mandatory, the other optional.

Example 5

STUDENT is assigned ADVISOR

A 1:M relationship, mandatory on the "one" side, optional on the "many" side, the shape most foreign key relationships end up taking.

Business rule. "Every student must be assigned exactly one advisor. An advisor may be assigned zero, one, or many students."

Every student needs an advisor, so ADVISOR's minimum, from STUDENT's side, is 1, capped at one. A new advisor might not have any students yet, so STUDENT's minimum, from ADVISOR's side, is 0, uncapped.

Chen model: STUDENT is assigned ADVISOR, (0,N) and (1,1) STUDENT connects through an is assigned diamond to ADVISOR. Next to STUDENT the pair reads (0,N). Next to ADVISOR the pair reads (1,1). Optional on the student side, mandatory single on the advisor side. STUDENT is assigned ADVISOR (0, N) for one advisor: zero to many students (1, 1) for one student: exactly one advisor, required
The same shape as DEPARTMENT and EMPLOYEE in the Chen notation section, redrawn with different entities on purpose. Once this pattern is familiar, it shows up constantly: a foreign key is almost always mandatory on the "many" table's side and capped at one.

Example 6

BOOK written by AUTHOR

Another many to many relationship, one side mandatory, the other optional, this time needing its own bridge entity to actually build.

Business rule. "A book must have at least one author, and may have several. An author may write zero, one, or many books."

A book with no listed author is not publishable, so AUTHOR's minimum, from BOOK's side, is 1, uncapped since a book can have several authors. A first time author has written zero books until the record exists, so BOOK's minimum, from AUTHOR's side, is 0.

Chen model: BOOK written by AUTHOR, (0,M) and (1,N) BOOK connects through a written by diamond to AUTHOR. Next to BOOK the pair reads (0,M). Next to AUTHOR the pair reads (1,N). Optional on the book side, mandatory many on the author side. BOOK written by AUTHOR (0, M) for one author: zero to many books (1, N) for one book: one to many authors, required
Neither BOOK nor AUTHOR can hold the other's foreign key directly, since either one could need several of the other. A BOOK_AUTHOR bridge entity, keyed on the pair (book_id, author_id), is what actually gets built; see the composite entity section for that construction, and the JOIN examples page for a working bridge table (ASSIGNMENTS) queried with real SQL.

Common mistakes

Five ways a correct business rule turns into an incorrect diagram, roughly in order of how often each one shows up.

What tends to go wrong, and the fix.
MistakeFix
Writing the pair backward, like (M,1) instead of (1,M)Minimum always comes first. If the sentence gives you a "many" and a "required," the required number goes second, not first: (1, M), never (M, 1).
Assuming every 1:1 relationship is mandatory on both sides1:1 only fixes both maximums at 1. Ask the minimum question separately for each side; see PERSON/PASSPORT above against COUNTRY/CAPITAL_CITY.
Copying one side's minimum onto the other sideThe two minimums come from two different sentences almost every time. ORDER/PRODUCT above has a 1 on one side and a 0 on the other from the same relationship.
Placing the pair next to the wrong entityThe pair sits next to the entity it counts, but it counts that entity per one instance of the entity on the far end. See the placement trap covered in the Chen notation section.
Treating "usually" or "typically" as if it meant "must"Only a hard requirement earns a minimum of 1. A rule that describes the common case, not a rule the database has to enforce, gets a minimum of 0 instead.