Problem Overview
Design a hotel or short-term rental booking system like Airbnb or Booking.com. Users search for available properties in a location and date range, view details, and book. Hosts manage listings and availability. The system must handle inventory correctly — two users cannot book the same room for the same dates — while serving millions of search requests per day with low latency.
Core Data Model
Properties table: property_id, host_id, name, description, location (lat/lng), property_type, base_price, max_guests. Rooms table: room_id, property_id, room_type, capacity, amenities. Availability table: room_id, date, status (AVAILABLE/BOOKED/BLOCKED), price (allows dynamic pricing per date). Reservations table: reservation_id, room_id, guest_id, check_in_date, check_out_date, status (PENDING/CONFIRMED/CANCELLED), total_price, payment_id. The availability table is the source of truth for what is bookable. It is pre-populated by hosts for the next 12 months and updated atomically on booking and cancellation.
Preventing Double Booking
Double booking — two guests booking the same room for overlapping dates — is the central design challenge. Solution: use a database transaction with a SELECT FOR UPDATE lock. When a guest initiates booking: (1) BEGIN TRANSACTION. (2) SELECT * FROM availability WHERE room_id = X AND date BETWEEN check_in AND check_out AND status = AVAILABLE FOR UPDATE — this locks those rows. (3) If all dates are available, UPDATE availability SET status = BOOKED, INSERT INTO reservations. (4) COMMIT. The FOR UPDATE lock prevents any concurrent transaction from reading those rows until the first transaction commits or rolls back. If two users attempt to book the same room simultaneously, one gets the lock, books the room, and commits. The second transaction then reads BOOKED status and fails cleanly.
def book_room(room_id, check_in, check_out, guest_id):
with db.transaction():
# Lock availability rows for this room and date range
rows = db.query(
"""SELECT date, status FROM availability
WHERE room_id = %s AND date >= %s AND date = %s AND date < %s",
room_id, check_in, check_out
)
reservation_id = db.insert(
"INSERT INTO reservations (room_id, guest_id, check_in, check_out, status) VALUES (%s, %s, %s, %s, 'CONFIRMED')",
room_id, guest_id, check_in, check_out
)
return reservation_id
Search with Availability
Search must filter by location and available dates. Naive approach: join properties with availability and filter — too slow at scale. Optimized approach: separate the two concerns. Location search: use geohashing or PostGIS to find properties within the search radius (same technique as location-based service design). This returns a candidate set of property IDs — potentially thousands. Availability filter: for each candidate property, check the availability table for the requested dates. This second query is parallelized across properties and served from a read replica. Cache popular date-range availability in Redis (TTL = 60 seconds) to reduce database load during peak searches. The result is then sorted by price, rating, or relevance.
Dynamic Pricing
Airbnb and Booking.com offer dynamic pricing suggestions to hosts. The pricing algorithm considers: historical booking rates for similar properties in the area, upcoming events (concerts, conferences drive demand spikes), day of week and seasonality, competitive pricing (nearby properties at similar price points), and supply (remaining inventory for those dates). A demand forecasting ML model predicts occupancy probability at different price points, optimizing for revenue (price x occupancy probability). Hosts can accept Airbnb price suggestions or set their own. The per-date price is stored in the availability table, allowing different prices per night within a stay.
Reservation Lifecycle and Cancellation
Reservations go through states: PENDING (payment processing) -> CONFIRMED (payment succeeded) -> CHECKED_IN -> COMPLETED, with CANCELLED possible from CONFIRMED. Cancellation must atomically: (1) Update reservation status to CANCELLED. (2) Set availability rows back to AVAILABLE. (3) Trigger refund via payment service (async). All three steps must succeed or all must roll back. Use a saga pattern: if the payment refund fails, a retry queue ensures eventual completion. Hosts can also block dates (maintenance, personal use) without creating a reservation — this sets availability to BLOCKED directly. Cancellation policies (flexible/moderate/strict) determine refund amounts based on days until check-in.
Scale Estimation
Airbnb: 6 million listings, 100 million guests. Peak search: 10 million searches/day = ~120 RPS average, 600 RPS peak. Each search touches 50-200 properties for availability check. Availability table: 6M listings * 365 dates * 12 months ahead = ~26 billion rows — requires partitioning by (property_id, date). With a composite index on (room_id, date, status), individual room availability checks are fast O(log n) lookups. For search throughput, shard the availability table by property_id. The geospatial search uses a separate read replica with a PostGIS extension or a dedicated Elasticsearch cluster.
Interview Tips
- SELECT FOR UPDATE is the correct concurrency mechanism — mention it explicitly
- The availability table (one row per room per date) is the expected schema
- Separate location search from availability filtering for performance
- Cancellation requires a saga pattern for distributed consistency
- Dynamic pricing is a differentiating detail — shows product understanding
Frequently Asked Questions
How do you prevent double booking in a hotel reservation system?
Double booking prevention requires atomic database operations. The standard approach is SELECT FOR UPDATE within a transaction: begin a transaction, select the availability rows for the room and date range with a FOR UPDATE lock (this prevents any concurrent transaction from reading those rows until the lock is released), check that all dates are AVAILABLE, then update them to BOOKED and insert the reservation record in the same transaction. If two users attempt to book the same room simultaneously, the database serializes the transactions — the first gets the lock and books, the second sees BOOKED status and fails cleanly. For higher throughput, use Redis with the SETNX command to atomically acquire a distributed lock on the room-dates combination, with a TTL fallback to prevent lock starvation if the booking process crashes.
How does a hotel booking system handle search with availability filtering?
Searching for available rooms in a location and date range has two phases: (1) Location search: use geohashing or PostGIS to find all properties within the search radius. A geohash prefix query on an indexed column is O(log n) and returns a candidate set of hundreds to thousands of properties. (2) Availability filter: for each candidate property, query the availability table to check whether all nights in the requested date range are available. This per-property availability query uses a composite index on (room_id, date, status) for fast lookups. The two phases are kept separate to optimize independently — the location search uses a geospatial index, the availability check uses a B-tree index. Results are cached in Redis with a 60-second TTL to reduce database load during peak search traffic. Availability caches are invalidated immediately on booking or host-initiated blocking.
How does Airbnb implement dynamic pricing?
Airbnb dynamic pricing (called Smart Pricing) uses a demand forecasting model to suggest optimal nightly prices to hosts. The model inputs include: historical occupancy rates for similar listings in the same area and price bracket, demand signals from search volume and wishlist additions for that area and date range, supply signals (remaining available listings for those dates), day-of-week and seasonal patterns, and proximity to events (concerts, conferences, sporting events cause demand spikes). The model outputs a price recommendation that maximizes expected revenue — price times predicted occupancy probability. Hosts can accept the suggestion, set a minimum or maximum price, or override entirely. Airbnb stores per-date prices in the availability table, allowing different prices for weekdays vs weekends and event-adjacent dates within a single reservation.
{ “@context”: “https://schema.org”, “@type”: “FAQPage”, “mainEntity”: [ { “@type”: “Question”, “name”: “How do you prevent double booking in a hotel reservation system?”, “acceptedAnswer”: { “@type”: “Answer”, “text”: “Double booking prevention requires atomic database operations. The standard approach is SELECT FOR UPDATE within a transaction: begin a transaction, select the availability rows for the room and date range with a FOR UPDATE lock (this prevents any concurrent transaction from reading those rows until the lock is released), check that all dates are AVAILABLE, then update them to BOOKED and insert the reservation record in the same transaction. If two users attempt to book the same room simultaneously, the database serializes the transactions — the first gets the lock and books, the second sees BOOKED status and fails cleanly. For higher throughput, use Redis with the SETNX command to atomically acquire a distributed lock on the room-dates combination, with a TTL fallback to prevent lock starvation if the booking process crashes.” } }, { “@type”: “Question”, “name”: “How does a hotel booking system handle search with availability filtering?”, “acceptedAnswer”: { “@type”: “Answer”, “text”: “Searching for available rooms in a location and date range has two phases: (1) Location search: use geohashing or PostGIS to find all properties within the search radius. A geohash prefix query on an indexed column is O(log n) and returns a candidate set of hundreds to thousands of properties. (2) Availability filter: for each candidate property, query the availability table to check whether all nights in the requested date range are available. This per-property availability query uses a composite index on (room_id, date, status) for fast lookups. The two phases are kept separate to optimize independently — the location search uses a geospatial index, the availability check uses a B-tree index. Results are cached in Redis with a 60-second TTL to reduce database load during peak search traffic. Availability caches are invalidated immediately on booking or host-initiated blocking.” } }, { “@type”: “Question”, “name”: “How does Airbnb implement dynamic pricing?”, “acceptedAnswer”: { “@type”: “Answer”, “text”: “Airbnb dynamic pricing (called Smart Pricing) uses a demand forecasting model to suggest optimal nightly prices to hosts. The model inputs include: historical occupancy rates for similar listings in the same area and price bracket, demand signals from search volume and wishlist additions for that area and date range, supply signals (remaining available listings for those dates), day-of-week and seasonal patterns, and proximity to events (concerts, conferences, sporting events cause demand spikes). The model outputs a price recommendation that maximizes expected revenue — price times predicted occupancy probability. Hosts can accept the suggestion, set a minimum or maximum price, or override entirely. Airbnb stores per-date prices in the availability table, allowing different prices for weekdays vs weekends and event-adjacent dates within a single reservation.” } } ] }