Hotel Booking

What the interviewer is asking

"Design a hotel booking system" can look like Ticketmaster with beds, and the interviewer wants to hear where it differs. A guest books a room type, a Double rather than a numbered seat, for several nights at once, so the inventory is a count per room type per night and every booking touches a range of rows. Hotels also sell more rooms than they have, on purpose, and guests search about a thousand times for each booking.

The walkthrough follows the 8 steps of the interview framework: scope, data, API, the busy path, numbers, services, scale and deep dives.

1. Scope: what version 1 must do

• Search a city for your dates; see each hotel's free room types and prices.
• Book a room type for a range of dates.
• Pay now, or pay at the hotel.
• Cancel, which gives the nights back.

One rule shapes the rest: never sell more rooms of a type than the hotel has, except a margin it chooses on purpose. Park reviews, loyalty points, prices that change with demand, and other booking sites. Hotels often sell the same rooms on several sites through a channel manager, which makes inventory harder: each site must hear of every booking fast, or two of them sell the last room.

2. Data: an inventory row per room type per night

record       fields
hotel        id, city, name
room_type    id, hotel_id, name, total
inventory    room_type_id, night, total, booked
reservation  id, guest, room_type_id, checkin, checkout, status
payment      id, reservation_id, amount, status, provider_id

The key table is inventory: one row per room type per night, with how many rooms exist and how many are booked; hotel h12's Doubles might read 39 of 40 for Saturday. Search reads one small row per night, a booking adds 1 to each of its nights, and two bookings for the same night change the same row, which the database can guard.

One row per room per night copies Ticketmaster's seats, but guests book a Double, not room 312: you'd keep 40 rows a night instead of 1, and a stay would need one room free on every night. Keeping only reservations and counting the overlapping ones before booking is worse: two bookings can each count 39 Doubles taken for Saturday and each insert a reservation, 41 for 40 rooms. That's write skew (the Transactions and Isolation lesson): they write different rows, so nothing clashes.

3. The API: three calls

GET /hotels?city=paris&checkin=2026-06-12&checkout=2026-06-14
  → 200 OK        [{"hotel": "h12", "room_types": […]}, …]

POST /reservations  {"hotel": "h12", "room_type": "double",
                     "checkin": "2026-06-12", "checkout": "2026-06-14",
                     "idempotency_key": "k4"}
  → 201 Created   {"id": "r7"}
  → 409 Conflict  if a night has no room left

DELETE /reservations/r7

A stay runs from check-in up to, but not including, check-out. Ana arrives on Friday the 12th and leaves on Sunday the 14th, so her booking takes 2 nights, Friday and Saturday; Sunday night's room is free for the next guest. The booking carries an idempotency key (the Idempotency Keys lesson), so a retried request never books twice.

4. The busy path: searching

1. Ana's app sends GET /hotels for Paris, the 12th to the 14th.
2. The service finds the hotels in Paris (an index on city).
3. For each hotel, it reads the inventory rows of the 12th and the 13th.
4. It keeps the room types free on both nights, with their prices.

Guests search again and again before they book once, so searching, not booking, carries the load.

5. The numbers

From the interviewer: 1 million bookings a day, about 1,000 searches per booking, and 100,000 hotels of about 50 rooms, 5 million rooms in all. Assume about 5 room types per hotel, booked up to 365 nights ahead, and round a day's 86,400 seconds to 100,000.

what            estimate
bookings        1 million ÷ 100,000 s ≈ 10 a second
searches        1 million × 1,000 = 1 billion a day ≈ 10,000 a second
busy hours      5 × 10,000 = 50,000 searches a second
inventory rows  100,000 × 5 types × 365 nights ≈ 180 million
storage         180 million × 500 bytes ≈ 90 GB

With 86,400 seconds it's about 12 bookings and 11,600 searches a second: the same picture. Bookings are tiny, and the inventory fits on one database server. A row per room instead of per room type would be 5 million × 365 ≈ 1.8 billion rows, 10 times more.

6. Services: searching apart from booking

• Search service: finds hotels with free room types and prices. It only reads, so it can run on many servers and read a copy.
• Booking service: owns the inventory table, the one place that says how many rooms are left; it books, cancels and saves reservations.
• Payment provider: outside; it charges cards and sends refunds when asked.

7. Scale: a cache for search

At busy hours, 50,000 searches a second each read about 20 hotels, a page of results: 1 million lookups a second, 200 times what one database server answers (about 5,000). You'd need about 200 read replicas.

