Data Modeling & ERDs

Relationships & Cardinality

18 min Lesson 3 of 10

Relationships & Cardinality

Entities do not exist in isolation. A Patient books Appointments; a Product belongs to a Category; a Student enrolls in many Courses and each Course has many Students. The lines that connect entities in an ERD are called relationships, and the numbers — or symbols — that annotate those lines are called cardinality.

Getting cardinality right is one of the most consequential decisions in data modeling: it determines how tables are structured, which foreign keys exist, whether junction tables are needed, and ultimately how much data integrity the database can enforce automatically. This lesson covers all three relationship types and teaches you to read and draw them using the standard crow-foot notation.

What Is Cardinality?

Cardinality describes how many instances of entity B can be associated with one instance of entity A, and vice versa. Two values must always be stated for each end of a relationship line:

Minimum cardinality — the fewest instances that must participate (0 = optional, 1 = mandatory).
Maximum cardinality — the most instances that can participate (1 = one, many = unbounded).

In crow-foot notation, these two values are encoded as symbols drawn at each end of the relationship line:

A vertical bar (|) means "exactly one" — mandatory.
A circle (○) means "zero" — optional.
A crow foot (fork) means "many" — one or more (or zero or more, combined with circle).

Reading tip: Always read a relationship line from the perspective of one entity toward the other. "One Customer places zero or many Orders" — read the symbols at the Order end. Then reverse: "One Order is placed by exactly one Customer" — read the symbols at the Customer end.

Diagram 1 — Crow-Foot Notation Legend

Before drawing any ERD, internalize these six symbols. The diagram below shows all the combinations you will encounter.

Crow-foot notation legend: the four endpoint symbols and their meanings. Each relationship line carries symbols at both ends.

The Three Relationship Types

One-to-One (1:1)

In a one-to-one relationship, one instance of entity A is associated with at most one instance of entity B, and vice versa. True 1:1 relationships are relatively rare in business data models — they often indicate that the two entities could be merged into one table, or that the split is intentional for security, performance, or modular reasons.

Example: In a hospital system, each Patient has exactly one MedicalRecord and each MedicalRecord belongs to exactly one Patient. The records are split because medical record access has stricter security controls than basic patient demographics.

In crow-foot notation, a 1:1 is drawn with a double-bar at both ends: one and only one on each side. If the relationship is optional on one side (e.g., a patient may not yet have a medical record created), replace one pair of bars with a circle-and-bar.

One-to-Many (1:M)

A one-to-many relationship is the most common type in relational databases. One instance of entity A is associated with zero, one, or many instances of entity B, but each instance of B is associated with exactly one instance of A.

Examples:

One Customer places zero or many Orders; each Order belongs to exactly one Customer.
One Doctor has one or many Appointments; each Appointment is assigned to exactly one Doctor.
One Category contains zero or many Products; each Product belongs to exactly one Category.

Implemented in SQL by placing a foreign key in the "many" table referencing the primary key of the "one" table. The crow-foot appears at the "many" end; the double-bar (or circle-bar for optional) appears at the "one" end.

Many-to-Many (M:N)

In a many-to-many relationship, one instance of A can relate to many instances of B, and one instance of B can relate to many instances of A.

Examples:

One Student enrolls in many Courses; one Course has many Students.
One Book is written by many Authors; one Author writes many Books.
One Order contains many Products; one Product appears on many Orders.

M:N relationships cannot be implemented directly in a relational database. They require a junction table (also called a bridge table or associative entity) that holds foreign keys to both sides and turns the M:N into two 1:M relationships. This is covered in depth in Lesson 6.

Analyst habit: When you identify an M:N relationship, immediately ask: "Does the relationship itself have attributes?" If the enrollment of a student in a course has a grade and an enrollment_date, those attributes belong on the junction table — which is itself a full entity.

Diagram 2 — All Three Relationship Types (Online Store)

The following ERD shows all three relationship types in a single model for a simplified online store. Read each relationship line using the legend above.

ERD showing all three relationship types: 1:1 between Customer and CustomerProfile, 1:M between Customer and Order, and M:N between Order and Product resolved through the OrderItem junction entity.

Reading the Diagram

Let us read each relationship in plain English to practice the notation:

Customer — CustomerProfile (1:1): One Customer has exactly one CustomerProfile. One CustomerProfile belongs to exactly one Customer. Neither side is optional here — both entities must exist together.
Customer — Order (1:M): One Customer may place zero or many Orders (optional many). Each Order must belong to exactly one Customer (mandatory one). A customer account can exist before any order is placed, which is why the Order end is "zero or many" not "one or many."
Order — OrderItem — Product (M:N resolved): One Order must contain one or many OrderItems. Each OrderItem references exactly one Order. Similarly, one Product may appear on one or many OrderItems, and each OrderItem refers to exactly one Product. The M:N between Order and Product is resolved through the OrderItem junction table, which also holds the quantity attribute that naturally belongs to the association.

Common modeling error — forgetting minimum cardinality: Many beginners draw only the maximum (one or many) and forget the minimum (mandatory vs. optional). This matters enormously in implementation: a mandatory relationship enforces a NOT NULL foreign key constraint; an optional one allows NULL. Always ask both questions: "Can this be zero?" and "Can this be many?"

Diagram 3 — Library System: Verifying Your Reading

Here is a second ERD for a library management system. Study it and try to articulate each relationship in plain English before reading the description below.

Library system ERD: a Member can have zero or many Loans; each Loan involves exactly one BookCopy; each Book has one or many physical BookCopies.

Reading the library ERD:

One Member may have zero or many Loans (a new member has no loans yet). Each Loan belongs to exactly one Member.
One BookCopy may appear on zero or many Loans over its lifetime (a copy that was never borrowed has zero loans). Each Loan covers exactly one BookCopy.
One Book has one or many physical BookCopies (a book title must have at least one copy to be in the system). Each BookCopy belongs to exactly one Book.

Summary

Cardinality specifies the minimum and maximum number of entity instances that participate in a relationship.
Crow-foot notation encodes cardinality with three symbols at each line end: double-bar (exactly one), circle (zero / optional), and crow-foot fork (many).
The three relationship types are 1:1 (rare, often a security or performance split), 1:M (the most common, implemented with a foreign key), and M:N (requires a junction table).
Always state both minimum and maximum cardinality — the minimum determines whether the foreign key is nullable and whether the relationship is mandatory.
When an M:N relationship has its own attributes (grade, quantity, enrollment_date), the junction table becomes a named associative entity in its own right.