Practice/Oracle/Design a Hotel Booking System

Design a Hotel Booking System

System DesignMust

Problem Statement

Design a hotel booking system that allows users to search for hotels, make reservations, and manage bookings. The system should support room availability tracking, pricing rules, and reservation management with strong transactional guarantees to prevent double-booking.

Think of Booking.com or Expedia: a massive catalog of hotels, fast search by location and dates, room types with dynamic pricing, and a checkout flow that must prevent two users from booking the last room simultaneously. Interviewers ask this to test whether you can model real-world constraints in a relational schema, handle high-contention reservations with strong transactional semantics, and separate search from booking for scale.

Key Requirements

Functional

• Hotel search -- users search for hotels by location, dates, number of guests, and filters (price range, amenities, star rating)
• Room pricing -- users view room types with accurate nightly prices, fees, taxes, and total cost for their stay
• Reservation -- users book a room for specific dates with payment and receive a confirmation
• Booking management -- users view, modify (when policy allows), and cancel their reservations

Non-Functional

• Scalability -- support millions of listed rooms and handle search traffic that dwarfs booking traffic by orders of magnitude
• Reliability -- guarantee no double-booking; a confirmed reservation must hold the room exclusively
• Latency -- return search results in under 500ms; complete booking checkout in under 3 seconds
• Consistency -- strong consistency for inventory and reservations; eventual consistency acceptable for search results and pricing cache

What Interviewers Focus On

Based on real interview experiences, these are the areas interviewers probe most deeply:

1. Preventing Double-Booking Under Contention

Room inventory is scarce and highly contended during peak times. This is the most critical design challenge.

Hints to consider:

• Use row-level locking or atomic conditional updates (UPDATE ... WHERE available_count > 0) to prevent race conditions
• Implement short-lived inventory holds with TTL (e.g., 10 minutes) while the user completes payment
• Design the reservation as a two-phase process: hold inventory first, then capture payment, then confirm
• Handle hold expiration gracefully: release inventory back to the pool if payment is not completed

2. Data Model for Availability and Pricing

Multi-night stays overlap, and pricing varies by date, room type, and demand. Interviewers expect a per-night granularity model.

Hints to consider:

• Model availability at the per-room-type, per-night level (hotel_id, room_type_id, date, available_count, price)
• For a booking spanning multiple nights, check and decrement availability for each night within a single transaction
• Support dynamic pricing: base rates adjusted by demand multipliers, seasonal rules, and promotional discounts
• Consider how cancellations and modifications restore availability across the affected date range

3. Separating Search from Booking

Search traffic is orders of magnitude higher than booking traffic. Interviewers look for a clear separation strategy.

Hints to consider:

• Use a search engine (Elasticsearch) with denormalized hotel data for fast filtering, sorting, and geo queries
• Sync availability data from the OLTP database to the search index asynchronously (e.g., via change data capture)
• Accept that search results may show slightly stale availability, but verify availability at booking time
• Cache popular search results (e.g., "hotels in Paris this weekend") with short TTLs for repeat query performance

4. Booking Workflow and Failure Handling

The booking process involves multiple steps that can fail independently. Interviewers probe your approach to maintaining consistency.

Hints to consider:

• Design the workflow as a saga: hold inventory, authorize payment, confirm reservation, send confirmation email
• Implement compensating actions for each step: if payment fails after inventory hold, release the hold
• Use idempotency keys for the entire booking flow to handle client retries without creating duplicate reservations
• Handle partial failures: what happens if the confirmation email fails but the booking succeeded?