In this chapter
We'll learn the three kinds of relationships between tables — one-to-one, one-to-many and many-to-many — and normalization: the habit of storing each fact once, explained with a messy address book.
The Problem in Real Life
The old bookings_archive table has 400,000 rows. Each one repeats the venue's name and address: "Grand Hall, 12 River Road." Last year the venue moved to "14 River Road." Someone updated the address in the venues table — but the 400,000 copies in the archive still say 12. Half of BlueTicket's reports now show one address, half the other.
"The same fact, written in four hundred thousand places," John says. "And of course they don't agree. Let's talk about how tables should relate — and how to write each fact only once."
Write each fact once, in one place. Everything else should point to it.
John
Copying Facts Everywhere vs. Linking to Them
Copies drift apart
When the same fact is stored in many places, one update misses some copies — and the data contradicts itself.
Things relate in different ways
An event has many seats; a fan goes to many events; each event has many fans. Each needs a different design.
Relationships and Normalization
Tables link to each other in three shapes. Each has an everyday example that makes it obvious.
- One-to-one — a person and their passport: each person has one passport, and each passport belongs to one person. In a database: each fan has one settings record. These are less common — often the two could just be one table.
- One-to-many — a mother and her children: one event has many seats, but each seat belongs to exactly one event. One venue has many events. This is the most common relationship of all. You build it by putting a foreign key on the "many" side: each seat row stores its
event_id. - Many-to-many — students and classes: one fan can go to many events, and one event has many fans. You can't store this with a single foreign key on either side — a fan row can't hold a list of all their events, and an event row can't hold all its fans. The answer is a third table in the middle, called a junction table (or join table), with one row per link. For BlueTicket, that middle table is
bookings: each row says "this fan, this seat (at this event)." Like a class register: one line per student-class pair.
| Relationship | Everyday example | BlueTicket example | How it's built |
|---|---|---|---|
| One-to-one | A person and their passport | A fan and their settings | A foreign key, unique |
| One-to-many | A mother and her children | An event and its seats | A foreign key on the "many" side |
| Many-to-many | Students and classes | Fans and events | A junction table (bookings) |
| booking_id | fan | venue_name | venue_address |
|---|---|---|---|
| 1 | Zoë | Grand Hall | 12 River Road |
| 2 | Liam | Grand Hall | 14 River Road |
| 3 | Ava | Grand Hall | 12 River Road |
Same venue, two different addresses — the copies drifted apart after the venue moved.
| venues.id | name | address |
|---|---|---|
| 3 | Grand Hall | 14 River Road |
Bookings now store only venue_id = 3. Change the address once, and everything is correct.
Many-to-many: fans and events, through bookings
fans
Zoë (77), Liam (12), ...
events
Comedy Night (42), Festival (57), ...
bookings (junction table)
one row per fan + seat: (77 → seat at 42), (77 → seat at 57), (12 → seat at 42)
Zoë goes to two events; Comedy Night has two fans
many on both sides
Normalization — the messy address book analogy: imagine an address book where every time you write about a friend, you also copy their full address. When they move, you'd need to find and fix every copy — and you'll miss some. A tidy address book stores each friend's address once, and everything else just mentions their name. Normalization is doing this for a database: organise tables so that each fact is stored in exactly one place, and other tables refer to it with a foreign key.
The three problems normalization prevents: update problems — change the venue's address in one place, the copies keep the old one (what happened in the archive). Insert problems — you can't record a new venue until it has a booking, because the venue only exists inside booking rows. Delete problems — delete the last booking at a venue, and you lose the venue's address entirely.
How BlueTicket fixes the archive: the venue's name and address move to the venues table only. The archive's rows keep just venue_id. When the venue moves again, one row changes, and every report is right instantly. Database designers describe tidiness in levels called normal forms (first, second, third...). You don't need the details now — the idea behind all of them is "one fact, one place."
A sensible exception: sometimes a team deliberately copies a value to make reading faster, or to record history. For example, a booking stores the price paid at the moment of purchase, even though the seat has its own price — because if the seat's price changes later, the booking must still show what the fan actually paid. That's a deliberate choice, not an accident — and it has a name: denormalization.
Key Takeaway
Tables relate in three ways: one-to-one (person and passport), one-to-many (an event and its seats — a foreign key on the "many" side), and many-to-many (fans and events — a junction table like bookings in the middle). Normalization means storing each fact once and linking to it, which prevents copies that drift apart.
Why This Matters
Good table design decides whether a system's data stays trustworthy for years or slowly fills with contradictions. Spotting a many-to-many relationship and a repeated fact are two of the most useful database design skills. The Relational Databases course goes through normal forms and relationships in full detail.
The tables are tidy. Now Anna is ready for the real fix: making it impossible for two sales of the same seat to both succeed — even if they arrive in the same millisecond.