Hotel Booking System Design: Inventory & Payments

内容更新：2026-09-28

A hotel booking platform is a two-sided marketplace with strict correctness requirements on the critical path: double-booking a room is a severe user experience failure, while incorrectly blocking available inventory costs revenue. The core design challenge is maintaining accurate room availability under concurrent bookings across a global user base, across a stay that spans multiple calendar days, with a checkout flow that must survive network failures, payment timeouts, and user abandonment — all without ever selling the same night to two different guests. Beyond that correctness challenge, the platform must serve fast, filtered, geo-aware search results across hundreds of thousands of properties, manage complex cancellation and refund policies per-hotel, and provide business analytics to hotel operators in near real time.

Step 1 — Clarify Requirements

Before drawing boxes, scope the system. Hotel booking covers a lot of ground — narrow it to a defensible, coherent subset.

Functional requirements (Hotel Admin)

• CRUD operations for hotels and rooms (name, description, amenities, location, images).
• Set room pricing — base rate, seasonal rates, minimum stay, discount rules.
• Manage availability calendar — block specific dates, set room count per night.
• View and manage bookings: confirm, cancel, modify, handle no-shows.
• Access business insights: occupancy rate, revenue, booking lead time, cancellation rate.

Functional requirements (User)

• Search hotels by city/location, check-in and check-out dates, guest count, filters (rating, amenities, price range).
• View hotel details: photos, room types, amenities, availability calendar, reviews.
• Reserve a room: select dates, guest count, add-ons; confirm with payment.
• View and manage own bookings: view confirmation, modify dates, cancel.
• Receive email/push notification on booking confirmation, reminder, and cancellation.

Non-functional requirements

• High consistency — no double-bookings; availability counts must be correct at all times.
• Low latency — search and hotel detail pages under 200 ms p99; checkout must feel instant.
• High availability — a down booking service means lost revenue; target 99.99%.
• Scalability — handles global user load and seasonal peaks (summer, holidays, major events).
• Durability — a confirmed booking is never lost.

Step 2 — Capacity Estimation

Try the QPS, bandwidth and storage calculator to vary traffic assumptions and separate peak throughput from daily stored data.

Use explicit interview assumptions: 500K properties on the platform and 1M bookings/day. These are workload assumptions, not measured Booking.com or Expedia figures.

Traffic

• Search requests: assume 50M DAU × 5 searches/day = 250M searches/day ≈ ~2,900 searches/sec average; peak (Friday afternoon, holiday season) 5× → ~14,500 searches/sec.
• Booking writes: 1M bookings/day ÷ 86,400 ≈ ~12 bookings/sec average; peak could be 10× during flash deals → ~120 bookings/sec. Manageable for SQL with proper indexing.
• Read:write ratio ≈ 250:1 — heavy caching is essential for search; booking writes are comparatively rare but must be perfectly correct.

Storage

• Hotels: assumed ~500K properties on the platform × ~5 KB per hotel record (metadata, coordinates, amenity flags) ≈ ~2.5 GB — tiny, easily fits in SQL with room to spare.
• Rooms: ~3M room types × ~2 KB ≈ ~6 GB.
• Availability table: 3M room types × 365 days × ~20 bytes per row ≈ ~22 GB/year — still SQL-manageable; partition by date to keep queries fast.
• Bookings: 1M/day × ~500 bytes × 365 days ≈ ~180 GB/year of booking records — comfortable in a single SQL instance; shard if needed.
• Media: hotel photos/videos in S3 — plan for petabytes; not in the DB.

Step 3 — API Design

Clean REST surface across the hotel admin and user-facing services:

# Hotel admin — listing management
POST   /hotels              -- create hotel
PUT    /hotels/{id}         -- update hotel
GET    /hotels/{id}         -- get hotel details
PUT    /hotels/{id}/rooms/{room_id}   -- update room
GET    /hotels/{id}/rooms             -- list rooms for hotel
PUT    /rooms/{room_id}/availability  -- set available_count per date range

