Q: How do you handle cancellation policies in a reservation system?

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.

Q: How do you efficiently find available hotels for a city search?

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.

Question 1

How do you check date range availability for a hotel room?

Accepted Answer

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.

Question 2

How do you prevent double-booking in a hotel reservation system?

Accepted Answer

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.

Question 3

How does dynamic (yield management) pricing work for hotels?

Accepted Answer

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.

Question 4

How do you handle cancellation policies in a reservation system?

Accepted Answer

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.

Question 5

How do you efficiently find available hotels for a city search?

Accepted Answer

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.

Hotel Reservation System Low-Level Design

Requirements

Data Model

Availability Search

Booking Flow (Preventing Double-Booking)

Availability Cache

Dynamic Pricing

Cancellation Policy

Overbooking Handling

Key Design Decisions