Cardinality

Learn one-to-one, one-to-many and many-to-many.

Cardinality

Cardinality states how many entities take part in a relationship: one-to-one (1:1), one-to-many (1:N) or many-to-many (M:N). The right cardinality drives the right table layout.

-- 1:1  users.profile_id -> profiles.id
-- 1:N  orders.customer_id -> customers.id
-- M:N  students <-> courses (junction table)

Three basic cardinality types exist.

Cardinality — details

An M:N relationship needs a junction table between the two entities. It holds the pair of foreign keys.

Exercise Cardinality

Create the M:N enrollments junction table for students and courses. Add two foreign keys.

Show example solution

CREATE TABLE enrollments (
  student_id INT REFERENCES students(id),
  course_id INT REFERENCES courses(id),
  PRIMARY KEY (student_id, course_id)
);

Test yourself

Add a foreign key

FOREIGN KEY (customer_id) REFERENCES ____

Show answer

FOREIGN KEY (customer_id) REFERENCES orders(id)

What does an M:N relationship need?
- An extra column
- A new index
- A trigger
- A junction table
Show answer

A junction table — An M:N relationship needs a junction table.
Cardinality: match the terms

1:1

one to one
1:N

one to many
M:N

many to many
junction

bridge

Next lesson: Surrogate keys

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies