Design Hotel Reservation

What are we designing? A hotel reservation system. A user searches for available hotels by city + date range + guest count; sees results with prices; books a specific room type; the system holds the inventory, takes payment, and confirms. Other users searching concurrently must not see the same room as available. Cancellations return inventory to the pool.

The defining challenges are (1) inventory as a time-cube — a hotel has rooms of multiple types, and each room type has independent availability per night, so the inventory is a (hotel_id, room_type_id, date) cube, (2) concurrency control — when two users race for the last room, exactly one must win and the other must see 'unavailable', and (3) overbooking — airlines and hotels deliberately sell more seats than they have, betting on no-shows; we must support this safely.

Functional requirements.

• Search hotels by city, check-in date, check-out date, guest count, with filters (stars, amenities, price).
• View real-time availability and price for a specific hotel + room type + date range.
• Book a room: hold inventory for ~15 min during payment, then commit on success or release on failure.
• Cancel a booking (with policy: free >48h, 50% >24h, 0% <24h).
• View/manage bookings; receive confirmation email/SMS.
• Hotel staff can manage inventory, prices, room types, blackouts.

Non-functional requirements.

• Search latency: p99 < 500 ms (searches span thousands of hotels).
• Booking latency: < 2 s end-to-end.
• Concurrency: handle two users racing for the last room — never double-book.
• Availability: 99.95% (bookings are revenue).
• Scale: 1M hotels, 50M room-nights sold per day, 10K searches/sec peak.

Non-goals. No dynamic pricing ML (rule-based pricing only in v1), no loyalty program, no multi-room bookings (single room per reservation in v1).

Capacity estimation.

Inventory size. 1M hotels x avg 5 room types x 365 days forward = 1.8B inventory rows (hotel_id, room_type_id, date, available_count, price). Each row ~50 bytes = 90 GB — fits in a sharded SQL DB.

Search traffic. 10K searches/sec peak. Each search filters hotels by city + date range + filters; returns ~100-1000 results. Read-heavy: 100x more searches than bookings.

Booking volume. 50M room-nights/day = ~5M bookings/day = ~60 bookings/sec. Each booking touches ~3 inventory rows (one per night) for a 3-night stay.

Holds. Every booking creates a temporary hold during payment. Holds expire after 15 min if payment doesn't complete. Peak concurrent holds ~10K.

Storage. Booking records ~1KB each = ~5 GB/day, ~2 TB/year. Audit trail (every state transition, who/when/why) ~5x = ~10 TB/year. Manageable in SQL with archiving.

APIs.

# Search
GET /v1/hotels?city=NYC&checkin=2025-01-10&checkout=2025-01-13&guests=2
  -> { hotels: [{id, name, stars, price_total, room_types: [...]}] }

# Hold inventory for a booking (15 min TTL)
POST /v1/holds
  { hotel_id, room_type_id, checkin, checkout, guest_count, idempotency_key }
  -> { hold_id, expires_at, price_total }
  -> 409 if no availability

# Confirm booking (after payment)
POST /v1/bookings
  { hold_id, payment_token, guest_info, idempotency_key }
  -> { booking_id, status: confirmed }

# Cancel
POST /v1/bookings/:id/cancel
  -> { status: cancelled, refund_amount }

# Get booking
GET /v1/bookings/:id

The two-step hold-then-confirm pattern is critical. Without holds, you'd either book without payment (open to abuse) or charge payment before checking availability (bad UX if it fails). The 15-min hold gives payment time to complete.

Data model.

Inventory (sharded SQL, sharded by hotel_id):

inventory (
  hotel_id, room_type_id, date,
  total_count, available_count, price_cents, version,
  PRIMARY KEY (hotel_id, room_type_id, date)
)

The version column is for optimistic locking. The available_count decrements when a hold is created and increments when released.

Holds (Redis, ephemeral):

hold:{hold_id}  -> { hotel_id, room_type_id, dates: [...],
                     guest_count, price_total, expires_at }
TTL = 900 (15 min)

Holds are in Redis (ephemeral) but the inventory decrement is in SQL (durable). On hold expiry, a sweeper increments available_count back.