# User — search and discovery
GET    /hotels/search?city=Paris&checkin=2025-07-01&checkout=2025-07-05&guests=2&max_price=200
GET    /hotels/{id}/availability?checkin=...&checkout=...

# User — booking (idempotent via Idempotency-Key header)
POST   /bookings
       Idempotency-Key: "client-uuid-123"
       { hotel_id, room_id, checkin, checkout, guests, payment_method_id }
       → 201 { booking_id, status: "RESERVED", hold_expires_at }

GET    /bookings/{id}       -- booking status and details
DELETE /bookings/{id}       -- cancel a booking (triggers refund)
PUT    /bookings/{id}/modify -- change dates (if hotel policy allows)

The POST /bookings endpoint carries an Idempotency-Key header. A retried request with the same key (double-tap, network timeout) returns the existing booking rather than creating a second one. This is essential because the booking flow involves a payment charge — without idempotency, a network glitch between the client and server can result in two charges to the guest's card.

Step 4 — Hotel and Room Listing Service

The hotel and room catalog is stored in a SQL database. The rationale is deliberately simple: the number of hotels in the world is finite and grows predictably. A relational database is the right fit — the data is structured, growth is foreseeable, and the query patterns (look up hotel by ID, list rooms by hotel) are well-suited to SQL indexes.

Hotel images and videos are stored in AWS S3 (or equivalent object storage). The SQL database holds references (URLs) to these media assets. A CDN sits in front of S3 to serve media to a global user base with low latency — fetching a hotel image from a geographically proximate CDN edge node is orders of magnitude faster than hitting the origin bucket in us-east-1.

When hotels or rooms are created or updated, the changes are published to Kafka as events. A consumer reads these events and updates the Elasticsearch search index asynchronously, keeping search results eventually consistent with the source of truth. Fully-booked rooms are removed from results as their available_count drops to zero — this removal flows through the same CDC pipeline, not through a direct Elasticsearch write on the booking path.

Step 5 — Data Model

The core tables span the hotel catalog and the booking system. All live in SQL for ACID correctness:

CREATE TABLE hotels (
  id            BIGINT PRIMARY KEY,
  name          VARCHAR(255) NOT NULL,
  city          VARCHAR(100),
  country       VARCHAR(2),     -- ISO 3166-1 alpha-2
  lat           DECIMAL(9,6),
  lng           DECIMAL(9,6),
  star_rating   SMALLINT,
  description   TEXT,
  amenities     JSONB,            -- pool, wifi, parking, pet-friendly...
  created_at    TIMESTAMP
);

CREATE TABLE rooms (
  id            BIGINT PRIMARY KEY,
  hotel_id      BIGINT REFERENCES hotels(id),
  room_type     VARCHAR(64),    -- STANDARD|DELUXE|SUITE
  max_guests    SMALLINT,
  base_rate_cents INT,
  description   TEXT
);

CREATE TABLE room_availability (
  room_id         BIGINT REFERENCES rooms(id),
  date            DATE,
  available_count INT NOT NULL CHECK (available_count >= 0),
  price_cents     INT NOT NULL, -- per-night rate for this specific date
  version         INT NOT NULL DEFAULT 0, -- optimistic lock
  PRIMARY KEY (room_id, date)
);

CREATE TABLE bookings (
  id              BIGINT PRIMARY KEY,
  user_id         BIGINT NOT NULL,
  room_id         BIGINT NOT NULL,
  checkin_date    DATE   NOT NULL,
  checkout_date   DATE   NOT NULL,
  guest_count     SMALLINT,
  total_cents     BIGINT NOT NULL,
  status          VARCHAR(32),  -- RESERVED|BOOKED|CANCELLED|COMPLETED
  idempotency_key VARCHAR(64) UNIQUE,
  payment_intent_id VARCHAR(128),
  hold_expires_at TIMESTAMP,   -- authoritative deadline; optionally mirrored in Redis
  created_at      TIMESTAMP,
  updated_at      TIMESTAMP
);

