System Design Interview: Design a Hotel Reservation System

By /
⏱ 5 min read

What Is a Hotel Reservation System?

A hotel reservation system manages room inventory, bookings, availability search, and payments for hotels. Examples: Booking.com, Expedia, hotel property management systems. Core challenges: preventing double-booking (two guests booking the same room on the same dates), real-time availability across thousands of hotels, and handling the booking flow atomically (reserve inventory → charge payment → confirm).

  • DoorDash Interview Guide
  • Lyft Interview Guide
  • Uber Interview Guide
  • Shopify Interview Guide
  • Stripe Interview Guide
  • Airbnb Interview Guide

    • System Requirements

    Functional

    • Search available rooms by location, dates, room type, guests
    • Book a room: reserve, charge, confirm
    • Cancel a booking with refund based on cancellation policy
    • Hotel management: add rooms, set pricing, view bookings

    Non-Functional

    • 500K hotels, 10M rooms
    • Search: <200ms, 10K searches/second
    • No double-bookings under concurrent load
    • 99.99% uptime during peak season

    Core Data Model

    hotels: id, name, location_id, star_rating, amenities
room_types: id, hotel_id, name, max_guests, base_price, total_count
room_inventory: id, room_type_id, date, available_count, price_override
bookings: id, user_id, room_type_id, check_in, check_out,
          total_price, status, payment_id, created_at

    For a search (hotel_id, check_in, check_out, guests): query room_inventory for all dates in the range. A room type is available only if available_count > 0 for EVERY date in the range.

    SELECT room_type_id, MIN(available_count) as min_available
FROM room_inventory
WHERE hotel_id = ?
  AND date >= check_in
  AND date  0

    Index on (hotel_id, date) makes this efficient. Pre-generate room_inventory rows for all room types for the next 365 days when a hotel is created.

    Preventing Double-Booking

    Two users search at the same time, both see 1 room available, both click book. Without coordination: both succeed and the same physical room has two reservations. Solution: pessimistic locking with SELECT FOR UPDATE across all relevant inventory rows inside a transaction.

    BEGIN TRANSACTION;
-- Lock all inventory rows for this booking
SELECT available_count FROM room_inventory
WHERE room_type_id = ? AND date >= ? AND date < ?
FOR UPDATE;
-- Check all dates still have availability
IF MIN(available_count) = ? AND date < ?;
-- Create booking
INSERT INTO bookings (...) VALUES (...);
COMMIT;

    The FOR UPDATE acquires row-level locks on all inventory rows. The second concurrent transaction must wait until the first commits or rolls back. No double-booking possible.

    Booking Flow

    User clicks "Book"
  → Reserve inventory (DB transaction, FOR UPDATE)
  → Hold reservation for 10 minutes (status = pending)
  → Redirect to payment page
  → Process payment (Stripe)
  → On payment success: confirm booking (status = confirmed)
  → On payment failure: release inventory (rollback reservation)
  → After 10-minute timeout: auto-release if payment not completed

    The 10-minute hold prevents holding inventory indefinitely if the user abandons checkout. A background job cancels expired pending bookings and increments available_count back.

    Pricing: Dynamic Rates

    Hotels set base prices; pricing engine adjusts based on demand. price_override column in room_inventory stores the adjusted price for a specific date. Computed by a nightly pricing job using occupancy forecasts. At search time, return the price for the specific dates requested (not just base_price).

    Caching Availability

    Availability search results cached in Redis with 30-second TTL. Key: “avail:{hotel_id}:{check_in}:{check_out}:{guests}”. Cache is invalidated on any booking for the same hotel. Trade-off: a user may see “1 room available” but lose the race at booking time — this is acceptable. The error message “this room was just booked” is a standard user experience on booking platforms.

    Interview Tips

    • room_inventory table with one row per (room_type, date) is the key data model — enables per-night pricing and availability.
    • SELECT FOR UPDATE across all date rows is the correct double-booking prevention.
    • 10-minute reservation hold + background cleanup is the checkout flow standard.
    • Availability caching is approximate — the DB transaction is the source of truth.
    Scroll to Top