Skip to content
LLD Learn/Relational Modeling Foundations
Browsing as a guest. Sign in to save your progress and earn XP as you complete chapters.

One-to-Many and Many-to-Many

4 min read

You'll learn to

  • -Model one-to-many with a single foreign key on the "many" side
  • -Model many-to-many with a junction table, and read the rows of one
  • -Notice when a junction table should carry its own columns, and add the UNIQUE constraint it needs

Cardinality means how many of one entity can relate to how many of another. It decides the shape of the tables. One-to-many and many-to-many are the two you will use constantly, and they need genuinely different structures.

One-to-Many: A Foreign Key on the "Many" Side

A class has many students, but each student belongs to one class. The shape is one-to-many, and the rule for placing the foreign key is: put it on the side that can only point at one of the other. A student can point at their one class, but a class cannot point at a single student. In the ship example, one department has many crew members, so the foreign key lives in the crew table.

One-to-many: the foreign key lives on the "many" side
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)
);

Here are three crew rows to make it concrete. Ana and Bo both point at department 2, and Cy points at department 1. Many crew can share one department, and no crew row ever needs to list more than one.

  • -crew row 1: Ana, department_id 2
  • -crew row 2: Bo, department_id 2
  • -crew row 3: Cy, department_id 1

Many-to-Many: A Junction Table

Now think of a sign-up sheet. A passenger can book many dining sittings, and one sitting holds many passengers. Neither side can hold a single column that points at just one of the other. The fix is a junction table, also called a join or bridge table. It has its own primary key and two foreign keys, one to each side. Each row means "this passenger is booked into this sitting".

Many-to-many: a junction table between the two sides
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
);

Read some rows of the junction table. Rows (passenger 1, sitting A), (passenger 1, sitting B) and (passenger 2, sitting A) say that passenger 1 attends two sittings, and sitting A holds two passengers. The same information would need repeated and awkward columns without the junction table.

When the Junction Table Has Its Own Columns

The reservations table already has more than two foreign keys' worth of meaning. It has its own primary key, and it is a natural home for a table_number or party_size, because a reservation is a real concept of its own, not just a link. This is the same signal as in the UML chapter: when a relationship has attributes of its own, it deserves to be its own entity. A junction table with extra columns is exactly that idea applied to a schema.

Do not forget a UNIQUE constraint across the two foreign key columns, like UNIQUE (passenger_id, sitting_id) above. 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.

Ready to Build This?

Design the Ticket Booking Ledger and Grand Dining Reservations schemas in the LLD Lab's Titanic act.

Ready to Build This?

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.

Discussion0

Join the Discussion

Sign in to leave comments, reply to others, or like insights.

Sign In to ScaleDojo

No comments yet. Be the first to start the thread!