Episode 23 · The table that remembered two emails

SQL and relations: modelling a hotel in tables

Tuesday, 10:40: Ana changed her email last week, yet 3 of her 14 booking confirmations went to the old one, and finance found 41 bookings that check out before they check in. Same table, same root cause.

How a hotel's guests, rooms and bookings fit into tables that point at each other, and why that saves you from a whole family of bugs.

30 min intermediateRelational model & SQLTables, primary keys and foreign keysSQL joinsNormalization vs denormalizationDatabase constraints as enforced rulesWhy databases exist

Problem
A hotel platform must store hotels, rooms, guests and bookings so that thousands of clients can read and change them without ever producing contradictory data.
Why the simple system fails
One big spreadsheet-like table repeats the hotel's name and the guest's email on every row. Updates miss copies, deletes erase facts you needed, and application bugs write nonsense (check-out before check-in) that nothing stops.
Engineering concept
Store each fact once in its own table, link tables with keys, declare rules as constraints the database enforces, and recombine data at read time with joins.
Technology
Relational databases (PostgreSQL, MySQL), SQL DDL (CREATE TABLE, constraints) and DML (SELECT with JOIN, GROUP BY), plus deliberate denormalization for hot read paths.
Trade-offs
Normalized schemas are correct and flexible but need joins; denormalized ones read faster but must be kept in sync. Constraints cost a little write time and buy guarantees no code review can.

The problem

2 min

Tuesday, 10:40. StayHub now serves about 30,000 guests a day.

Maya · Product manager
Ana Costa changed her email last week. 3 of her 14 booking confirmations still went to the old address.
Omar · On-call SRE
And finance found 41 bookings that check out before they check in.
Lina · Senior engineer, your mentor
Same table, maybe the same cause. Guess first.

The big idea

4 min

One fact, one place

The big idea

Store each fact once, link by id, and let the database enforce the rules.

Patching the email code would fix this copy in this code path, until the next script misses one. Changing the structure fixes it for every writer: Ana's email lives in one row, and impossible dates are refused at insert time.

How it works

5 min

A relational keeps data in tables of rows and columns. Here's how bookings_flat becomes linked tables.

From one flat table to linked tables

1
Find the things
One flat row mixes four things: a hotel, a room, a guest and a booking. A hotel exists even with no bookings, which the flat table can't express.
2
One table per thing
hotels, rooms, guests, bookings. Each row gets a : an id that is unique and never changes. Email is UNIQUE, but not the key, because emails change.
3
Point, don't copy
A booking stores guest_id and room_id: , pointers to another table's id. The database refuses pointers to nothing. Changing Ana's email is now one UPDATE on one row.
4
Write the rules down
A is a rule the database checks on every write: NOT NULL, UNIQUE (email), CHECK (check_out > check_in). The 41 bad rows would be refused, whoever inserts them.
5
Join to read
A combines rows by following the pointers at query time: booking → guest, booking → room → hotel. A LEFT JOIN keeps rows with no match, filled with NULL.

Storing each fact once is . Copying on purpose to speed up one read is : a choice made after measuring, not the default.

Watch a decision being made

4 min

Worked example

Lina splits bookings_flat

bookings_flat (about 5 million rows) copies hotel and guest details onto every booking and accepts impossible dates. Redesign it without losing any information.

1Give each thing a table and a stable id
Group the columns by what they describe: hotel, room, guest, booking. Each gets a table with an id primary key.
Why: If email were the key, every pointer to Ana would need rewriting when she changes it. Ids don't change.
2Link with foreign keys
The "many" side holds the pointer: bookings.guest_id, bookings.room_id, rooms.hotel_id.
sql
```
guest_id bigint NOT NULL REFERENCES guests(id),
room_id  bigint NOT NULL REFERENCES rooms(id) ON DELETE RESTRICT
```
Why: No booking for a guest or room that doesn't exist, and no deleting a room that still has bookings.
3Declare the rules
CHECK (check_out > check_in), an allowed list of statuses, NOT NULL on everything a booking can't live without.
Why: The 41 bad rows came from a partner import script, not our app. Only a rule inside the database stops every writer.
4Keep the history on the booking
total_cents stays on bookings: the price Ana agreed to that day, not a copy of today's room price.
Why: When prices change next season, May's invoice must still say what was paid in May. A historical fact is its own fact, not a duplicate.

In real life

3 min

Two companies, two shapes of data

Stack Overflow: relational at the core

Has publicly described running mostly on a relational SQL database for years
Questions, answers, users and votes in their own tables, joined at read time

Fits data with many relationships and rules that must hold.

Discord: shaped for one query

