Design Hotel Booking

A guest types "Lisbon, 12 to 15 June, 2 guests", scrolls through a few hundred places, opens ten of them, and books one. Behind that is a stays marketplace: hotels list rooms, people rent out homes, and the site has to say which of them are free on those exact nights.

It looks like ticket booking with rooms instead of seats, and the rule is the same: never sell one thing twice. What differs is the shape of the inventory and the traffic. A stay covers several nights, so one booking claims several rows at once. There is no on-sale spike; instead, people search hundreds of times per booking, filtering by availability that keeps changing. The steps follow A Framework for Any Design. Try each "Your turn" box before you read on.

#Clarify the problem

• Functional: search by place, dates and guest count; view a listing with photos, prices and a calendar; book one or more rooms of a type for a date range; pay; cancel.
• Non-functional: no night sold beyond its limit (strong consistency on inventory); search under 300 ms, allowed to lag bookings by seconds; listing pages under 200 ms; holds last 15 minutes; a confirmed booking is never lost.
• Out of scope: reviews, host payouts, guest-host messaging, price setting, loyalty programmes.

#Estimate the scale

A day has 86,400 seconds. Assume the busiest hour of the year runs at 5 times the average.

• Bookings: 500,000 ÷ 86,400 ≈ 6 a second, about 30 at peak. One database primary handles that.
• Searches: 500,000 × 100 = 50 million a day, which is 50,000,000 ÷ 86,400 ≈ 600 a second, about 3,000 at peak.
• Listing views: 500,000 × 200 = 100 million a day, about 1,200 a second, 6,000 at peak.
• Bookings stored: 500,000 × 365 ≈ 180 million a year, about 180 GB at 1 KB each.
• Photos: 1 million properties × 20 photos × 500 KB = 10 TB, served from a CDN and stored in object storage.

#Define the API

GET  /search?near=38.72,-9.14&check_in=2027-06-12&check_out=2027-06-15&guests=2&cursor=...
  200 { "results": [ { "property_id": 501, "room_type_id": 42,
                       "total_cents": 39000, "photo": "..." }, ... ],
        "next_cursor": "..." }
 
GET  /properties/{id}                        static content, cacheable for minutes
GET  /properties/{id}/availability?from=2027-06-01&to=2027-08-01
  200 { "room_types": [ { "id": 42, "nights": [ { "date": "2027-06-12",
          "free": 3, "price_cents": 13000 }, ... ] } ] }
 
POST /reservations          Idempotency-Key: 7f3c...
  body: { "property_id": 501, "room_type_id": 42,
          "check_in": "2027-06-12", "check_out": "2027-06-15",
          "rooms": 1, "guests": 2 }
  201 { "reservation_id": 88001, "status": "held",
        "held_until": "...", "total_cents": 39000 }
  409 Conflict      not enough rooms on 2027-06-14; body lists similar options
  400 Bad Request   check_out not after check_in, or more than 365 days ahead
 
POST /reservations/{id}/pay
  200 { "status": "confirmed" }
  402 Payment Required   declined; the hold stays until it expires
  410 Gone               the hold expired
 
POST /reservations/{id}/cancel

The API decision worth arguing is how to write a stay. Use two plain dates in the property's own calendar, no time, no time zone. The stay is the nights from check-in up to but not including check-out: 12 to 15 June is the nights of the 12th, 13th and 14th. UTC timestamps invite off-by-one bugs, because midnight in Lisbon is not midnight on the server.

The listing page is two calls on purpose: the static part caches well for minutes, while availability and price change with every booking.

#Model the data

room_types
  property_id    BIGINT
  room_type_id   BIGINT
  name           VARCHAR(100)   -- "Double room, sea view"
  max_guests     SMALLINT
  total_units    SMALLINT       -- 1 for a home
  PRIMARY KEY (property_id, room_type_id)
 
inventory                        -- one row per room type per night
  property_id    BIGINT
  room_type_id   BIGINT
  night          DATE           -- local date at the property
  total_units    SMALLINT
  overbook_limit SMALLINT       -- extra units the hotel allows; 0 for homes
  reserved       SMALLINT       -- held + confirmed
  price_cents    INT
  PRIMARY KEY (property_id, room_type_id, night)
 
reservations
  reservation_id BIGINT
  property_id    BIGINT
  room_type_id   BIGINT
  user_id        BIGINT
  check_in       DATE
  check_out      DATE           -- exclusive
  rooms          SMALLINT
  status         VARCHAR(10)    -- held | paying | confirmed | expired | cancelled
  held_until     TIMESTAMP
  total_cents    INT
  idem_key       VARCHAR(64) UNIQUE
  PRIMARY KEY (property_id, reservation_id)

A hotel sells a room type, not room 214; the front desk picks the room at check-in. So inventory is a count per type per night, and a home is the same thing with total_units = 1.

A booking changes several rows of one property all-or-nothing, which points to a relational database with transactions. Every key leads with property_id, so if you partition by property later, a booking never crosses partitions. A guest's list of trips is a separate table by user_id, written after the booking commits.

