Hotel Reservation System Design Template

System Design: [Hotel reservation system name]

Reference design for a hotel group's central reservation system: inventory counted per room type and per night, multi-night stays booked atomically, rate plans with derived prices and stay restrictions (minimum length of stay, closed to arrival, closed to departure), an overbooking policy computed from show-up rates and walk costs, group blocks, and distribution of availability and rates to channels. It differs from seat booking in that nothing is sold by identity: a stay consumes one unit of a room type on each night, and the room number is assigned later. It follows the structure of the System Design Document template, adds a 45-minute interview path, access-pattern and failure-mode tables and a cited "How real systems do it" box, and is pre-filled with a worked design to adapt. Replace every [bracketed] value and every row marked Example; values marked Example are assumptions, not benchmarks.

Document control

Approvals

Revision history

Contents

1. Overview
2. Requirements
3. Capacity estimation
4. API design
5. Data model
6. High-level design
7. Detailed design
8. Scalability and performance
9. Reliability, security and operations
10. Trade-offs and alternatives
11. Delivery plan and risks
12. Appendices

1. Overview

1.1 Problem statement

[Example: Our 1 200 hotels each run their own property management system, and the website, the call centre and two travel agencies each keep a copy of availability that is refreshed every 15 minutes. On busy weekends we sell rooms that no longer exist, front desks walk guests without a policy, and a three-night booking sometimes holds Friday and Saturday but loses Sunday. Rate changes take hours to reach all channels, so the same room shows three prices.]

1.2 Proposed solution