Has publicly written about storing billions of messages in a wide-column database
Laid out for one question: recent messages in a channel; gives up flexible joins

Fits one huge, simple access pattern at enormous scale.

What goes wrong

3 min

Skipping foreign keys "for performance"

Orphan rows appear within weeks; every report needs defensive code.

Instead

Keep foreign keys and index the referencing columns.

Storing lists in a column (`'wifi,pool,spa'`)

You can't constrain or join it; "rooms with a pool" becomes a fragile text search.

Instead

A join table such as room_type_amenities.

Denormalizing before measuring

Copies must stay in sync forever, and most slow joins are just missing indexes.

Instead

Normalize, measure, index, then copy only the read path that still hurts.

Trade-offs

3 min

Shape	Fits when	You pay
Normalized	System of record: bookings, payments, guests	Joins on reads; more tables
Denormalized read copy	A measured hot read path still slow after indexing	Copies drift if the sync fails
JSON columns	Truly variable attributes you rarely filter on	Weak constraints, harder joins

Friday, 11:00. The new schema is live. "My bookings" now joins four tables.

Karim · CTO
Let's copy hotel name and city back onto bookings and skip the joins.
Lina · Senior engineer, your mentor
Then we're back to copies that drift. Hotels rename themselves every week.
Karim · CTO
Joins cost time on every page view. Renames are rare.

Your job as tech lead

4 min

Claude Code can write the code for this. You still own the decisions, and you have to know what good looks like to accept or reject its work.

Decisions you own

Which tables exist and what each is the single home for. A guest's email lives in one row, not on all 14 of Ana's bookings.
Which rules the database enforces for every writer: check-out after check-in, a booking points to a real room, one account per email.
What happens when something others point to is deleted: refuse the delete, or archive. Never silently cascade.

Tell Claude Code

We're splitting bookings_flat (~5 million rows, guest and hotel fields copied on every row, room_id with no foreign key) into guests, hotels, rooms and bookings. Plan first, don't touch production. Propose the schema with primary keys, foreign keys, a unique email and a check on the dates, keep the booked price on each booking, and a migration in reversible steps. Show me how you'll handle the 41 rows with impossible dates and the 37 orphaned bookings before writing any migration.

Check before you accept

Every booking has foreign keys to rooms and guests, email is unique, and a CHECK guards the dates.
Deleting a room with future bookings is refused, not cascaded.
On a staging copy: 5 million bookings in, 5 million out, and Ana Costa has one guest row.

Red flags

It adds ON DELETE CASCADE to bookings "to keep things clean".
It drops a CHECK or foreign key because existing bad rows make the migration fail.
It runs the migration against production, or in one irreversible step.

Check yourself

2 min

Q1/3Scenario

Bookings store guest_email directly on each row. A guest with 14 bookings updates their email in their profile, and some confirmation emails still go to the old address. What's the root cause?

How confident are you?

Next episode: the new tables hold 5 million bookings, and the "My bookings" page now takes 500 ms while the database CPU sits at 90%.

Finished?

Mark it complete to update your skill map.

Next: Indexes: from 500 ms to 10 ms

Go deeper (optional)You don't need this to understand the lesson. Open it when a project needs the details: Technical deep dive, Visual diagram, Analogy, Why it matters, Check your understanding, Practice ladder, Explain it yourself.

Technical deep dive

Splitting bookings_flat

Worked example

Lina splits bookings_flat

bookings_flat (about 5 million rows) copies hotel, room and guest details onto every booking and accepts impossible dates. Redesign it so emails can't drift and bad dates can't be saved, without losing any information.

1Name the things
Read the columns and group them by what they describe: hotel_name, hotel_city → a hotel; room_id, room_number → a room; guest_name, guest_email → a guest; dates, price, status → the booking itself.
Why: Each group is a thing that exists on its own. A hotel exists even with no bookings, which the flat table can't express.
2Give each thing a table and an id
Create hotels, room_types, rooms, guests, bookings, each with a stable id primary key. Email gets UNIQUE, but it is not the key.
Why: Emails change; ids don't. If email were the key, every pointer to the guest would need rewriting when Ana changes it.
3Link with foreign keys
The "many" side holds the pointer: rooms.hotel_id, bookings.guest_id, bookings.room_id, each declared with REFERENCES.
sql
```
guest_id bigint NOT NULL REFERENCES guests(id),
room_id  bigint NOT NULL REFERENCES rooms(id) ON DELETE RESTRICT
```
Why: The database now refuses a booking for a guest or room that doesn't exist, and refuses deleting a room that still has bookings.
4Declare the rules
CHECK (check_out > check_in), CHECK (status IN ('pending','confirmed','cancelled')), NOT NULL on everything a booking can't live without.
Why: The 41 bad rows came from a partner import script, not our app. Only a rule inside the database stops every writer.
5Keep the history on the booking
total_cents stays on bookings. It's the price Ana agreed to on that day, not a copy of today's room price.
Why: Prices change next season. An invoice from May must still say what was paid in May. Copying a historical fact is correct modelling, not denormalization.
6Join it back and compare
Rebuild the old flat view with joins and check row counts and totals match before switching the app over.
sql
```
SELECT b.id, h.name, g.email, b.check_in, b.check_out
FROM bookings b
JOIN guests g ON g.id = b.guest_id
JOIN rooms  r ON r.id = b.room_id
JOIN hotels h ON h.id = r.hotel_id;
```
Why: Same 5 million rows, same totals: proof the split lost nothing. Then the old table can go.

