Design a Hotel Booking Platform Down to the SQL Tables and Booking Transaction

Design an online hotel booking platform. Travelers search for hotels in a destination for a check-in date, a check-out date and a number of guests, compare the available room types and prices, and book. Hotel partners manage their properties, room types, room counts and nightly prices. Travelers can also cancel bookings. After the high-level design, the interviewer will go deep into implementation: expect to write out the actual relational tables with their key fields, keys and constraints, and to walk through the exact transaction that reserves a room without ever selling the same room twice. ### Constraints and Clarifications - Assume the standard scope: partner inventory and price management, search by destination, dates and guests, a hotel details page with live availability, booking, and cancellation. Reviews, loyalty programs, flights and the internals of payment processing are out of scope; payment is an external provider you call. - A confirmed booking must never exceed the rooms available on any night of the stay. - Search traffic is far higher than booking traffic. ### Clarifying Questions - Do guests book a room type (any standard double) or a specific physical room? - Is any overbooking allowed, as some hotels do deliberately, or is the limit strict? - Is payment taken at booking time, or does the guest pay at the hotel with a card held as a guarantee? - Can prices differ per night, and are there rate plans such as refundable and non-refundable? - What scale should we assume for hotels, searches per second and bookings per day? ### Part 1 — Requirements, APIs and architecture State the functional and non-functional requirements, rough load estimates, the main APIs for travelers and partners, and the high-level components, including how search stays fast while bookings stay correct. ```hint Two paths with different needs Separate the path that answers "what is available here?" for many browsing users from the path that commits a booking, and decide which one must be exactly right. ``` #### What This Part Should Cover - Functional scope and the strict no-double-booking requirement - Load estimates showing the read-heavy search path - Traveler and partner APIs - Components for search, inventory and booking, and how data flows between them ### Part 2 — The SQL schema Write the tables you would create: hotels, room types, per-night inventory and prices, and reservations. For each, give the key fields, the primary key, foreign keys, unique and check constraints, and indexes, and explain how availability for a date range is represented and queried. ```hint Pick the unit of inventory Decide what one row of inventory represents, so that "is this stay available?" becomes a check over a small, well-defined set of rows. ``` #### What This Part Should Cover - Tables with their key fields, types and keys - A representation of availability that makes the no-double-booking rule enforceable - Constraints and indexes justified by specific queries - A query that returns the available room types and prices for a stay ### Part 3 — Reserving without double booking Walk through the booking flow from "Book" to a confirmed reservation: the transaction and its SQL, how concurrent requests for the last room are resolved, temporary holds during payment and their expiry, retries, cancellation, and what happens when payment or the database fails mid-flow. ```hint Make the check and the decrement inseparable Look for a way to confirm availability and consume it for every night of the stay in one step that two transactions cannot interleave. ``` #### What This Part Should Cover - The booking transaction and how it serializes access to the last room - Holds with expiry, and the race between expiry and payment - Idempotent retries and cancellation - Failure handling across the database and the payment provider ### What a Strong Answer Covers - A concrete schema that a reviewer could create as written, with constraints doing real work - Correctness under concurrency shown with actual SQL, not hand-waving about locks - A search path that scales separately and tolerates slightly stale availability - Partitioning that keeps each booking's writes in one transaction - Failure handling, reconciliation, and the metrics that would reveal inventory drift ### Follow-up Questions - How would you support a hotel that deliberately overbooks by a small number of rooms? - How would you handle a partner who sells the same rooms on several booking platforms at once? - A traveler's payment succeeds just after their hold expires and the last room has been sold to someone else. What happens? - How do time zones affect stay dates, cancellation deadlines and hold expiry?