Key schema decisions: the CHECK (available_count >= 0) constraint is the database-level guard against overselling. The version column enables optimistic concurrency control. The idempotency_key UNIQUE constraint prevents duplicate bookings at the DB level. The price_cents column on room_availability captures the per-night rate at booking time — the price can change after booking but the guest's confirmed rate is locked in the booking record's total_cents.

Step 6 — Search Service

Search is powered by an Elasticsearch cluster (Solr is a comparable alternative — both are built on Apache Lucene). For a hotel platform where listings update continuously (price changes, availability, new properties), Elasticsearch is the better fit due to its real-time indexing and built-in geo-distance queries.

Search query flow

A typical search — "hotels in Paris, 2 adults, July 1–5, max €200/night" — executes as follows:

1. Elasticsearch query — filter by city = "Paris" (or geo_distance from "Paris" centroid), min star rating, amenities. Return hotel IDs sorted by relevance score (blending text match, popularity, conversion rate).
2. Availability filter — for each returned hotel, query the room_availability SQL table to verify at least one room type has available_count > 0 for every night in the requested range and price_cents <= 200 × 100. Filter out hotels with no qualifying rooms.
3. Price computation — for qualifying hotels, sum the per-night price_cents across the stay dates, apply promotions and loyalty discounts, compute the total displayed price.
4. Return results — paginated list of hotels with thumbnail, nightly rate, total stay price, availability badge.

Step 7 — Booking Service and Concurrency Control

The booking service uses a SQL database for its ACID guarantees — a double-booking is unacceptable, and ACID transactions are the clean way to prevent it. The central challenge is multi-night availability: a 5-night stay requires available_count > 0 on five separate rows in room_availability, and all five decrements must succeed atomically or all fail — there is no valid partial booking.

Pessimistic locking

A short transaction can lock every stay-date row with SELECT ... FOR UPDATE, decrement inventory, and commit. Call the payment provider after committing. Waiting on payment while holding locks is an implementation mistake, not a requirement of pessimistic locking.

Optimistic locking (preferred)

The preferred approach uses the version column on room_availability for optimistic concurrency control. No locks are held between the user's search and their booking confirmation — the transaction is only opened at the moment of the actual write:

-- Step 1: Read current state (no lock)
SELECT date, available_count, version
FROM room_availability
WHERE room_id = 42
  AND date BETWEEN '2025-07-01' AND '2025-07-04';
-- → returns 4 rows with version=7,8,7,9 and available_count=3,3,2,3

-- Step 2: Begin transaction; attempt conditional decrement on each date
BEGIN;
UPDATE room_availability
  SET available_count = available_count - 1,
      version = version + 1
  WHERE room_id = 42
    AND date = '2025-07-01'
    AND version = 7        -- optimistic lock: fails if row was modified
    AND available_count > 0; -- prevents going negative
-- Repeat for all dates in stay
-- If any UPDATE returns 0 rows affected → ROLLBACK (someone beat us to it)
-- If all succeed → INSERT booking record → COMMIT

If the version check fails (another concurrent booking modified one of the rows), the transaction rolls back immediately and the service retries with fresh data. Under low contention (most nights), the first attempt succeeds. Under high contention (popular hotel on peak weekend), the service may retry 1–3 times before succeeding or returning a "room no longer available" error. The CHECK (available_count >= 0) constraint is a second line of defense at the database level — even if application logic has a bug, the DB rejects any decrement below zero.

Step 8 — Preventing Overselling During Checkout

Interactive practice: replay the booking race and late-payment cases. Compare stale writes with an atomic decrement, then trace a delayed expiry worker, repeated callbacks and refund deduplication.

The reservation lives in SQL: decrement inventory for every night and insert a RESERVED booking with hold_expires_at in one transaction. Redis may mirror that deadline for fast reads, but deleting a Redis key does not restore SQL inventory.

