One-to-Many and Many-to-Many
You'll learn to
- -Model one-to-many relationships directly with a foreign key, and many-to-many with a junction table
- -Recognize when a junction table needs its own attributes (e.g. a booking amount or timestamp) beyond the two foreign keys
Cardinality - how many of one entity can relate to how many of another - determines the actual shape of the tables. One-to-many and many-to-many are the two you will use constantly, and they require genuinely different table structures.
One-to-Many: A Foreign Key on the "Many" Side
One department has many crew members, but each crew member belongs to exactly one department - a one-to-many relationship. This is modeled with a single foreign key on the "many" side (crew.department_id), with no extra table needed. The foreign key lives on whichever side can only point to one of the other - crew members point at their one department, not the other way around.
CREATE TABLE departments (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE crew (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
department_id INTEGER NOT NULL,
FOREIGN KEY (department_id) REFERENCES departments(id)
);Many-to-Many: A Junction Table
A passenger can make many dining reservations, and a single dining sitting can hold many passengers - many-to-many. A single foreign key cannot represent this, since neither side can point to just one of the other. The fix is a junction table (also called a join or bridge table) with its own primary key and two foreign keys, one to each side of the relationship.
CREATE TABLE passengers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE dining_sittings (id INTEGER PRIMARY KEY, sitting_time TEXT);
CREATE TABLE reservations ( -- the junction table
id INTEGER PRIMARY KEY,
passenger_id INTEGER NOT NULL,
sitting_id INTEGER NOT NULL,
FOREIGN KEY (passenger_id) REFERENCES passengers(id),
FOREIGN KEY (sitting_id) REFERENCES dining_sittings(id),
UNIQUE (passenger_id, sitting_id) -- prevents duplicate reservations
);When the Junction Table Needs Its Own Attributes
The reservations table above already has more than just two foreign keys - it has its own primary key, and it is a natural place for a table_number or party_size column, because a reservation is a real, first-party concept in its own right, not just a bookkeeping link. This is the same signal from the Phase 1 UML chapter: whenever a relationship itself has attributes, it deserves to be modeled as its own entity - a junction table with extra columns is exactly that idea, applied to schema design.
A UNIQUE constraint across the two foreign key columns in a junction table (like UNIQUE (passenger_id, sitting_id) above) is easy to forget and important to include - without it, nothing stops the same passenger from being inserted twice into the same sitting.
Interview Signal is part of Pro
See a real weak answer next to a real strong one for this exact topic.
Quiz is part of Pro
Test what you just read with a short quiz, and bank the XP.
Design the Ticket Booking Ledger and Grand Dining Reservations schemas in the LLD Lab's Titanic act.
Once a one-to-many relationship exists as a foreign key, querying across it is its own skill - SQL Lab's Chapter 3 ("Crossed Wires") opens with exactly that: joining two tables on a foreign key to turn an id into a name.