Design a Hotel Reservation System

Availability & Booking System Design

Overview

A hotel reservation system lets a traveler search hotels in a city, see which room types are free for their check-in / check-out dates, book a room for those nights, and later cancel or modify — the core of Booking.com, Expedia, Marriott, or Hilton. It looks like a shopping cart, but it has a shape that ordinary e-commerce does not: you are not buying one item, you are reserving a room type for every night in a date range, and each night has its own limited supply.

That single fact drives the whole design. Availability is not "is this product in stock?" — it is "does this hotel have at least one room of this type free on the 12th and the 13th and the 14th?" The unit of inventory is a room-night, and a stay spans several of them.

It is labeled "Medium" because the naive designs break in teachable ways. If you model one database row per physical room, you drown in rows and joins and still answer the wrong question. If you decrement availability for the check-in night but forget the middle nights, you oversell a partial range. And if you treat the read-heavy search path and the write-heavy booking path the same way, one starves the other.

A strong answer models inventory as counts per (hotel, room_type, date) — an availability calendar — reserves every night in the range atomically so two guests can't both take the last room-night, keeps booking idempotent, releases inventory cleanly on cancel or expiry, and serves search from a cached, eventually-consistent read path while booking stays strongly consistent.

Functional requirements

• Search hotels by location and dates. Given a city (or map area) and a check-in/check-out range, return hotels that have at least one room type available for every night in the range, with filters (price, rating, amenities).
• Book a room type for a date range. Reserve one (or more) rooms of a chosen type for the whole [check_in, check_out) span, without overselling beyond intended overbooking.
• Cancel or modify a reservation. Release the held room-nights back to inventory (cancel), or shift the dates/room type (modify = release old nights + reserve new ones atomically).

Out of scope (say so): payment processing internals (assume a payment service exists, made idempotent like the booking), dynamic pricing engines, hotel-side admin tooling, and reviews. We keep availability + the multi-night booking transaction firmly in scope — that is the whole problem.

Non-functional requirements

• No overselling (beyond intended overbooking). For a given (hotel, room_type, date), confirmed reservations must not exceed the sellable count — unless the hotel deliberately overbooks by a small margin (covered in a deep dive). Booking is the strongly consistent path.
• Read-heavy search, low latency. Search vastly outnumbers booking — most visitors browse, few book. Search must stay fast and highly available, and can tolerate slightly stale availability; it is eventually consistent and heavily cached.
• Atomic multi-night reservation. A stay touches one inventory cell per night. Either all nights in the range are reserved or none are — a partial reservation (some nights taken, one full) is a bug that both oversells and strands the guest.
• Correct release on cancel/expiry. Cancelling a reservation, or an unpaid hold timing out, must return the exact room-nights it consumed — every night in its range — to inventory.

Estimations

State assumptions; the goal is to justify the read-vs-write split and the inventory model, not to be exact.

Rounded assumptions

• Hotels: ~1M worldwide; average ~100 rooms each, across ~5 room types → ~1.8B room-type-days of inventory in a 1-year booking window (1M hotels × 5 room types × 365 days/year ≈ 1.8B cells; or if you trim to a 90-day sellable window: 1M × 5 × 90 ≈ 450M ≈ 500M — either way it is small).
• Bookings/day: ~10M platform-wide → ~115 bookings/sec average, call it ~1K bookings/sec at peak.
• Searches: read-heavy. Assume ~100 searches per booking (people browse a lot) → ~100K–1M searches/sec at peak. This is the number that shapes the design.
• Average stay: ~3 nights → each booking writes ~3 inventory cells (one per night) in one transaction.

How the numbers affect the design

Storage

• Per inventory cell: hotel_id (8), room_type_id (8), date (4), total (2), reserved (2) ≈ ~24 bytes. 1.8B cells × 24 bytes ≈ ~43 GB — still small and fits comfortably in a sharded relational store. The calendar is not a storage problem; it is a concurrency and query problem.
• Reservations grow over time but are append-mostly and modest.

Core entities / data model

The central modeling decision: inventory is counts per (hotel, room_type, date), not one row per physical room. You never care which physical room #214 someone gets at booking time (the hotel assigns that at check-in); you care whether a room of that type is free that night.

Reservation.status moves PENDING → CONFIRMED (and → CANCELLED on cancel, or PENDING → EXPIRED if an unpaid hold times out). Availability on a date is simply total_rooms - reserved_count > 0. Storing a count rather than a row-per-room is what makes a booking a small update on a handful of cells instead of a scan-and-lock over hundreds of physical rooms.

