c4EN

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

Step 1 - Understand the Problem and Establish Design Scope

Requirements:

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

Non-functional requirements:

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

Back-of-the-envelope estimation:

• 1M rooms, 70% occupancy, avg stay 3 days
• Daily reservations: (1M × 0.7) / 3 = ~240,000
• Reservations per second: 240,000 / 86,400 ≈ 3 TPS (transactions)
• 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 - Propose High-Level Design and Get Buy-In

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:

• View hotel details
• Find available room types by date range
• Record a reservation
• Look up reservation / history

Why relational database:

• Read-heavy, write-light workflow (visitors >> bookers) — NoSQL is optimized for writes, RDBMS works well for reads
• ACID guarantees (prevent negative balance, double charge, double reservations)
• 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 initial schema: Users reserve a type of room (standard, king, queen), not a specific room. Room numbers are assigned at check-in. Requires improved data model (see Deep Dive).

High-level design #

Microservice architecture:

Figure 4: High-level design

Components:

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

Note: 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 - Design Deep Dive

Topics: improved data model, concurrency issues, scaling, data consistency in microservices.

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:

• room: room information
• room_type_rate: price data per room type for future dates
• reservation: guest reservation data
• 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 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 single DB:

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

Concurrency issues #

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

Problem 1 solution — Idempotency key:

• Client-side: gray out/hide "submit" button (unreliable)
• API: reservation_id as idempotency key, unique constraint on 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 confirmation page with reservation_id
3. Submit: reservation_id is PK → second submit violates 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 low
• Cons: poor performance under heavy contention (many retries)
• Verdict: Good option for 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 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):

• Only current + future inventory matters → TTL/LRU eviction
• Pre-populate inventory in Redis: key: hotelID_roomTypeID_{date}, value: available rooms
• Read path: check Redis first → if available, validate against DB (source of truth)
• Write path: update DB first → propagate to cache asynchronously (CDC/Debezium or app code)
• 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: Reservation Service owns both reservation and inventory tables in same relational database → leverage ACID. If microservice purist insists on separate DBs:

Figure 17: Monolithic vs microservice

Figures 18-19: Monolithic vs microservice architecture

Solutions for cross-service consistency:

• 2PC (Two-phase commit): blocking protocol, atomic across nodes. Single node failure blocks progress. Not performant.
• 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 same DB — complexity of distributed transactions not worth it here.

Step 4 - Wrap Up

Designed hotel reservation system: requirements → estimation → API/data model → architecture. Deep dive covered: room-type-level reservations, race condition solutions (pessimistic/optimistic locking, DB constraints), scaling via sharding + Redis cache, microservice data consistency (2PC, Saga). Key design decisions: relational DB for ACID, hybrid service boundaries for pragmatic consistency.