Bookings (sharded SQL, sharded by booking_id):

bookings (
  id PK, user_id, hotel_id, room_type_id, checkin, checkout,
  status, price_cents, payment_id, created_at, cancelled_at,
  version,
  INDEX (user_id, created_at),
  INDEX (hotel_id, checkin)
)

Booking state machine:

searching -> holding -> payment_pending -> confirmed -> (checked_in -> checked_out -> completed)
                                  \-> failed (hold released)
                                  \-> cancelled (per policy)

Audit trail (append-only):

booking_events (booking_id, ts, event, actor, payload, PRIMARY KEY (booking_id, ts))

Every state transition appends an event — never updates. This is the event-sourcing pattern, applied narrowly for auditability.

Deep dive: concurrency control — pessimistic vs optimistic.

The race. Two users, Alice and Bob, both see the last remaining single-king room at the Marriott NYC for Jan 10-12. Both click 'book' simultaneously. The system must let exactly one succeed and reject the other.

Pessimistic locking: SELECT ... FOR UPDATE. When Alice's request enters the transaction, SELECT ... FOR UPDATE on the inventory rows (Jan 10, 11, 12 for that hotel+room_type) acquires row-level locks. Bob's SELECT FOR UPDATE blocks until Alice's transaction commits. After Alice commits (available_count = 0), Bob's SELECT sees 0 and returns 409. Simple and correct.

Pros: simple, no retry needed. Cons: lock contention under high concurrency — if 100 users race for the last room, 99 of them block; throughput collapses. Locks held during payment = bad (15 min).

Optimistic locking: version column. No lock acquired. Read inventory with version V. Check available_count >= 1. Then UPDATE inventory SET available_count = available_count - 1, version = version + 1 WHERE ... AND version = V. If the rowcount is 0, someone else changed it — retry the whole transaction (or fail).

Pros: no lock contention, scales much better under contention. Cons: retry overhead on contention; need explicit version column.

Our choice: optimistic locking for the hold (short transaction), no lock held during payment. The hold transaction completes in <100ms — no lock during the 15-min payment window. This is critical: we never hold a row lock for the duration of a payment.

Inventory at the date grain. Don't have one row per hotel+room_type with a 'dates_booked' array — you can't atomically check 'is Jan 10-12 all free' without scanning. Instead, one row per (hotel, room_type, date) and a transaction that locks/updates all rows in the range. SQL's row-level locks make this efficient.

Deep dive: overbooking and holds.

Overbooking math. Airlines and hotels deliberately sell more seats/rooms than they have, betting that some customers will no-show. If a hotel has 100 rooms and historical no-show rate is 5%, they can sell 105 rooms and expect 100 to show up. The expected cost of an oversell (rebooking a guest at another hotel + apology) is lower than the expected cost of an empty room.

Implementation. The total_count column is the physical count (100). The overbookable_count is the saleable count (105). Holds decrement from available_count, which can go below total_count - overbookable_count (i.e. we can oversell by up to overbookable_count - total_count = 5). When available_count < 0 we are in overbook territory; the search still shows availability but a flag is set so ops can intervene.

Walk rate management. 'Walk' = relocating an overbooked guest to another hotel. Ops dashboards show overbook count per hotel + date; if projected walks exceed threshold, ops stop new bookings or call partner hotels. The math is statistical — actual no-show rate varies; ops intervene on exceptions.

Holds and abandonment. ~30% of holds expire without payment — users change their mind, payment fails, etc. During those 15 min, the room appears unavailable to others. This is intentional (we want to honor the hold), but means effective availability is lower than the inventory count. Overbooking compensates for this: if 30% of holds expire, sell ~30% more than physical capacity.

Idempotent holds. A user might double-click 'book' or retry after a network blip. The hold API requires an idempotency_key; the Inventory Service stores idempotency_key -> hold_id in Redis for 24h. Duplicate requests within 24h return the original hold_id. Same pattern as Stripe.

Cancellation policies. Cancellations are not just 'set status to cancelled' — they have a refund policy. Free >48h before checkin, 50% >24h, 0% <24h. The Cancellation Service computes refund based on policy, calls Payment Service to issue refund, increments inventory.available_count back, publishes 'booking_cancelled' event.