Inventory lives in a strongly consistent relational store (Postgres/MySQL) partitioned by hotel_id, so one hotel's rows — and one hotel's contention — stay on one shard and the multi-night reservation is a local, single-shard transaction. Search data (hotel metadata, location, price, amenities, a rolled-up availability hint) is denormalized into a search index + cache, decoupled from the transactional calendar.

API design

A RESTful API covers the three steps: search, book, cancel/modify. Search is the read-heavy, cacheable path; booking is the strongly consistent one.

Search hotels for a date range

GET /api/search?city=paris&check_in=2026-08-12&check_out=2026-08-15
    &guests=2&price_max=300&rating_min=4&amenities=wifi,pool
200 OK
Returns: { "hotels": [ { "hotel_id": "...", "name": "...", "min_price": 210,
           "available_room_types": ["king","double"] }, ... ] }   (cached)

Return hotels with at least one room type free for every night in [check_in, check_out). Read-heavy and tolerant of slight staleness, so this is served from a search index + cache, not the inventory DB. The date range is half-open: check_out is the morning you leave, so you consume nights 12, 13, 14 — not the 15th.

Book a room type for the range

POST /api/reservations
Header: Idempotency-Key: <client-generated-uuid>
Body: { "hotel_id": "...", "room_type_id": "king", "check_in": "2026-08-12",
        "check_out": "2026-08-15", "rooms": 1 }
201 Created
Returns: { "reservation_id": "...", "status": "CONFIRMED" }   (or 409 if any night is full)

Reserve one room of the type for the whole span. The Idempotency-Key is mandatory: a network retry must not create a second reservation or double-decrement inventory. Returns 409 Conflict if any night in the range is full — this is the step that must be race-free and atomic across all nights (deep dive 2).

Cancel a reservation

DELETE /api/reservations/{id}
200 OK
Returns: { "status": "CANCELLED" }

