Core Entities
Hotel: hotel_id, name, address, city, star_rating, amenities (JSON), check_in_time, check_out_time. RoomType: room_type_id, hotel_id, name (STANDARD, DELUXE, SUITE), max_occupancy, base_price_per_night, amenities (JSON), total_rooms (total physical rooms of this type). Room: room_id, hotel_id, room_type_id, floor, room_number, status (AVAILABLE, UNDER_MAINTENANCE). Reservation: reservation_id, guest_id, hotel_id, room_type_id, room_id (assigned at check-in, null at booking), check_in_date, check_out_date, num_guests, status (PENDING, CONFIRMED, CHECKED_IN, CHECKED_OUT, CANCELLED), total_price, booked_at. PriceRule: rule_id, hotel_id, room_type_id, start_date, end_date, price_per_night (overrides base price for peak seasons).
Availability Check
The core query: how many rooms of type T are available at hotel H for dates [check_in, check_out)? Available = total_rooms – reserved_count. Reserved count: SELECT COUNT(*) FROM reservations WHERE hotel_id = H AND room_type_id = T AND status IN (‘CONFIRMED’, ‘CHECKED_IN’, ‘PENDING’) AND check_in_date :checkin. The date overlap condition: two reservations overlap if and only if one starts before the other ends AND ends after the other starts. Index on (hotel_id, room_type_id, check_in_date, check_out_date, status) for this query. Available rooms = total_rooms – reserved_count. If available > 0, booking is possible.
Reservation Flow with Concurrency
Race condition: two users book the last room simultaneously. Both check availability (count = 1), both proceed to insert. Both reservations are created — overbooking. Solution: use a database constraint + conditional insert. Option 1 — Optimistic locking: include a version check. Track a room_type_availability table with (hotel_id, room_type_id, date, available_count). On booking: UPDATE room_type_availability SET available_count = available_count – 1 WHERE hotel_id=H AND room_type_id=T AND date BETWEEN check_in AND check_out AND available_count > 0. If rows_affected total_rooms: rollback and return an error. Wrap in a database transaction with SERIALIZABLE isolation level.
Dynamic Pricing
Room prices vary by season, day of week, and demand. Price for a reservation = sum of nightly prices over all nights in [check_in, check_out). For each night: find the applicable PriceRule (hotel_id, room_type_id, date range). If a rule covers this date, use its price. Else use the base price. PriceRule table has (start_date, end_date) ranges. To find the applicable rule for a specific date: SELECT price_per_night FROM price_rules WHERE hotel_id=H AND room_type_id=T AND start_date = :date ORDER BY start_date DESC LIMIT 1 (most specific rule wins). At booking time: compute the total price for all nights, store it on the reservation. Price is locked at booking time (changes after booking don’t affect existing reservations).
Room Assignment at Check-In
At booking time, a room type is reserved (not a specific room). At check-in time, a specific room is assigned. This maximizes flexibility: the hotel can assign rooms based on current occupancy patterns, maintenance schedules, and guest preferences (floor, view, accessibility). Assignment logic: SELECT room_id FROM rooms WHERE hotel_id=H AND room_type_id=T AND status=AVAILABLE AND room_id NOT IN (SELECT room_id FROM reservations WHERE hotel_id=H AND check_in_date=:today AND status=CHECKED_IN) LIMIT 1 FOR UPDATE. UPDATE reservations SET room_id=:assigned_room, status=CHECKED_IN WHERE reservation_id=:id. The FOR UPDATE lock prevents two front-desk agents from assigning the same room simultaneously.
Cancellation and Refund Policy
CancellationPolicy (per hotel, per room type): free cancellation until N days before check-in. 50% refund if cancelled within N days. No refund if cancelled within 24 hours or after check-in. On cancellation: compute the refund amount based on the policy and the number of days until check-in. Create a Refund record. Mark the reservation as CANCELLED. Restore availability: decrement the reserved count (release the slot back to inventory). For the refund: trigger the payment gateway refund (see lld-payment-gateway). Important: the availability restoration and the status update must happen atomically (single transaction). Otherwise a crash between the two steps leaves the inventory inconsistent.
Asked at: Airbnb Interview Guide
Asked at: Uber Interview Guide
Asked at: Lyft Interview Guide
Asked at: Shopify Interview Guide
See also: Scale AI Interview Guide 2026: Data Infrastructure, RLHF Pipelines, and ML Engineering