Bottlenecks and failure modes.

• Hot hotel contention. A popular hotel during a festival has 1000 users racing for 50 rooms. Mitigation: optimistic locking with bounded retries (3); after 3 retries, return 'temporarily unavailable, please try again' rather than infinite retry storms.

• Hold expiration storms. A flash sale ends; 10K holds expire simultaneously. Mitigation: sweep in batches over 5 min, not all at once; rate-limit inventory increments.

• Payment service timeout. Payment intent takes 30+ seconds (3DS challenge). Hold expires before payment completes. Mitigation: extend hold TTL when payment is in-flight; or use a 'payment_in_progress' state distinct from 'holding'.

• Cross-shard transactions. A booking touches inventory (sharded by hotel_id) and bookings table (sharded by booking_id) — different shards. Mitigation: don't use distributed transactions; use the saga pattern — first decrement inventory (commit), then create booking (with hold_id link). If booking fails, compensate by incrementing inventory back.

• Search query cost. 'Available hotels in NYC for Jan 10-12' must check inventory for all NYC hotels x all dates. Mitigation: precompute availability per hotel for the next 365 days; index by (city, available_count > 0, date_range); cache search results in Redis for 30s.

• Time zone bugs. A 'date' is ambiguous without timezone. NYC midnight vs UTC midnight differ. Mitigation: always store dates in the hotel's local timezone; explicitly model check-in time as hotel-local.

• Stale cache in search. Search results cached 30s; a hotel might sell out in those 30s. Mitigation: when user clicks 'book', the hold call validates against the DB (not the cache) and returns 409 if unavailable — the user sees 'sorry, just booked'.

Scaling strategy and trade-offs.

Inventory sharding. Shard by hotel_id. A hotel's inventory for all dates lives on one shard — enables a single-shard transaction for a booking. 1M hotels / 100 shards = 10K hotels per shard.

Search read replicas. Search is read-heavy (100x bookings). Read replicas for search; writes go to primary.

Precomputed availability. Daily job computes 'is hotel X available for date range Y' for the next 90 days, stores in a denormalized table for fast search. Avoids the per-hotel inventory scan on every search.

Multi-region. Hotels are inherently regional (a Tokyo hotel is searched mostly by users in Asia). Deploy per-region with the inventory for that region's hotels. Cross-region replication for global search.

Trade-offs made explicit.

• We chose optimistic locking for holds — gained high concurrency under contention, lost simplicity (must retry on version conflict).
• We chose 15-min hold TTL — gained payment time, lost effective availability during the hold window (mitigated by overbooking).
• We chose overbooking at the saleable_count level — gained revenue optimization, lost the risk of walking a guest (managed by ops dashboards).
• We chose saga over 2PC for cross-shard booking — gained availability (no global coordinator), lost the need for explicit compensation logic on failure.
• We chose date-grain inventory (one row per night) — gained atomic multi-night checks, lost some storage (3 rows for a 3-night stay vs 1) — acceptable.
• We chose 30s search cache TTL — gained search throughput, lost 30s of staleness (acceptable; hold call validates against DB).

Mental model. Think of Design Hotel Reservation as a deliberate boundary in a system. The boundary exists because something becomes harder to manage when everything is done in one place: latency, scale, failure isolation, consistency, cost, or team ownership. The useful question is not “what does Design Hotel Reservation mean?” but “what pressure makes this boundary worth introducing, and what new failure mode does it create?”

Numerical lens: write down traffic, payload size, read/write ratio, peak multiplier and durability target before choosing a component. The numbers should justify the architecture.

Answer this without notes: When would you choose Design Hotel Reservation, and when would you intentionally avoid it? Mention at least one bottleneck it addresses, one failure mode it introduces, and one alternative. Then quantify the workload you are designing for.

Numerical sanity check

Back-of-the-envelope reasoning beats fake precision. State your traffic, payload, concurrency and growth assumptions explicitly, then calculate enough to know whether the current architecture is orders of magnitude away from the target.