Design a Hotel Reservation System

Requirements

Guests search hotels, pick a room type and dates, and pay. Hotels manage their rooms and rates. The heart of the problem is inventory: a room type has a fixed number of rooms per night, and two guests must never be confirmed for the last one.

Functional

• Search hotels by city and dates; show available room types and prices.
• Reserve a room type for a date range, then pay to confirm.
• Cancel a reservation (with policy-based refunds).
• Hotel admins add/update rooms, inventory and rates.
• Configurable controlled overbooking (e.g. allow 105% of capacity).

Non-functional

• No unintended double booking: inventory correctness is critical.
• High read throughput for search; reads outnumber bookings ~100–1000×.
• Moderate latency: search under ~300 ms, booking under ~1–2 s (includes payment).
• High availability, especially during holiday peaks and flash sales.

Capacity Estimates

Assume 5,000 hotels × 1,000 rooms on average = 5 M rooms, 70% occupancy, 3-night average stay.

Booking writes are low enough for a single relational primary per region. Search traffic is the scaling challenge, and it can be served from caches and replicas because it tolerates slightly stale data.

API Design

GET  /v1/hotels/search?city=Paris&checkIn=2026-12-20&checkOut=2026-12-23&guests=2
  200: [{ "hotelId": 77, "name": "...", "minPrice": 18900, "available": true }, ...]

GET  /v1/hotels/{hotelId}/availability?checkIn=...&checkOut=...
  200: [{ "roomTypeId": 3, "name": "Deluxe King", "remaining": 4, "totalPrice": 56700 }]

POST /v1/reservations
  Headers: Idempotency-Key: 1c8e...
  Body: { "hotelId": 77, "roomTypeId": 3, "checkIn": "2026-12-20",
          "checkOut": "2026-12-23", "guestId": 501 }
  201:  { "reservationId": "r_9001", "status": "PENDING", "holdExpiresAt": "..." }
  409:  { "error": "SOLD_OUT" }

POST /v1/reservations/{id}/confirm   { "paymentToken": "pm_tok..." }
POST /v1/reservations/{id}/cancel

PUT  /v1/admin/hotels/{hotelId}/roomtypes/{rtId}/inventory   (hotel staff)

• The idempotency key ensures a retried "Book" click does not create two reservations.
• A reservation starts as a short-lived hold (e.g. 10 minutes) while the guest pays.

Data Model

CREATE TABLE room_type_inventory (
  hotel_id        BIGINT,
  room_type_id    BIGINT,
  stay_date       DATE,
  total_inventory INT NOT NULL,
  total_reserved  INT NOT NULL DEFAULT 0,
  version         INT NOT NULL DEFAULT 0,         -- for optimistic locking
  PRIMARY KEY (hotel_id, room_type_id, stay_date),
  CHECK (total_reserved <= total_inventory * 1.05) -- overbooking cap
);

CREATE TABLE reservations (
  reservation_id  VARCHAR(32) PRIMARY KEY,
  idempotency_key VARCHAR(64) UNIQUE NOT NULL,
  hotel_id        BIGINT, room_type_id BIGINT, guest_id BIGINT,
  check_in        DATE, check_out DATE,
  status          VARCHAR(16),   -- PENDING, CONFIRMED, CANCELLED, EXPIRED
  hold_expires_at TIMESTAMP,
  total_price     BIGINT
);

CREATE TABLE rates (
  hotel_id BIGINT, room_type_id BIGINT, stay_date DATE, price_minor BIGINT,
  PRIMARY KEY (hotel_id, room_type_id, stay_date)
);

Inventory is stored per room type per night. A 3-night stay touches 3 inventory rows, and all must be updated atomically.

High-Level Design

flowchart LR
  User --> CDN[CDN for static content]
  User --> GW[API Gateway]
  GW --> SS[Search Service]
  GW --> RS[Reservation Service]
  GW --> HS[Hotel Admin Service]
  SS --> ES[(Search Index)]
  SS --> AC[(Availability Cache)]
  RS --> DB[(Inventory and Reservations DB)]
  RS --> PAY[Payment Service]
  HS --> DB
  DB -->|CDC events| BUS[[Event Bus]]
  BUS --> AC
  BUS --> ES
  EXP[Hold Expiry Worker] --> DB

• Search reads from a search index (Elasticsearch) for hotels and from an availability cache for "is anything left". Results may lag the database by seconds; the authoritative check happens at booking time.
• Reservation Service owns the inventory database. It reserves inventory in one transaction, then hands off to payment.
• Change data capture streams inventory changes to refresh the cache and index.
• The expiry worker releases holds whose payment never completed.

Deep Dives

Concurrency control options