1. Reserve: validate all requested nights, update rows in date order, and commit the inventory changes and booking together. A missing night or failed conditional update rolls back the entire reservation.
2. Pay: after committing, call the payment provider with a stable key for that payment operation. This example uses payment at booking time; never hold database locks during the external call.
3. Confirm: verify the webhook signature, payment identity, amount and currency. In a transaction, lock the booking row and move it from RESERVED to BOOKED only if its database deadline is still valid. Persist event deduplication and a confirmation outbox event in that transaction.
4. Expire: a scheduled worker finds overdue reservations and locks each booking row. Only the worker that changes RESERVED to HOLD_EXPIRED may restore the nights, in the same transaction. Use the same booking-then-inventory lock order as cancellation.

Redis expiry notifications can be lost during disconnects and are not a durable scheduler. Treat them as a prompt to check SQL; retain the database sweeper as the recovery mechanism.

Step 9 — Booking State Machine

Confirmation and expiry compete for the same booking row. Their transactions serialize on that row, so exactly one transition wins. A duplicate callback or cancellation must not release inventory again.

• RESERVED → BOOKED: verified payment, reservation still active; inventory was already reserved, so do not decrement it again.
• RESERVED → HOLD_EXPIRED: deadline passed; restore inventory exactly once.
• RESERVED / BOOKED → CANCELLED_BY_USER: validate the cancellation policy; restore eligible inventory once and record any required refund operation.
• BOOKED → COMPLETED: stay completed; no future inventory is released.

Write outgoing events to a transactional outbox alongside the state change, then relay them to Kafka. This closes the gap where the database commits but the process crashes before publishing. Consumers still deduplicate deliveries.

Step 10 — Payment Processing and Idempotency

For this walkthrough, payment is collected at booking time. A separate pay-at-property design must define when authorization, capture and booking confirmation occur; creating a PaymentIntent alone neither authorizes nor charges a card.

• Booking request: bind the idempotency key to the authenticated caller and a request fingerprint. Reusing a key with different dates or amounts is an error; a valid retry returns the same booking.
• Provider operation: store the PaymentIntent ID and a stable idempotency key for each create, capture or refund operation. A timeout is an unknown result, so retrieve status or retry the same operation instead of creating a new charge.
• Callbacks: verify signatures and persist processed event IDs in the same transaction as local effects. Stripe webhooks can be duplicated and delivered out of order; state guards must also reject stale transitions.

If authorization and capture are separate, configure capture_method=manual, confirm the payment, and capture before the provider's actual capture_before deadline. Do not assume a hold survives until a distant check-in date. See Stripe's authorization and capture lifecycle.

Step 11 — Cancellation and Refunds

Check the caller's permission and the cancellation terms saved at booking time. Only a successful state transition may release the room. The PostgreSQL example below couples the transition to the inventory update: on a retry, the cancelled result is empty and no inventory changes.

WITH cancelled AS (
  UPDATE bookings
  SET status = 'CANCELLED_BY_USER', updated_at = NOW()
  WHERE id = :booking_id AND status IN ('RESERVED', 'BOOKED')
  RETURNING room_id, checkin_date, checkout_date
)
UPDATE room_availability AS a
SET available_count = a.available_count + 1,
    version = a.version + 1
FROM cancelled AS c
WHERE a.room_id = c.room_id
  AND a.date >= c.checkin_date AND a.date < c.checkout_date;

This statement illustrates inventory release only. The production transaction must also record a unique refund/outbox operation when money is owed. A worker retries that operation with its original idempotency key and tracks refund completion separately from booking cancellation. If the transaction fails, both the status and inventory changes roll back. See PostgreSQL data-modifying WITH queries.

Step 12 — Availability Calendar

The availability calendar is the data structure that drives both search filtering and the room detail view ("show me which dates are available for this room type"). The room_availability table with a (room_id, date) primary key is the authoritative source. For the user-facing calendar UI, a pre-computed cache is essential:

