Requirements
- Search available rooms for a date range (check-in, check-out, guests, room type)
- Book a room: prevent double-booking the same room for overlapping dates
- Manage room inventory: hotel admins can add/remove rooms and configure pricing
- Handle cancellations with configurable cancellation policies
- 10K hotels, 1M rooms total, 100K bookings/day
Data Model
Hotel(hotel_id, name, address, city, country, lat, lng, rating, amenities[])
RoomType(room_type_id, hotel_id, name, capacity, bed_type, amenities[], base_price)
Room(room_id, hotel_id, room_type_id, room_number, floor, status ENUM(ACTIVE,MAINTENANCE))
Reservation(reservation_id, user_id, hotel_id, room_id, check_in, check_out,
status ENUM(PENDING,CONFIRMED,CANCELLED), total_amount, created_at)
RoomPrice(room_type_id, date, price) -- per-night pricing, overrides base_price
Availability Search
Query: find available rooms at Hotel H for check-in C1 and check-out C2.
SELECT r.room_id, rt.name, rp.price
FROM Room r
JOIN RoomType rt ON r.room_type_id = rt.room_type_id
LEFT JOIN RoomPrice rp ON rp.room_type_id = rt.room_type_id AND rp.date = :check_in
WHERE r.hotel_id = :hotel_id
AND r.status = 'ACTIVE'
AND r.room_id NOT IN (
SELECT room_id FROM Reservation
WHERE status IN ('PENDING', 'CONFIRMED')
AND check_in :check_in -- overlapping date ranges
)
Date overlap condition: two ranges [A,B) and [C,D) overlap iff A < D AND B > C.
Booking Flow (Preventing Double-Booking)
- User selects room and dates
- POST /reservations/hold: create Reservation with status=PENDING, expires_at=NOW()+15min
- User confirms and pays
- POST /reservations/{id}/confirm: BEGIN TRANSACTION; check no other CONFIRMED reservation overlaps (SELECT COUNT(*) … FOR UPDATE); if clean, charge payment; UPDATE status=CONFIRMED; COMMIT
The SELECT FOR UPDATE at step 4 acquires a row-level lock on the room’s reservations, preventing concurrent bookings. The hold (PENDING) at step 2 uses an INSERT with a UNIQUE constraint check to prevent creating two PENDINGs for the same room+dates simultaneously.
Availability Cache
Availability queries are read-heavy (users search many times before booking). Cache available room counts per hotel per date range in Redis: key=avail:{hotel_id}:{date}, value=hash of room_type_id → available_count. TTL=60s. Invalidate on booking and cancellation. Slight staleness (60s) is acceptable — the actual booking still validates in the DB. This reduces load on the reservation query by 90%+.
Dynamic Pricing
Hotel admins set per-night prices via the RoomPrice table. Price depends on: day of week, season, local events, current occupancy (yield management). Occupancy-based pricing: if occupancy > 80% for a date, apply a 1.2x multiplier. Computed nightly by a batch job. The active price for a room type on a date is: COALESCE(RoomPrice.price, RoomType.base_price). Cache the price in Redis with the availability data.
Cancellation Policy
Policy types: FREE_CANCELLATION (cancel anytime for full refund), PARTIAL_REFUND (50% refund if cancelled >48h before check-in), NON_REFUNDABLE. On cancellation: check policy and calculate refund amount; initiate refund via payment processor; UPDATE Reservation SET status=CANCELLED; add room back to available inventory (cache invalidation + DB update). Cancellation within 24h of check-in: apply the strictest policy.
Overbooking Handling
Airlines overbook intentionally; hotels may do so unintentionally. When a hotel is overbooked (more confirmed reservations than rooms): walk the guest (book them at an equivalent or better hotel, pay the difference). Track walked_reservations for SLA monitoring. Prevention: use SELECT FOR UPDATE strictly and cap pending reservations to available_rooms + 5% buffer.
Key Design Decisions
- Overlap condition: A < D AND B > C — memorize this for availability queries
- Two-phase booking (PENDING → CONFIRMED) with expiry gives users time to complete payment
- SELECT FOR UPDATE at confirm time prevents the race condition between two users booking the same room
- Per-night RoomPrice table enables dynamic pricing without schema changes
{“@context”:”https://schema.org”,”@type”:”FAQPage”,”mainEntity”:[{“@type”:”Question”,”name”:”How do you check date range availability for a hotel room?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”The core query: find rooms where no confirmed or pending reservation overlaps the requested [check_in, check_out). Two date ranges [A,B) and [C,D) overlap iff A < D AND B > C. SQL: SELECT room_id FROM rooms WHERE hotel_id=X AND status='ACTIVE' AND room_id NOT IN (SELECT room_id FROM reservations WHERE status IN ('PENDING','CONFIRMED') AND check_in < :check_out AND check_out > :check_in). Index (hotel_id, check_in, check_out) on the reservations table. For high-concurrency queries: cache available room counts per hotel per date in Redis (TTL=60s) — serve from cache for browsing, validate in DB at booking time. The slight staleness (60s) is acceptable for search; atomic validation at booking prevents double-booking.”}},{“@type”:”Question”,”name”:”How do you prevent double-booking in a hotel reservation system?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Two-phase booking: (1) HOLD: when a user selects a room, create a Reservation record with status=PENDING and expires_at=NOW()+15min. (2) CONFIRM: when user pays, BEGIN TRANSACTION; SELECT COUNT(*) FROM reservations WHERE room_id=X AND status IN ('PENDING','CONFIRMED') AND check_in < :check_out AND check_out > :check_in FOR UPDATE; if count > 1 (includes our own PENDING + another), throw SeatNoLongerAvailable; charge payment; UPDATE status=CONFIRMED; COMMIT. The FOR UPDATE locks the relevant reservation rows, serializing concurrent confirmation attempts for the same room. A background job expires PENDING reservations after 15 minutes and frees the hold.”}},{“@type”:”Question”,”name”:”How does dynamic (yield management) pricing work for hotels?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Base price is set per room type. Dynamic multipliers are applied based on occupancy and demand: if current_bookings / total_rooms > 80% for a date → apply 1.2x multiplier; if < 30% → apply 0.9x (discount). External signals: local events (concerts, conferences) increase demand → higher multiplier. Day of week: weekends may have different base rates. Implementation: a nightly batch job computes dynamic prices and writes to the RoomPrice table (room_type_id, date, price). The availability query fetches the price from RoomPrice if it exists, falling back to RoomType.base_price. Cache the price per room type per date in Redis (TTL=1h). Dynamic pricing is displayed to users at search time and locked in at CONFIRM time.”}},{“@type”:”Question”,”name”:”How do you handle cancellation policies in a reservation system?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Store CancellationPolicy on each Reservation: FREE_CANCELLATION (full refund any time), PARTIAL_REFUND (refund depends on notice given: >48h=50%, <48h=0%), NON_REFUNDABLE. On cancellation request: look up the policy and the check_in date. Calculate refund_amount based on policy and time_until_check_in. Initiate refund via payment processor with the calculated amount. UPDATE Reservation SET status=CANCELLED, refund_amount=X. Increment available inventory (cache invalidation + no DB update needed since availability is derived from non-cancelled reservations). Log to CancellationAudit. For same-day cancellations: always apply the strictest policy. For corporate accounts: may have negotiated different cancellation terms stored per corporate_account.”}},{“@type”:”Question”,”name”:”How do you efficiently find available hotels for a city search?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”User searches: city + check_in + check_out + guests + room_type. Two-step approach: (1) Geo query: find hotels in the city using PostGIS (SELECT hotel_id FROM hotels WHERE ST_Distance(location, :city_center) < :radius ORDER BY rating DESC LIMIT 50). Or use Elasticsearch with geo_distance filter for more advanced filtering (amenities, price range). (2) Availability filter: for the top 50 hotels, check room availability. Use the cached Redis availability counts (avail:{hotel_id}:{date_range}) for fast filtering — only hotels with available_count > 0 pass. Fetch full details for passing hotels from DB. This two-step (geo + availability) avoids a single expensive join across all hotels and all reservations.”}}]}
Airbnb system design is the canonical hotel reservation interview topic. See common questions for Airbnb interview: hotel reservation and availability system design.
Amazon system design covers booking and reservation systems. Review design patterns for Amazon interview: reservation and booking system design.
Stripe system design covers payment flows for reservation systems. See design patterns for Stripe interview: payment and reservation system design.