Hotel Reservation System

Design a hotel reservation system for a chain (e.g. Marriott). Applicable to Airbnb, flight reservation, movie ticket booking.

Step 1 of 4: Understand the problem and establish design scope #

Goal: lock in requirements and scale.

Requirements #

1. 5,000 hotels, 1 million rooms total
2. Customers pay in full when making reservations
3. Booking via hotel website or app
4. Customers can cancel reservations
5. 10% overbooking allowed (hotels sell more rooms than available, anticipating cancellations)
6. Hotel prices change dynamically per day based on expected occupancy
7. Features: hotel/room detail pages, reserve a room, admin CRUD for hotel/room info, overbooking support

Non-functional:

1. High concurrency (peak season/big events → many users booking the same room)
2. Moderate latency (a few seconds to process a reservation is acceptable)

Back-of-the-envelope estimation:

1. 1M rooms, 70% occupancy, avg stay 3 days
2. Daily reservations: (1M × 0.7) / 3 = ~240,000
3. Reservations per second: 240,000 / 86,400 ≈ 3 TPS (transactions)
4. QPS funnel (10% conversion per step, no prefetching):

Figure 1. QPS distribution: detail page 300 QPS → booking page 30 QPS → reservation 3 TPS

Step 2 of 4: Propose high-level design and get buy-in #

Goal: agree on the API, data model, and microservice split.

API design #

Hotel-related APIs:

Room-related APIs:

Reservation-related APIs:

POST /v1/reservations request body:

{
  "startDate": "2021-04-28",
  "endDate": "2021-04-30",
  "hotelID": "245",
  "roomID": "U12354673389",
  "reservationID": "U12354673390"
}

reservationID serves as the idempotency key to prevent double booking.

Data model #

Access patterns:

1. View hotel details
2. Find available room types by date range
3. Record a reservation
4. Look up reservation / history

Why relational database:

1. Read-heavy, write-light workflow (visitors >> bookers): NoSQL is optimized for writes, RDBMS works well for reads
2. ACID guarantees (prevent negative balance, double charge, double reservations)
3. Stable, clear relationships between entities (hotel, room, room_type)

Figure 2: Database schema

Reservation status state machine (pending → paid/refunded/canceled/rejected):

Figure 3: Reservation status

Key issue with the initial schema: users reserve a type of room (standard, king, queen), not a specific room. Room numbers are assigned at check-in. Requires an improved data model (see Deep Dive).

High-level design #

Microservice architecture:

Figure 4: High-level design

Components:

1. CDN: caches static assets (JS, images, videos, HTML)
2. Public API Gateway: rate limiting, authentication, routes requests to services
3. Internal APIs: hotel staff only, protected by VPN
4. Hotel Service: hotel/room info (static, easily cached)
5. Rate Service: room rates for future dates (price depends on expected occupancy)
6. Reservation Service: receives reservation requests, tracks room inventory
7. Payment Service: executes payment, updates reservation status
8. Hotel Management Service: staff-facing admin operations

Services communicate internally (e.g. Reservation Service queries Rate Service for room rates). Inter-service comm uses gRPC.

Figure 5: Connections between services

Step 3 of 4: Design deep dive #

Goal: improved data model, concurrency, scaling, cross-service consistency.

Improved data model #

Reservation API changes: roomID → roomTypeID + roomCount:

{
  "startDate": "2021-04-28",
  "endDate": "2021-04-30",
  "hotelID": "245",
  "roomTypeID": "12354673389",
  "roomCount": "3",
  "reservationID": "U12354673390"
}

Figure 6: Updated schema

Key tables:

1. room: room information
2. room_type_rate: price data per room type for future dates
3. reservation: guest reservation data
4. room_type_inventory: inventory per hotel/room_type/date. Columns: hotel_id, room_type_id, date, total_inventory, total_reserved. Composite PK: (hotel_id, room_type_id, date). Pre-populated for 2 years via a daily scheduled job

Storage estimation: 5,000 hotels × 20 room types × 2 years × 365 days = 73M rows: fits in a single database. Achieve HA via replication across regions/AZs.

Table 4: Sample room_type_inventory data

Inventory check SQL:

SELECT date, total_inventory, total_reserved
FROM room_type_inventory
WHERE room_type_id = ${roomTypeId} AND hotel_id = ${hotelId}
AND date BETWEEN ${startDate} AND ${endDate}

Application checks: if (total_reserved + ${numberOfRoomsToReserve}) <= total_inventory