Here is the full schema. Notice how many rules are declared rather than coded:

sql

CREATE TABLE hotels (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text NOT NULL,
  city       text NOT NULL,
  country    char(2) NOT NULL
);

CREATE TABLE room_types (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  hotel_id    bigint NOT NULL REFERENCES hotels(id),
  name        text NOT NULL,
  capacity    int  NOT NULL CHECK (capacity BETWEEN 1 AND 12),
  base_price_cents int NOT NULL CHECK (base_price_cents > 0)
);

CREATE TABLE rooms (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  hotel_id     bigint NOT NULL REFERENCES hotels(id),
  room_type_id bigint NOT NULL REFERENCES room_types(id),
  number       text NOT NULL,
  UNIQUE (hotel_id, number)                       -- no two "204"s in one hotel
);

CREATE TABLE guests (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email  text NOT NULL UNIQUE,
  name   text NOT NULL
);

CREATE TABLE bookings (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  guest_id    bigint NOT NULL REFERENCES guests(id),
  room_id     bigint NOT NULL REFERENCES rooms(id) ON DELETE RESTRICT,
  check_in    date NOT NULL,
  check_out   date NOT NULL,
  status      text NOT NULL CHECK (status IN ('pending','confirmed','cancelled')),
  total_cents int  NOT NULL CHECK (total_cents >= 0),
  CHECK (check_out > check_in)
);

PostgreSQL. Keys, foreign keys and constraints turn business rules into guarantees.

Joins recombine the data. An INNER JOIN keeps only rows with a match on both sides; a LEFT JOIN keeps every row from the left table and fills missing matches with NULL.

sql

-- Arrivals: INNER JOIN, every row needs a guest, room and room type
SELECT g.name, g.email, r.number, rt.name AS room_type, b.check_out
FROM bookings b
JOIN guests     g  ON g.id  = b.guest_id
JOIN rooms      r  ON r.id  = b.room_id
JOIN room_types rt ON rt.id = r.room_type_id
WHERE r.hotel_id = 42
  AND b.check_in = CURRENT_DATE
  AND b.status = 'confirmed'
ORDER BY r.number;

-- Booked nights per room type this month: LEFT JOIN keeps unbooked types (0)
SELECT rt.name,
       COALESCE(SUM(b.check_out - b.check_in), 0) AS nights_booked
FROM room_types rt
LEFT JOIN rooms r    ON r.room_type_id = rt.id
LEFT JOIN bookings b ON b.room_id = r.id
                    AND b.status = 'confirmed'
                    AND b.check_in >= date_trunc('month', CURRENT_DATE)
WHERE rt.hotel_id = 42
GROUP BY rt.name
ORDER BY nights_booked DESC;

Tonight's arrivals for one hotel, and booked nights per room type including types with zero bookings.

Constraint	Example	Prevents
PRIMARY KEY	bookings.id	Duplicate or missing identity
FOREIGN KEY	bookings.room_id → rooms.id	Orphans: bookings for rooms that don't exist; deleting a booked room
NOT NULL	guests.email	Half-filled rows that break code later
UNIQUE	guests.email, (hotel_id, number)	Duplicate accounts, two rooms numbered 204
CHECK	check_out > check_in	Impossible values written by any client
EXCLUDE (PostgreSQL)	No overlapping date ranges per room	Double-booking, even under concurrency

Each constraint is one class of bug that can no longer reach production data.

sql

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE bookings ADD CONSTRAINT no_overlapping_stays
  EXCLUDE USING gist (
    room_id WITH =,
    daterange(check_in, check_out) WITH &&
  ) WHERE (status = 'confirmed');

An exclusion constraint makes overlapping confirmed bookings for the same room impossible.

Visual diagram

hotels

room_types

rooms

