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).
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
Availability Search
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.
{
“@context”: “https://schema.org”,
“@type”: “FAQPage”,
“mainEntity”: [
{
“@type”: “Question”,
“name”: “How do you model room inventory to support per-night availability and pricing?”,
“acceptedAnswer”: { “@type”: “Answer”, “text”: “The standard model uses a room_inventory table with one row per (room_type_id, date). This enables per-night availability counts and per-night price overrides. Schema: room_inventory(id, room_type_id, date, available_count, price_override). For a hotel with 50 room types and availability tracked 365 days out: 50 * 365 = 18,250 rows per hotel. With 500K hotels: 9 billion rows — partition by hotel_id and date range. Alternative models: store only booked nights (invert: query absence), or store availability as a bitmap (one bit per day for 365 days = 46 bytes). The explicit row-per-date model wins for its query simplicity. Availability search is a MIN aggregation over a date range; booking decrements specific rows. The room_types table stores total_count (physical rooms of that type), which seeds the initial available_count. On cancellation: increment available_count back. The price_override column allows revenue management systems to update nightly prices without touching base rates.” }
},
{
“@type”: “Question”,
“name”: “How do you prevent double-booking under high concurrent load?”,
“acceptedAnswer”: { “@type”: “Answer”, “text”: “Two users booking the same room on the same dates simultaneously is the classic double-booking race condition. Both read available_count = 1, both check it is positive, both decrement: result is available_count = -1, i.e., two bookings for one room. Prevention: SELECT FOR UPDATE inside a database transaction. When transaction A executes "SELECT available_count FROM room_inventory WHERE room_type_id = X AND date IN (…) FOR UPDATE", it acquires exclusive row-level locks on those rows. Transaction B attempting the same SELECT FOR UPDATE must wait until A commits or rolls back. A completes successfully and decrements available_count to 0. B then reads 0, detects insufficient inventory, and rolls back with an appropriate error to the user. This serializes concurrent bookings for the same room. Performance: FOR UPDATE locks at row granularity (not table), so concurrent bookings for different room types or different date ranges proceed without blocking each other. Latency impact: a single booking transaction holds locks for ~100ms (DB write + payment initiation) — acceptable given that double-booking is a high-cost failure.” }
},
{
“@type”: “Question”,
“name”: “How does a reservation hold prevent abandoned checkouts from blocking inventory?”,
“acceptedAnswer”: { “@type”: “Answer”, “text”: “When a user clicks "Book", you do not want to charge them immediately — they need to review the booking details and enter payment information. But if you hold inventory for an unlimited time, an abandoned checkout locks a room indefinitely. Solution: two-phase booking with a timed hold. Phase 1: create a booking record with status = pending and hold_expires_at = NOW() + 10 minutes. Decrement room_inventory.available_count. The room is now "spoken for" but not confirmed. Phase 2: the user completes payment within the hold period. On payment success: update status = confirmed. On payment failure: update status = payment_failed and increment available_count back. On timeout (hold_expires_at has passed): a background cleanup job runs every minute, finds expired pending bookings, sets status = expired, and increments available_count. During the hold, other users see 0 availability for this room — correct, since it may be confirmed shortly. The 10-minute window is a product decision balancing user experience against inventory hold time. Airbnb uses 24 hours for rare listings; Booking.com uses ~15 minutes.” }
}
]
}