Designing a Hotel Booking System

A hotel booking system manages room inventory, search, reservations, and payments for millions of hotels.

Requirements

• Search hotels by location, dates, guests
• Real-time room availability
• Reserve without double-booking
• Payment processing
• Cancellation policies

Data Model

hotels(id, name, location, rating, amenities)
rooms(id, hotel_id, type, price_per_night, capacity)
reservations(id, room_id, user_id, check_in, check_out, status, price)
inventory(room_id, date, available_count)

Availability Search

SELECT r.* FROM rooms r
JOIN hotels h ON r.hotel_id = h.id
WHERE h.city = 'Paris'
  AND NOT EXISTS (
    SELECT 1 FROM reservations res
    WHERE res.room_id = r.id
    AND res.status = 'confirmed'
    AND res.check_in < '2026-06-15'
    AND res.check_out > '2026-06-10'
  )

Preventing Double-Booking

Optimistic locking:

INSERT INTO reservations (room_id, check_in, check_out)
SELECT room_id, check_in, check_out
WHERE NOT EXISTS (conflicting reservation)

Add unique constraint on (room_id, date range). DB enforces exclusivity.

Booking Flow

1. Search → Show available rooms
2. User selects room → Soft reserve (10 min hold)
3. User completes payment
4. Confirm reservation + release hold
5. Payment fails → Release hold

Dynamic Pricing

• Increase price as availability decreases
• Seasonal pricing rules
• Last-minute discounts
• Demand forecasting ML model

Caching

• Cache hotel details (rarely changes)
• Cache search results (short TTL, 5 min)
• Don't cache availability (real-time required)

Conclusion

Double-booking prevention via DB constraints + short-lived holds during checkout is the industry-standard pattern for reservation systems.