Release the room-nights back to inventory — one increment per night in the range, in one transaction. A modify is the same idea: one transaction over the union of old-night-release and new-night-reserve cells — old nights increment back, new nights decrement with the same guard — so a failed modify atomically leaves both ranges unchanged and the guest keeps their original reservation unchanged (they just couldn't change dates).

High-level architecture

Two paths dominate, and they are deliberately different: a search path (read-heavy, cached, eventually consistent) and a booking path (write, strongly consistent, atomic across nights), plus a background worker that expires unpaid holds.

1. Search path

Search is ~100× booking volume and tolerates slight staleness, so it never touches the transactional calendar directly.

flowchart LR
    Client[Client] -->|"search city plus dates"| GW[API Gateway]
    GW --> Search[Search Service]
    Search -->|"geo plus filter query"| Index[(Search Index / Cache)]
    Index -->|"candidate hotels"| Search
    Search -->|"availability hint"| Client

1. A search resolves the city/map area and filters against a search index (hotels by location, price, rating, amenities) with an availability hint attached.
2. Results come back from the index/cache; the transactional inventory DB is not in this path at all. Deep dive 3 covers how geospatial search and the availability check combine, and how the hint is kept fresh.

2. Booking path

Booking is the strongly-consistent path: reserve every night in the range atomically, hold until paid, then confirm.

flowchart LR
    Client[Client] -->|"book type plus range"| Booking[Booking Service]
    Booking -->|"decrement every night atomically"| InvDB[(Inventory DB - per hotel)]
    Booking -->|"charge idempotent"| Pay[Payment Service]
    Booking -->|"on success mark CONFIRMED"| InvDB
    Expiry[Hold-Expiry Worker] -->|"release unpaid holds"| InvDB

1. The Booking Service opens one transaction and decrements availability for every night in [check_in, check_out) with a guard that fails if any night is full (deep dive 2). All nights or none.
2. The reservation is held PENDING while the guest pays; payment is idempotent so a retry can't double-charge.
3. On success the reservation flips to CONFIRMED. If the guest abandons, a hold-expiry worker releases the room-nights back to inventory.

3. Combined architecture

Putting it together — the search index is fed asynchronously from the inventory DB so search stays fresh without coupling to the booking transaction:

flowchart LR
    Client[Client] --> GW[API Gateway]
    GW -->|"search"| Search[Search Service]
    Search --> Index[(Search Index / Cache)]
    GW -->|"book / cancel"| Booking[Booking Service]
    Booking -->|"decrement or release every night"| InvDB[(Inventory DB - per hotel)]
    Booking -->|"charge idempotent"| Pay[Payment Service]
    Expiry[Hold-Expiry Worker] -->|"release unpaid holds"| InvDB
    InvDB -.->|"availability change stream via CDC"| Index

The dotted Inventory DB → Search Index edge is how search stays fresh: every committed availability change streams out via change data capture (CDC) and updates the search index/cache asynchronously — so the read path reflects recent bookings within seconds without ever joining the booking transaction.

Deep dives

1. Availability / inventory model — the room-night calendar

The foundational decision. Availability is not a single number per hotel; it is a grid of counts indexed by (hotel, room_type, date). Picture a calendar per room type where each date holds "how many of this type remain sellable that night."

Why counts, not rooms. Two models compete:

One (hotel, room_type) is a row of per-date cells; a stay is a contiguous slice of them:

flowchart LR
    subgraph King["Hotel H - King - availability calendar"]
        D12["Aug 12<br/>8 / 10"]
        D13["Aug 13<br/>10 / 10 FULL"]
        D14["Aug 14<br/>3 / 10"]
        D15["Aug 15 check-out<br/>not consumed"]
    end
    Stay["Stay 12th to 15th<br/>consumes nights 12, 13, 14"] --> D12
    Stay --> D13
    Stay --> D14

Querying "available for every night in the range." The core availability question is: is this room type sellable on every date in [check_in, check_out)? With the calendar model that's a range scan over the date rows for one (hotel, room_type), asserting reserved_count < total_rooms on all of them:

Available for the whole stay 12th–14th only if EVERY night has room:
  (hotel=H, type=King, date=2026-08-12) → reserved 8  / total 10  → free ✓
  (hotel=H, type=King, date=2026-08-13) → reserved 10 / total 10  → FULL ✗
  (hotel=H, type=King, date=2026-08-14) → reserved 3  / total 10  → free ✓
  → NOT available for the stay: the 13th is full, so the 3-night range fails

Half-open range: a stay checking in the 12th and out the 15th consumes nights 12, 13, 14 — never the 15th (you've left that morning). Getting this boundary wrong either oversells the checkout day or wastes a night's inventory.

2. Booking without overselling — multi-night atomic decrement

This is the correctness core. A stay spans multiple nights; you must reserve all of them or none, and two concurrent bookings must never both take the last room on a shared night.

The failure to avoid: partial-range oversell. If you decrement nights one at a time in separate statements, a booking can grab nights 12 and 14, discover 13 is full, and either oversell (if you don't roll back) or leave a mess. The fix is one transaction covering every night, with a guard.

Guarded multi-night decrement. In a single transaction, issue one conditional update per night — or better, a single set-based update over the date range — that only succeeds if every night still has room, and bump reserved_count where it does. Then assert you touched exactly nights rows; if not, roll back the whole transaction:

Book King, 12th–14th (3 nights), 1 room (:rooms = 1), in ONE transaction:
  UPDATE inventory
    SET reserved_count = reserved_count + :rooms
    WHERE hotel_id=H AND room_type_id=King
      AND date IN (2026-08-12, 2026-08-13, 2026-08-14)
      AND reserved_count + :rooms <= total_rooms
  -- rows updated must equal 3 (one per night)
  IF rows_updated <> 3 THEN ROLLBACK  -- some night was full → 409, no oversell
  ELSE COMMIT                          -- all 3 nights reserved atomically

The reserved_count + :rooms <= total_rooms predicate is the guard: it's a conditional update, so any night where adding the requested rooms would exceed capacity is simply not updated, the row count comes up short, and the whole reservation rolls back. The guard generalises correctly to multi-room bookings — a 2-room request (rooms=2) on a night with only 1 slot free (reserved=9, total=10) fails the predicate where reserved_count + 1 <= total_rooms would not (9+1 ≤ 10 is true, allowing the oversell). Because it's one transaction on one shard (partitioned by hotel_id), it's atomic — no partial range escapes.

Every (hotel, room_type, date) cell in the booking window is pre-materialized — an inventory row exists for every date before any booking arrives (consistent with the ~1.8B-cell estimate). This means rows_updated < nights unambiguously means "a night was full or already at capacity," not "a row was missing," so the 409 is always accurate.

The booking flow holds inventory only for the tiny decrement, then pays outside the transaction:

sequenceDiagram
    participant C as Client
    participant B as Booking Service
    participant I as Inventory DB
    participant P as Payment Service
    C->>B: book King 12th-14th, Idempotency-Key
    B->>I: BEGIN decrement all 3 nights guarded
    alt every night had room
        I-->>B: 3 rows updated, COMMIT
        B->>P: charge idempotent
        P-->>B: paid
        B->>I: mark reservation CONFIRMED
        B-->>C: 201 CONFIRMED
    else some night full
        I-->>B: fewer than 3 rows, ROLLBACK
        B-->>C: 409 pick another
    end

Why two concurrent bookings can't both take the last room-night. The row update serializes on the contended cell:

Two guests race for the last King room on the 13th (reserved 9 / total 10):
  T1: BEGIN; UPDATE ... + :rooms WHERE ... AND reserved_count + :rooms <= total_rooms  → row now 10/10
  T2: BEGIN; UPDATE ... + :rooms WHERE ... AND reserved_count + :rooms <= total_rooms  → 0 rows (10+1 > 10, guard fails)
  T1: touched all 3 nights → COMMIT ✓
  T2: the 13th updated 0 rows → rows_updated < 3 → ROLLBACK → 409 "just booked"
  → exactly one guest gets the last room-night; the other loses cleanly, no oversell

Two consistency mechanisms fit here. Optimistic locking — the guarded conditional update above — is the sensible default: no locks held, high throughput at low contention (most hotels/dates aren't hot). Under genuine contention on a hot date (a sold-out festival weekend), a brief pessimistic SELECT ... FOR UPDATE on the night rows (locked in a consistent date order to avoid deadlock) serializes contenders instead of thrashing on retries. Both keep the transaction tiny — the decrement only, never held across payment.

Idempotent booking. Network timeouts make clients retry. Store the client's Idempotency-Key with the reservation; a retry with the same key returns the original reservation instead of decrementing inventory a second time — a unique constraint on the key makes the second insert a no-op that reads back the first result. Without this, one "book" click becomes two rooms consumed.

Releasing on cancel or expiry. Cancel is the mirror image: in one transaction, increment reserved_count back by :rooms (the room count stored on the reservation) for every night in the reservation's range and flip status to CANCELLED. Unpaid PENDING holds are released the same way by a background expiry worker after a TTL, so an abandoned checkout doesn't lock room-nights forever. Make release idempotent too (guard on current status) so a double-cancel doesn't over-credit inventory and manufacture phantom rooms.

3. Search — geospatial + availability, on a read-heavy cached path

Search is the high-volume path (~100× booking): "hotels in this city/map area, available for these dates, filtered by price/rating/amenities." Two problems combine — find hotels near a location, and among those find ones available for the whole range — and it must be fast and cheap.

Geospatial candidate lookup. First narrow by location. Indexing hotels by raw lat/lng and scanning is too slow at 1M hotels, so use a geospatial index — geohash, quadtree, or S2 cells — so "hotels within this map box / near this point" reads a handful of cells instead of scanning the world. The same technique appears in Yelp/nearby-places designs; it recurs whenever the query is "things near a point." The search index (e.g. Elasticsearch) holds hotel metadata, the geo cell, price, rating, and amenities so the location narrowing and the attribute filters happen together in one query.

Search narrows by geo and filters, checks the cached hint, and defers the exact per-night check to booking:

flowchart TB
    Q["Search city plus dates plus filters"] --> Geo["Geo index<br/>hotels in map area"]
    Geo --> Filt["Filter price, rating, amenities"]
    Filt --> Hint{"Availability hint<br/>any room in window?"}
    Hint -->|"yes"| Results["Candidate hotels returned"]
    Hint -->|"no"| Drop["Excluded"]
    Results -->|"guest picks one, clicks book"| Exact["Exact per-night check<br/>at booking, deep dive 2"]

Combining with the availability check. Location + filters yields candidate hotels; you still need "available every night in the range." Two orderings:

• Filter-then-verify (recommended): the search index carries a coarse per-hotel availability hint — precomputed "has any room type with availability in this date window," refreshed from the inventory calendar via CDC. Search returns candidates using the hint; the exact per-night check runs only at the book step for the one hotel the guest picks. This keeps search off the transactional calendar entirely and is why the hint is a hint, not a promise.
• Verify-every-hotel (avoid): running the exact multi-night calendar check against the inventory DB for every candidate on every search would hammer the transactional store with the read-storm — the opposite of the read/write split. Don't.

Caching the read path. Search is dominated by popular (city, date-range) queries. Cache full search results with a short TTL (tens of seconds to a couple minutes) keyed by the normalized query; a busy city-weekend query hits cache instead of re-running geo + filter every time. The CDC stream that updates the availability hint also invalidates affected cached queries so a fresh booking greys out availability within seconds. Near-static data (hotel name, location, amenities) can cache far longer than volatile availability.

Tradeoffs & bottlenecks

• Count-based inventory vs row-per-room. Counts per (type, date) make availability a comparison and a booking a tiny update — the right call. Row-per-room is only worth it if you must sell a specific physical room (rare); otherwise it's needless rows, scans, and contention.
• Strong consistency for booking vs eventual for search. Booking is CP: reject or 409 rather than oversell. Search is AP: cached, eventually consistent, occasionally shows a room that's just gone. Naming this split is the central tradeoff — you deliberately pay staleness in search to keep booking correct and search fast.
• Intentional overbooking vs strict. Airlines and hotels routinely oversell by a small margin because a predictable fraction of guests cancel or no-show; selling exactly to capacity leaves rooms empty and revenue lost. Model it by extending the booking guard to reserved_count + :rooms <= total_rooms + overbook_margin (the same guard from DD2, with the margin as a tunable dial per hotel/date from historical cancel rates) — with a walk/relocation policy for the rare night everyone shows up. Strict no-overbooking is simpler and guest-friendly but leaves money on the table. This is a business dial, not a bug.
• Optimistic vs pessimistic locking on hot dates. Optimistic (guarded conditional update) wins at low contention — most dates. A sold-out festival weekend is a hot row where optimistic retries thrash; a brief pessimistic lock (in consistent date order) serializes contenders cleanly there.
• Hot-hotel / hot-date hotspot. Partitioning by hotel_id keeps a hotel's contention on one shard — but a viral hotel or a sold-out city on a marathon weekend concentrates load there. Caching search and keeping booking transactions tiny keep that shard from tipping.
• Hold TTL length. Too short and guests lose rooms mid-checkout; too long and abandoned holds lock inventory and depress sell-through. Tune to real checkout times.

Extensions if asked

Add only the extension that changes the design discussion:

• Dynamic pricing. Price per room-night varies by demand, day-of-week, and remaining supply — add a pricing service that reads occupancy from the calendar; store price per (room_type, date) alongside the count so a stay's total sums the nightly prices.
• Group bookings. Reserving 20 rooms for a wedding block is a larger multi-cell decrement (N rooms × M nights) in one transaction, with all-or-nothing semantics and possibly a partial-availability negotiation.
• Channel managers / OTA sync. A hotel sells the same rooms on Booking.com, Expedia, and its own site; a channel manager syncs inventory across channels. This reintroduces overselling risk across systems and needs a reconciliation/CDC sync — the same eventual-consistency-plus-authoritative-check pattern.
• Waitlists. When a date is full, let guests join a waitlist and notify them (like a hold offer) when a cancellation frees a room-night.
• Loyalty / rate plans. Member rates, refundable vs non-refundable plans, and points redemption layer pricing and policy rules onto the same inventory model.

What interviewers look for & common mistakes

What interviewers usually reward:

• Modeling inventory as room-night counts per (hotel, room_type, date) — an availability calendar — not a row per physical room.
• Reserving every night in the range atomically in one guarded transaction, so no partial-range oversell and no double-take of the last room-night.
• Idempotent booking and correct release on cancel/expiry — every night credited back, guarded against double-cancel.
• Splitting the read-heavy cached search path from the write-heavy strongly-consistent booking path, and feeding search freshness via CDC.
• Naming intentional overbooking as a business dial (like airlines), distinct from accidental oversell.

Before you finish, do a quick mistake check:

• Did you model inventory as counts per (room_type, date), not a row per physical room?
• Did you decrement (and release) every night in the range atomically — no partial-range oversell?
• Did you use the half-open [check_in, check_out) range so the checkout night isn't consumed?
• Did you make booking idempotent and cancellation correct (credit every night, once)?
• Did you serve search from a cached, eventually-consistent path and keep booking strongly consistent?
• Did you distinguish intentional overbooking from accidental overselling?