guests

bookings

room_type_amenities

payments

Click a component

The core hotel schema. Each box is a table; arrows are foreign keys pointing at the referenced table.

Analogy

In a hotel officeIn a database

In a hotel officeThe guest register, room list, booking ledgerIn a databaseThree tables
In a hotel officeEach guest's card numberIn a databaseThe primary key
In a hotel office"Card 81723, room 204" in the ledgerIn a databaseForeign keys
In a hotel officeClerk looks up card and room togetherIn a databaseA join
In a hotel officeOffice rules nobody can bypassIn a databaseConstraints
In a hotel officeThe lobby board of tonight's arrivalsIn a databaseA denormalized copy for fast reads

Where the analogy breaks: a clerk takes minutes to cross-reference ledgers. A database joins millions of rows in a moment, and checks the rules on every write without getting tired.

Why it matters

Imagine one giant spreadsheet instead: one row per booking, with the guest's email and the hotel's name copied into every row.

A guest changes their email. It's on 14 rows, and the code updates 13. Now the guest has two emails. Delete a hotel's only booking, and you forget the hotel exists. A bug saves a check-out before the check-in, and nothing complains until finance does.

These bugs happen in real products, again and again. Separate tables plus constraints make them impossible by design, instead of hoping every developer gets it right forever.

One big sheet vs linked tables

One wide table

Same email copied 14 times
Copies drift apart
No guardrails on bad data

Simple today, messy tomorrow

Linked tables

Each fact stored once
Links checked by the database
Rules enforced for everyone

A solid source of truth

Check your understanding

Q1/3Reasoning

Match each rule to the constraint that enforces it:

A booking can't reference a non-existent room

Two guests can't share an email

Check-out must be after check-in

No overlapping confirmed stays in one room

Every booking has a status

How confident are you?

Practice ladder

Step 1 · Trace

Count the rows of a LEFT JOIN

Code Warm-up

Hotel 42 (Casa Azul) has 3 room types:

Double: 4 rooms
Suite: 2 rooms
Family: 0 rooms (just added, no rooms yet)

Trace both queries and say how many rows each returns, and what the Family row looks like (if it appears).

-- Query A
SELECT rt.name, r.number
FROM room_types rt
JOIN rooms r ON r.room_type_id = rt.id
WHERE rt.hotel_id = 42;

-- Query B
SELECT rt.name, r.number
FROM room_types rt
LEFT JOIN rooms r ON r.room_type_id = rt.id
WHERE rt.hotel_id = 42;

Step 2 · Complete

Finish Lina's payments table

Design Practice

A booking can have several payments: a deposit, the balance, sometimes a refund (negative amount). Each payment has a reference from the payment provider, which must never be recorded twice (the provider sometimes sends the same webhook twice).

Lina started the table. Fill in the four gaps so the database enforces every rule:

CREATE TABLE payments (
  id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  booking_id    bigint NOT NULL ___1___,
  kind          text NOT NULL ___2___,
  amount_cents  int NOT NULL,
  provider_ref  text NOT NULL ___3___,
  created_at    timestamptz NOT NULL DEFAULT now(),
  ___4___
);

Rules: every payment belongs to a real booking; kind is one of deposit, balance, refund; refunds are negative and other kinds positive; a provider reference appears once.

Step 3 · Solve

Untangle the reviews spreadsheet

Design Practice

StayHub just bought a small review site. Its data is one table, reviews_flat, with 80,000 rows:

hotel_name, hotel_city, reviewer_name, reviewer_email, stay_month, rating, text, hotel_reply, reply_author

Known problems: some hotels appear as "Casa Azul" and "Casa Azul " (trailing space); one reviewer changed email and now looks like two people; ratings of 0 and 7 exist (the scale is 1–5); and a hotel can only reply once per review.

Design the tables (columns, keys, constraints) and say how you'd link them to StayHub's existing hotels and guests.

Step 4 · Decide

Partner metadata: columns or JSON?

Decision Stretch

The Hotel Partners team wants to store extra details hotels send through their channel managers. Each manager sends different fields: one sends "pet_policy", another "late_checkout_fee", a third 40 fields we've never seen. About 1,800 hotels, maybe 60 different field names in total, and new ones every month.

Product only needs two of them in queries today: pet policy (shown as a filter in search) and late check-out fee (shown on the booking page).

Option A: add a new column for every field. Option B: one partner_metadata jsonb column for everything. Option C: something in between.

Write a decision: "We chose X over Y because…, accepting the risk of…".

Explain it yourself

Explain why a hotel platform shouldn't store bookings in one wide table, and how keys, joins and constraints fix it.

0 words