10% overbooking: if (total_reserved + ${numberOfRoomsToReserve}) <= 110% * total_inventory

Scaling beyond a single DB:

1. Archive old reservation history (current + future only; past → cold storage)
2. Shard by hash(hotel_id) % number_of_servers

Concurrency issues #

Two problems: (1) the same user clicks "book" multiple times, (2) multiple users book the same room simultaneously.

Problem 1: Idempotency key:

1. Client-side: gray out/hide the "submit" button (unreliable)
2. API: reservation_id as idempotency key; a unique constraint on the primary key prevents duplicates

Figures 7-9: Double reservation, unique constraint, confirmation page

Workflow:

1. Customer enters details → reservation service generates reservation_id (globally unique)
2. System displays a confirmation page with reservation_id
3. Submit: reservation_id is the PK → second submit violates the unique constraint → rejected

Figure 10: Unique constraint violation

Problem 2: Race condition (multiple users, last room):

Figure 11: Race condition

Without serializable isolation: both transactions see 99 reserved (1 left), both book → double booking.

Three locking approaches:

Option 1: Pessimistic locking

SELECT ... FOR UPDATE locks rows. Other transactions wait.

• Pros: easy, prevents conflicts, good for heavy contention
• Cons: deadlocks possible, not scalable (long locks block others)
• Verdict: NOT recommended

Figure 12: Pessimistic locking

Option 2: Optimistic locking

Version number column. Read version → update increments version by 1. DB validates next_version = current_version + 1.

• Pros: no DB locks, fast when contention is low
• Cons: poor performance under heavy contention (many retries)
• Verdict: good option for a hotel system (low reservation QPS)

Figure 13: Optimistic locking

Option 3: Database constraints

CONSTRAINT `check_room_count` CHECK((`total_inventory - total_reserved` >= 0))

• Pros: easy to implement, good for low contention
• Cons: high failure rate under heavy contention; not version-controlled; not all DBs support
• Verdict: good option for a low-QPS hotel system

Figure 14: Database constraint

Reservation SQL pseudo-code:

-- Step 1: check inventory
SELECT date, total_inventory, total_reserved
FROM room_type_inventory
WHERE room_type_id = ${roomTypeId} AND hotel_id = ${hotelId}
AND date BETWEEN ${startDate} AND ${endDate};

-- For every row returned:
if ((total_reserved + ${numberOfRoomsToReserve}) > 110% * total_inventory) { Rollback }

-- Step 2: reserve rooms
UPDATE room_type_inventory
SET total_reserved = total_reserved + ${numberOfRoomsToReserve}
WHERE room_type_id = ${roomTypeId}
AND date BETWEEN ${startDate} AND ${endDate};
COMMIT;

Scalability #

For booking.com/expedia scale (QPS 1,000× higher):

Database sharding: shard by hotel_id. 16 shards × 30,000 QPS → ~1,875 QPS per shard.

Figure 15: Database sharding

Caching (Redis):

1. Only current + future inventory matters → TTL/LRU eviction
2. Pre-populate inventory in Redis: key: hotelID_roomTypeID_{date}, value: available rooms
3. Read path: check Redis first → if available, validate against the DB (source of truth)
4. Write path: update DB first → propagate to cache asynchronously (CDC/Debezium or app code)
5. Cache-DB inconsistency is acceptable: DB does final validation. If cache says available but DB says full → error returned, cache syncs on next refresh

Figure 16: Caching

Data consistency among services #

Hybrid approach: the Reservation Service owns both reservation and inventory tables in the same relational database → leverage ACID. If a microservice purist insists on separate DBs:

Figure 17: Monolithic vs microservice

Figures 18-19: Monolithic vs microservice architecture

Solutions for cross-service consistency:

1. 2PC (Two-phase commit): blocking protocol, atomic across nodes. A single node failure blocks progress. Not performant
2. Saga: sequence of local transactions. Each step publishes a message for the next. On failure, compensating transactions undo preceding steps. Eventual consistency

Pragmatic choice: store reservation + inventory in the same DB: the complexity of distributed transactions is not worth it here.

Step 4 of 4: Wrap up #

What we designed:

1. Requirements → estimation → API/data model → architecture
2. Room-type-level reservations (not specific rooms)
3. Race condition solutions: pessimistic/optimistic locking, DB constraints
4. Scaling via sharding + Redis cache
5. Microservice data consistency (2PC, Saga)

Key design decisions: relational DB for ACID; hybrid service boundaries for pragmatic consistency.