[Example: A central reservation system owns future inventory as counters per property, room type and night. A booking reserves one unit on each night of the stay in a single transaction on the property's shard, or fails as a whole. Rate plans are defined once (a base rate plus derived plans and restrictions) and priced per night. Availability and prices for search come from a precomputed calendar that is updated by events within seconds; the booking path always re-checks the counters. Overbooking limits per room type and night come from revenue management and are enforced as part of the counter check. Channels receive availability, rates and restrictions by push on every change, and reservations from channels enter through the same booking path. The PMS receives reservations and sends back in-house changes and out-of-order rooms.]

1.3 Decisions requested from reviewers

1.4 45-minute interview path

Use this order when the document serves an interview or a short design review. The written sections below are the long form of each step.

Start the default deep dive with the naive answer: "for each night, read availability, then insert the booking". Show two guests booking the last Saturday room, one for Friday and Saturday and one for Saturday and Sunday, both reading 1 available. Then compare a lock per night, a conditional update per night in one transaction, and a single property lock, and explain why rows are locked in date order.

2. Requirements

2.1 Core functional requirements

2.2 Non-functional requirements and invariants

2.3 Below the line (out of scope)

Name these explicitly so reviewers and interviewers do not pull the design toward them.

• [Example: revenue management forecasting and price optimisation (this system consumes their output)]
• [Example: room assignment, housekeeping and check-in (PMS)]
• [Example: loyalty points accrual (separate service fed by stay events)]
• [Example: seat-based events and onsales (see Ticket and Hotel Booking System Design (system-design/design-ticket-booking))]
• [Item deferred, with the release that will take it]

3. Capacity estimation

3.1 Assumptions

3.2 Bookings and searches

Rooms in the portfolio: $P \times R = 1\,200 \times 150 = 180\,000$. Occupied room nights per day divided by length of stay gives arrivals, which equal net bookings in a steady state:

Booking writes are about $76\,364 / 86\,400 \approx 0.9$ per second on average and about 9 per second during a promotion at 10 times the average. Each booking touches $\ell$ night rows, about 168 000 night-row updates a day.

3.3 Inventory and rate data

3.4 Summary

4. API design

4.1 Endpoints

The Idempotency-Key header follows the pattern of draft-ietf-httpapi-idempotency-key-header-08 (22 October 2026, an Internet-Draft, not an RFC). Errors use problem details (RFC 9457).

4.2 Request and response examples

Hold request:

{"property_id": "PRP-0417", "room_type": "KING-DLX", "rate_plan": "BAR", "checkin": "2027-03-11", "checkout": "2027-03-14", "adults": 2}

Hold response with the per-night breakdown:

{"hold_id": "HLD-88213", "expires_at": "2027-02-02T10:15:00Z", "currency": "USD", "nights": [{"date": "2027-03-11", "price": 180}, {"date": "2027-03-12", "price": 220}, {"date": "2027-03-13", "price": 240}], "total": 640, "cancellation_policy": "Free until 48 hours before arrival, property local time"}

A hold that fails on any night returns 409 with the failing night and the reason (sold_out, closed_to_arrival, min_los_not_met), so the client can suggest other dates.

5. Data model

5.1 Entities

Inventory and rates are keyed by property-local stay dates (a night is a date, not a timestamp). A reservation has one row per night it consumes.

5.2 Storage choices

5.3 Access patterns

6. High-level design

7. Detailed design

The deep dives in this section, each with options, choice and reason:

7.1 Inventory model

Sell room types, not rooms. A room number is chosen at or near check-in by the PMS, which lets the hotel absorb early departures, late check-outs and maintenance without touching reservations. For each property, room type and night:

A booking is allowed on a night only if available is at least 1 and the night is not stop-sold. NFR-01 is this inequality checked for every row.

7.2 Multi-night atomic hold

A stay from 11 to 14 March consumes the nights of 11, 12 and 13 March. The hold runs one transaction on the property's shard:

BEGIN;
-- lock the nights in date order first, so overlapping stays queue in the same order
SELECT stay_date FROM inventory_night
 WHERE property_id = 'PRP-0417' AND room_type = 'KING-DLX'
   AND stay_date >= DATE '2027-03-11' AND stay_date < DATE '2027-03-14'
 ORDER BY stay_date
 FOR UPDATE;
UPDATE inventory_night
   SET held = held + 1, version = version + 1
 WHERE property_id = 'PRP-0417' AND room_type = 'KING-DLX'
   AND stay_date >= DATE '2027-03-11' AND stay_date < DATE '2027-03-14'
   AND stop_sell = FALSE
   AND physical - out_of_order + overbook_limit - sold - held >= 1;
-- the service checks that the row count equals the number of nights (3);
-- if not, ROLLBACK and report the first failing night
INSERT INTO hold (hold_id, property_id, room_type, checkin, checkout, expires_at)
VALUES ('HLD-88213', 'PRP-0417', 'KING-DLX', DATE '2027-03-11', DATE '2027-03-14', now() + INTERVAL '15 minutes');
COMMIT;

The explicit SELECT with ORDER BY stay_date FOR UPDATE locks the nights in date order (an UPDATE over a range does not promise any lock order), so two overlapping stays always lock shared nights in the same order and cannot deadlock; the locks are held only for the few milliseconds of the transaction, never across payment. If the updated row count is less than the number of nights, at least one night is full or closed, and the transaction rolls back.

Confirming a hold moves the count from held to sold on each night and writes reservation nights with their prices. A sweeper releases expired holds by the reverse update. A modification holds the new nights and releases the old nights in the same transaction, so a guest who moves a stay never ends up with neither booking.

7.3 Rate plans and restrictions

Revenue management sets a base rate (best available rate, BAR) per room type and night. Other plans derive from it:

The stay price is the sum of the nightly prices, each from that night's rate row, so a stay over a price change mixes prices correctly. Restrictions are evaluated against the arrival and departure dates:

Restrictions make availability depend on the stay, not only on the nights: a Saturday with MinLOS 2 is available for Friday to Sunday but not for Saturday alone. The calendar therefore stores restrictions next to prices, and the search evaluates them per requested stay.

7.4 Search calendar

The calendar builder consumes change events per property and keeps, for each (property, room type, rate plan), an array of 730 entries: available count, price, MinLOS, MaxLOS, CTA, CTD. A search for one property reads at most 8 x 12 = 96 keys and evaluates the stay in memory. Lag is the event pipeline lag (NFR-05). The cache can be wrong for a few seconds; the hold in 7.2 is always the final check, and a failed hold returns the failing night so the page can refresh.

7.5 Overbooking policy

Some guests do not arrive and some cancel too late to resell. Selling a few rooms above capacity raises expected revenue but risks walking a guest (relocating them to another hotel at the group's cost). With capacity $C$, booking limit $B$ and independent show probability $q$, arrivals follow $X \sim \text{Binomial}(B, q)$. With $c_u$ the margin of a room night lost when a room stays empty and $c_o$ the cost of walking one guest, the expected contribution is:

Example: $C = 100$, $q = 0.92$, $c_u$ = 150 (room rate 180 minus 30 variable cost), $c_o$ = 450 (relocation, transport, compensation and goodwill), all in USD:

The expected-contribution optimum is $B = 107$ (an overbooking limit of 7), but it walks at least one guest on about 24% of such nights. With a policy cap of 10% on the probability of any walk, the limit is $B = 105$ (limit 5), which gives up 58.7 of expected contribution per night (14 483.9 minus 14 425.2) to cut the walk probability from 0.239 to 0.071. Revenue management approves the cap; the system stores overbook_limit per room type and night and enforces it in 7.1. The binomial model assumes guests decide independently; groups and event nights break that assumption, so set lower limits there.

When arrivals exceed rooms, the walk workflow chooses which reservation to relocate (never loyalty top tiers or guests already walked on this trip, as Example rules), books a comparable nearby hotel, pays transport and compensation, and records the walk for the next forecast. Consumer rules on relocation and disclosure differ by country; check the local rules and a qualified professional. This is not legal advice.

7.6 Channels, group blocks and the PMS

• Shared pool: channels sell from the same counters (last-room availability), so a room is never stranded in one channel's allotment. The channel push sends availability, rates and restrictions on every change, batched per property for 1 s. A channel that sells on a stale count sends a reservation that fails the hold; the gateway answers with a rejection the channel understands, and the lag is tracked per channel (9.3).
• Group blocks: a block reserves rooms for a group as its own counter per night, taken out of general inventory. Guests book from the block until the cutoff date; at cutoff a job returns unused block rooms to general inventory in one transaction per property.
• PMS: the central system owns future reservations; the PMS owns in-house stays and room assignment. Out-of-order rooms from the PMS raise out_of_order counts and can create a negative availability that triggers the walk workflow before arrival day rather than at the desk.

7.7 How real systems do it

How real systems do it (paraphrased, not copied). Rothstein's paper "Hotel overbooking as a Markovian sequential decision process" (Decision Sciences, 1974) describes a general hotel reservation system and models booking acceptance as a sequence of decisions; applied to sample data from a real hotel, cancellations were frequent enough that the model prescribed a substantial amount of overbooking. Talluri and van Ryzin's book "The Theory and Practice of Revenue Management" (Springer, 2004) treats overbooking and network revenue management, where a product such as a multi-night stay uses capacity on several resources at once, which is why 7.2 books every night of a stay together. Cross, Higbie and Cross, "Revenue Management's Renaissance" (Cornell Hospitality Quarterly, 2009), argue that hotel revenue management should move beyond room inventory control to total revenue, price elasticity and measures that compare performance with competitors. Read the originals in 12.3; this box keeps only the general ideas.

8. Scalability and performance

Latency budget for a hold (target p99 500 ms):

9. Reliability, security and operations

9.1 Failure modes

9.2 Security and abuse

9.3 Observability

9.4 Cost estimate

10. Trade-offs and alternatives

11. Delivery plan and risks

11.1 Milestones

11.2 Migration and rollback

[Example: Migrate property by property. For each property, freeze availability changes for 10 minutes, load future reservations from the old system and the PMS, reconcile counts night by night, then switch channels to the new push. Rollback restores the old system as the source and replays reservations made in the new one.]

11.3 Risks

12. Appendices

12.1 Open questions

12.2 Glossary

12.3 References

Standard and licence note

Based on common industry practice for hotel central reservation systems and on the revenue management literature cited in 12.3, paraphrased only. No standard governs this design. The overbooking table is computed from the stated example inputs with an exact binomial model; all prices, volumes and limits are illustrative examples, not benchmarks. Notes on relocation rules are not legal advice; check the current official text and a qualified professional. Original text written for this collection. Template licence: MIT.