The classic race: one Deluxe King left on Dec 20. Two guests click "Book" at the same moment; both read reserved = 9, total = 10, both write reserved = 10, and both get confirmed. Three standard fixes:

1. Pessimistic locking

BEGIN;
SELECT total_inventory, total_reserved FROM room_type_inventory
 WHERE hotel_id=77 AND room_type_id=3 AND stay_date BETWEEN '2026-12-20' AND '2026-12-22'
 FOR UPDATE;
-- application checks every night has space
UPDATE room_type_inventory SET total_reserved = total_reserved + 1 WHERE ...;
INSERT INTO reservations ...;
COMMIT;

Rows are locked until commit, so the second transaction waits and then sees the updated count. Safe and easy to reason about, but locks reduce throughput and can deadlock if transactions lock dates in different orders (always lock in ascending date order).

2. Optimistic locking — read the row with its version, then:

UPDATE room_type_inventory
   SET total_reserved = total_reserved + 1, version = version + 1
 WHERE hotel_id=77 AND room_type_id=3 AND stay_date='2026-12-20' AND version = 41;

If 0 rows are updated, someone else won; retry or fail. No locks held, so it is fast when contention is low, but under heavy contention (flash sale) most attempts fail and retry.

3. Database constraint / conditional update

UPDATE room_type_inventory SET total_reserved = total_reserved + 1
 WHERE hotel_id=77 AND room_type_id=3 AND stay_date BETWEEN ... 
   AND total_reserved + 1 <= total_inventory;

Check the affected row count equals the number of nights; otherwise roll back. The database's row-level atomicity does the work. Simple and efficient, though the business rule now lives partly in SQL.

Holds, payment and expiry

Booking is a two-step process: reserve inventory, then pay. Holding inventory while the guest types card details prevents "it was available a minute ago" failures.

1. Create the reservation as PENDING with hold_expires_at = now + 10 min, incrementing total_reserved in the same transaction.
2. On successful payment, flip to CONFIRMED with a conditional update (WHERE status = 'PENDING').
3. The expiry worker (or a delayed queue message) finds expired PENDING rows, marks them EXPIRED and decrements inventory, again conditionally so it cannot race with a late confirmation.
4. If payment succeeds after the hold already expired, refund automatically or try to re-reserve.

Overbooking

Hotels intentionally oversell because a few percent of guests cancel or do not show up. Model it as a per-room-type ratio: allow total_reserved <= total_inventory × 1.05. The check is the same conditional update with a different limit. Front-desk tools handle the rare case where everyone shows up (upgrade or relocate).

Search vs booking consistency

Availability shown in search is a hint, not a promise.

• Cache per (hotel, room type, date) remaining counts with a short TTL, refreshed by CDC events.
• Because inventory changes slowly relative to reads, a few seconds of staleness is fine.
• The booking transaction is the single source of truth; if it fails with SOLD_OUT, refresh the page with alternatives.
• Prices follow the same pattern: show cached prices but re-quote and lock the price in the reservation at booking time.

Partitioning the data

Almost every query is scoped to one hotel, so partition inventory and reservations by hotel_id. A multi-night booking then stays within one shard and one local transaction. Guest-centric queries ("my trips") are served by a secondary index or a separate read model keyed by guest_id, populated asynchronously.

Scaling & Reliability

• Read path: CDN for images and static pages, search index replicas, availability cache, DB read replicas. This absorbs 99%+ of traffic.
• Write path: a relational primary per shard with synchronous standby; low TPS makes this comfortable.
• Hot hotels during flash sales: put a queue or token bucket in front of the reservation endpoint for that hotel so the database sees a steady stream instead of a thundering herd.
• Idempotency on reservation and confirmation endpoints handles client retries.
• Microservice boundaries: if reservation and payment are separate services, use a saga (reserve → pay → confirm, with compensating release) rather than a distributed transaction.
• Channel managers: inventory is also sold through other travel sites; sync via an event feed and keep a small buffer to absorb propagation delay.

Trade-offs

Interview Tips

• Model inventory as rows per room type per night early; everything else builds on it.
• Show the double-booking race with concrete numbers, then present all three fixes and pick one with reasons.
• Keep transactions short; explain holds and expiry instead of long-lived locks.
• Separate the stale-tolerant search path from the strongly consistent booking path.
• Mention idempotency keys for the Book button and deadlock-avoiding lock ordering.
• Bring up overbooking; it shows you understand the business, not just the database.

References

• System Design Primer: consistency patterns and RDBMS/ACID
• PostgreSQL docs: Explicit locking (row-level locks, deadlocks)
• PostgreSQL docs: Transaction isolation
• Martin Fowler: Optimistic Offline Lock
• Karan Pratap Singh: System Design (databases, transactions)