#High-level design

Searches and page views, 9,000 a second at peak, read copies that may be seconds old. Bookings, 30 a second, are the only writers of inventory.

#Deep dives

#Reserving several nights at once

A three-night stay is three rows that must all have room. Check and claim them in one atomic step, or a guest gets a booking with a hole in it.

BEGIN;
UPDATE inventory
   SET reserved = reserved + 1
 WHERE property_id = 501 AND room_type_id = 42
   AND night >= '2027-06-12' AND night < '2027-06-15'
   AND reserved + 1 <= total_units + overbook_limit;
-- rows changed must be 3 (the nights 12, 13, 14). Fewer: ROLLBACK, return 409.
INSERT INTO reservations (..., status, held_until)
     VALUES (..., 'held', now() + interval '15 minutes');
COMMIT;

Why it is safe: an updated row stays locked until commit, so a second transaction that wants it waits. In databases such as PostgreSQL at read committed, the waiting update then re-checks its WHERE clause against the new value, sees the night is full and skips it, so the row count is short and you roll back. At stricter isolation levels it fails with a serialization error instead, which also becomes a 409 (see transactions and isolation). Overlapping stays can lock shared nights in opposite orders and deadlock; the database aborts one, and you retry it once or lock nights in date order first.

#Searching by availability

"Places free on 12 to 15 June" is the hard query, because availability changes with every booking.

Each room type's document in the search index carries its list of sold-out nights. A room type fits a stay if none of the stay's nights is in the list: one "must not match" filter. The change stream updates that field only when a night's free count reaches zero or leaves it, applied per room type in log order so a late "free" never overwrites a newer "sold out". The place is a geo filter in the same query (see Design Proximity Search).

The list only knows "at least one free", so a family wanting three rooms may see a hotel with one left. The search service therefore rechecks the 25 results it is about to show in one batched replica read: 25 room types × 3 nights = 75 rows, one query per search, 3,000 a second at peak. Results that fail are dropped.

Listing pages are the other read. The static part sits on a CDN for five minutes and is purged when the host edits it; the calendar is about 60 rows from a replica. None of the 6,000 views a second reach the primary.

#Holds, payment and expiry

Reserve the nights, then charge; charging first leaves a paid guest with no room whenever the claim fails. The flow is the one from ticket booking: a hold, a paying state the sweeper never touches, and an idempotent charge (Design a Payment System handles unknown results). Two things differ.

• Expiry needs a writer. A seat row carries its own held_until, so an expired hold reads as free. A count of 3 cannot say one of the three is stale, so the sweeper releases it: UPDATE reservations SET status = 'expired' WHERE id = 88002 AND status = 'held' AND held_until < now(), and only if that changed one row, subtract from the nights in the same transaction. The status check makes it safe to run twice.
• Authorize now, capture later. Many stays are free to cancel until a deadline, so the booking authorizes the card, holding the funds, and captures later. Card network rules make an authorization lapse after days, so for a stay months ahead, save the card as a token and authorize nearer the date.

#Overbooking on purpose and by accident

Some guests never arrive. In our scenario, a 100-room hotel with about 5 no-shows per 100 bookings may choose to sell 104. The overbook_limit column lets each hotel set this per room type, and the platform enforces it exactly. It is 0 for homes. The hotel decides, because the hotel pays when it has to send a guest elsewhere.

Accidental overbooking is the bigger risk. A hotel often sells the same rooms on several sites, which learn of each other's bookings through a sync service, with lag. Two sites can each sell the last room in the same minute, and no transaction in your database can stop it. Push your bookings out within seconds to shrink the window, and plan for the rest: a relocation process that finds a comparable room and pays the difference.

#Bottlenecks and failures

At 10x, 30,000 searches a second, the index needs work first: shard it by region, since every query names a place, and add replicas. The rechecks become 30,000 reads a second, so add inventory replicas or an availability cache fed by the same change stream. Bookings at 300 a second still fit one primary; partition by property_id when you want headroom or isolation.

#What a strong answer sounds like

#Follow-up questions

#Prices change per night. How do you show the total?

Each inventory row has its own price, so the total is the sum over the stay's nights. Copy it onto the reservation when the hold is taken, so a price change mid-checkout does not change what the guest agreed to.

#A guest cancels. What changes?

One transaction moves the reservation to cancelled and subtracts from each night, guarded by the status like the sweeper. The refund follows the property's policy through the payment system.

#How would you add request-to-book, where the host approves?

The same hold, with a longer expiry, say 24 hours, and a card authorization rather than a charge. The host's accept confirms the reservation; a decline or a timeout releases the nights and the authorization.

#A festival fills one city's hotels. Does the design hold?

Contention is per room type and night, so guests spread over hundreds of hotels rarely collide. The pressure is on search: many identical queries, which a short-lived cache of result pages absorbs.

#A hotel takes 5 rooms out for repairs. What happens?

Lower total_units on those nights in one transaction. If reserved is now above the limit, cancel nobody automatically: the nights show as sold out, and the extra bookings go to the same relocation process as accidental overbooking.