Many-to-Many and the Junction Table

The last page handled one-to-many: one film, many screenings, and the pointer lives happily on the many side. But the Marquee's most important relationship refuses that shape. A customer attends many screenings, and a screening seats many customers — many on both ends. Neither table can hold the pointer, because a pointer column holds one value, and both sides need "many."

Relational databases solve many-to-many with a third table that holds one row per pairing — this customer, at this screening. At the Marquee, that table already has a name: bookings. And here is the idea this page turns on: the junction table is not a technical workaround bolted on to make the model cope. It is where the real data lives — the moment you see that, the relational model clicks.

Why Neither Side Can Point

Try to make one side hold the relationship and watch it fail. Give screenings a column for its customers — but a column holds one value, and the Friday 20:00 show has forty. Give customers a column for their screenings — same wall, from the other side. A single pointer column simply cannot say "these forty."

A spreadsheet, of course, would let you cheat: cram "214, 231, 305…" into one cell as a comma-separated blob, or sprout columns named customer1, customer2, customer3. Both moves feel resourceful and both rot on contact with reality — the blob can't be searched or counted or protected by any rule, and the numbered columns die the day customer4 shows up. This is precisely the kind of improvisation that made the Chapter 1 spreadsheet untrustworthy. The declared-shape discipline of a table forbids it, and usefully so: the refusal forces the honest design out into the open.

The Junction Table

The honest design: stop trying to store "many" in a cell, and give each pairing its own row in a third table. One customer at one screening — one row. Anna attends Friday's Night Bus: a row. Her friend Ben attends the same show: another row. Anna comes back Sunday: a third row. A table built this way is called a junction table, and it carries two foreign keys — one pointing at each side it connects:

CREATE TABLE bookings (
  booking_id    INTEGER PRIMARY KEY,
  customer_id   INTEGER REFERENCES customers(customer_id),
  screening_id  INTEGER REFERENCES screenings(screening_id),
  seat          TEXT,
  booked_at     TIMESTAMP,
  UNIQUE (screening_id, seat)
);

Read it with last page's skills: customer_id must name a real customer, screening_id must name a real screening — two REFERENCES clauses, two enforced pointers per row. And notice the final line, UNIQUE (screening_id, seat): no two rows may share the same screening and the same seat. That single quiet line is what makes selling seat G7 twice for the same show — the disaster that opened this book — structurally impossible. Remember this line; Chapter 7 stands on it.

Watch it work. Anna and two friends book that Sunday Night Bus screening (screening_id 42):

INSERT INTO bookings (customer_id, screening_id, seat, booked_at)
VALUES (214, 42, 'G7', '2026-07-19 15:02');

INSERT INTO bookings (customer_id, screening_id, seat, booked_at)
VALUES (231, 42, 'G8', '2026-07-19 15:04');

INSERT INTO bookings (customer_id, screening_id, seat, booked_at)
VALUES (305, 42, 'G9', '2026-07-19 15:11');

Three INSERTs, three rows, each naming one customer and one screening — Anna in G7, and her friends in G8 and G9. The booking ids are left out and minted by the engine, exactly as Topic 07 taught. Nothing here is new machinery: many-to-many is just two one-to-manys meeting in the middle. Customers-to-bookings is one-to-many; screenings-to-bookings is one-to-many; put them together and the junction table connects many customers to many screenings using only tools you already own.

The Pairing Has Its Own Facts

Look at what else each row carries: a seat and a booked_at timestamp. Ask where else those facts could possibly live. The seat isn't a fact about Anna — she sits in different seats on different nights. It isn't a fact about the screening — the screening has dozens of seats. G7-on-Friday-at-19:02 is a fact about the booking itself: the pairing, not either partner.

Think of a guest list for an event. The list is not the people and not the event — it is its own document, with its own information per line: who, which seat, when they RSVP'd. Junction tables usually grow their own columns like this, and that growth is the tell that they are real entities, not connective tissue. The world agrees: an order is the junction of shoppers and products; an enrollment, of students and courses; an appointment, of patients and doctors. In every one of those businesses, the junction table is the table the money flows through — usually the most important table in the schema.

The Complete Marquee Schema

With bookings declared, the schema this chapter promised is finished. Four tables, three foreign-key connections, every rule enforced on every write:

You can now read the whole thing, and that is not a small skill. One film has many screenings; one screening has many bookings; one customer has many bookings; and through bookings, customers and screenings meet in the many-to-many the business actually runs on. Lora's data has gone from a spreadsheet that lost a sale to a structure where the loss is impossible by declaration. The next chapter starts asking it questions.