• Cache the next 90 days of availability per room in Redis as a sorted set or a compact bitset (one bit per day, 90 days = ~12 bytes). Look up is O(1).
• Invalidate the cache entry for a room whenever its availability changes (booking, cancellation, hotel admin update).
• The hotel detail page fetches the cached calendar — typically a full cache hit since availability changes are less frequent than views for popular hotels.

Step 13 — Analytics and Reporting

The Analytics Service consumes two event streams from Kafka: search actions (what users search for, which results they click) and booking transactions (bookings, cancellations, revenue). These feeds power hotel admin dashboards and platform-level reporting:

• Occupancy rates — booked nights ÷ total available nights per room type per period.
• Revenue trends — daily/weekly/monthly revenue per hotel, with breakdown by room type.
• Booking lead time — distribution of (check-in date − booking date); tells hoteliers how far ahead guests book and informs pricing strategy.
• Cancellation rate — segmented by cancellation policy type; identifies which policies drive revenue retention.
• Search-to-book funnel — how many searches result in a hotel detail view → hold → confirmed booking; identifies drop-off points.

Analytics events flow from Kafka into a data warehouse (ClickHouse or Redshift) where SQL aggregations run without touching the operational DB. Hotel admin dashboards query the warehouse; the operational booking service is never used for analytics queries.

Step 14 — Scaling and Fault Tolerance

Hotel booking is read-dominated (searches vastly outnumber bookings) but write-critical (booking writes must be correct). The scaling strategy differs for each:

Search scaling

• Elasticsearch is horizontally scalable — add shards for larger index, add replicas for higher read throughput.
• Cache popular search queries in Redis (city + date range) with a 60–120 s TTL. Most search traffic is highly repetitive (same popular cities, same popular dates).
• The availability filter (SQL lookup after Elasticsearch) is the bottleneck under heavy search load. Mitigate with read replicas and the pre-computed availability cache in Redis.

Booking write scaling

• At ~12 bookings/sec average (120 peak), a single SQL primary handles the load comfortably. Optimistic locking keeps transactions short — no long-held row locks.
• If write scale becomes a concern: shard the room_availability table by hotel_id hash. All availability writes for a hotel go to one shard; no cross-shard join needed for a booking (a guest books one hotel per booking).
• Co-locate bookings and inventory by hotel_id to preserve local transactions; maintain a user-keyed read projection for "my bookings".

Fault tolerance

• Redis failure — read reservation state from SQL. Inventory reservations, deadlines and expiry recovery already live in the database and do not depend on Redis availability.
• Payment processor timeout — reuse the payment object ID and operation idempotency key; show processing while unknown and reconcile within the reservation lifecycle.
• Kafka failure — bookings continue; Elasticsearch falls slightly behind (search may show sold-out rooms until Kafka recovers and the index catches up). The DB is always the source of truth — Elasticsearch is a downstream projection.
• DB primary failure — automatic failover to a standby replica (e.g., PostgreSQL with Patroni or Amazon RDS Multi-AZ). Brief read-only window during failover; new bookings queue and retry when the new primary is elected.

Step 15 — Why Not Cassandra for Bookings?

Unlike e-commerce order history (high volume, append-heavy, suitable for Cassandra archival), hotel booking volume is low enough that a SQL primary can retain the full booking history without a dedicated archival service. The correctness benefits of ACID transactions outweigh the scalability benefits of NoSQL at this data volume. Specifically:

• 1M bookings/day × 365 days × 500 bytes ≈ 180 GB/year — well within SQL's sweet spot.
• ACID multi-row updates (decrement availability across multiple dates + insert booking record in one transaction) are not natively supported by Cassandra's lightweight transactions (LWT are per-partition and limited).
• Cassandra's eventual consistency model would require complex application-level conflict resolution to prevent double-bookings — exactly the problem SQL's row-level constraints solve trivially.

Step 16 — Key Tradeoffs

Practice the related concepts

Compare single-item inventory and cross-service checkout in ecommerce system design. Review transaction boundaries in DDIA Chapter 7 and timeouts and partial failure in Chapter 8.