Instead, put a cache in front (cache-aside, as in the Caching lesson): one entry per hotel with its free rooms for every room type and night, kept 5 minutes. One cache server answers about 100,000 reads a second, so about 10 carry the 1 million. Reloads are cheap: at most 100,000 hotels every 300 seconds is about 330 a second. A copy a few minutes old is fine for browsing because the booking never trusts it.

8. Deep dive: date ranges

A range has as many rooms as its fullest night. With h12's Doubles at 35 of 40 on Friday and 39 of 40 on Saturday, 5 are free on Friday and 1 on Saturday, so only 1 guest can still book Friday to Sunday.

A booking is one transaction with one conditional update per night, the fix from the Concurrency and Locks lesson:

UPDATE inventory SET booked = booked + 1
WHERE room_type = 'h12-double' AND night = :night
  AND booked < total

If any night changes 0 rows, the transaction rolls back: every night or none. Nights are taken in date order, so two stays never wait on each other in a circle (a deadlock).

Ana books Friday to Sunday and Ben Saturday to Monday, at the same moment. Ana's update takes Friday (36 of 40) while Ben's takes Saturday (40 of 40); each row stays locked until its transaction ends. Ana's update on Saturday waits; Ben takes Sunday (21 of 40) and commits reservation r8. Ana's update then runs against Saturday as it is, 40 of 40, changes 0 rows, and her transaction rolls back, so Friday returns to 35. Ben has the room and nobody has half a stay. Reading the count first and writing later would have let both through.

9. Deep dive: search vs truth

Cy's search at 10:00 reads h12 from a cache entry filled at 9:58: 1 Double free on Saturday. Ben's booking takes it at 10:01. At 10:02 Cy presses Book, her update changes 0 rows, and she's told it just sold out and shown what's still free. That rare "just sold out" is the price of a fast search, and it never sells a room twice. Even an exact cache couldn't prevent it, since her page went stale while she read it: as with Ticketmaster's live seat map, the booking decides, not the search.

10. Deep dive: overbooking

Some guests cancel late or never come, say about 5% of a night's bookings, so a full hotel still has empty rooms. Hotels plan for it by overbooking, allowing up to 105%:

  AND booked < total * 1.05      -- 40 Doubles: 40 × 1.05 = 42

That's 2 more than the rooms, about the guests who stay away on an average night. But nights vary. Five Saturdays with all 42 sold:

Saturday  no-shows  arrive  empty  walked
week 1    3         39      1      0
week 2    2         40      0      0
week 3    0         42      0      2
week 4    4         38      2      0
week 5    1         41      0      1

Selling 40 with the same no-shows would leave 10 room-nights empty; selling 42 leaves 3, and 3 guests are walked: the hotel books them a room nearby and pays for it. That's the trade-off, so each hotel sets its margin from its own no-show history, per night, kept as a sell limit in the inventory row.

11. Deep dive: payments and cancellation

Reserve first, then charge. Dee books a Double for $360 and pays now. One transaction takes both nights, saves reservation r9 as pending and commits; then the booking service asks the provider to charge, with r9 as the idempotency key. If her card is declined, there's nothing left to roll back: a compensating step gives both nights back (−1 each) and marks r9 failed, the saga idea from Transactions and Isolation. Holding the transaction open through the charge would lock the nights' rows for seconds, and charging first would mean a refund whenever the last room went in between. A pending reservation that is never confirmed expires and gives its nights back, like Ticketmaster's holds.

Cancel once. If the reply to DELETE /reservations/r8 is lost and the app sends it again, a cancel that just subtracts 1 from each night does it twice: Saturday goes from 40 to 39, then 38, though 39 Doubles are still booked. Instead, set r8 to canceled only if it's still confirmed, and give the nights back only if that changed 1 row; the retry changes 0 rows. The refund carries its own idempotency key.

What breaks first

• A busy weekend in one city: the cache absorbs the searches; only bookings reach the inventory rows.
• The payment provider: charges retry safely with the key, and pending reservations expire.
• Growth: split the inventory by hotel, so a stay's nights sit on one server and a booking stays one transaction.

Summary: the order to follow

1. Scope: search, book a room type for some nights, pay, cancel.
2. Data: an inventory row per room type per night.
3. API: GET /hotels?city=&checkin=&checkout=, POST /reservations with a key, DELETE /reservations/{id}.
4. Busy path: searching, about 1,000 per booking.
5. Numbers: about 10,000 searches and 10 bookings a second; 180 million rows.
6. Services: search, booking (owns the inventory), a payment provider.
7. Scale: a 5-minute availability cache for search.
8. Deep dives: every night or none, in date order; the booking decides; overbooking up to 105%; reserve, then charge, and give nights back once.