Hotel Reservation System Design — Student Notes
Student handout for the hotel reservation system design workshop — framework, requirements, APIs, schema, concurrency, and scaling. Based on Alex Xu & Sahn Lam.
• 8 min read • By Jatinder (Jay) Bhola
Hotel Reservation System Design — Student Notes
System Design Interview Workshop
Based on: Chapter 7, System Design Interview — An Insider's Guide: Volume 2 by Alex Xu & Sahn Lam
1. The 4-Step Framework
Use this framework for ANY system design interview:
| Step | What to Do | Time (45 min interview) |
|---|---|---|
| 1. Understand & Scope | Ask clarifying questions. Define functional & non-functional requirements. | 3–10 min |
| 2. High-Level Design | Draw architecture, define APIs, choose data stores. Get interviewer buy-in. | 10–15 min |
| 3. Deep Dive | Go deep on the hardest problem. Show technical depth. | 10–25 min |
| 4. Wrap Up | Summarize trade-offs. Mention improvements you'd make with more time. | 3–5 min |
Pro tip: Step 1 is where most candidates fail. Never jump into designing without asking questions first.
2. Requirements
Functional
- View hotel and room details
- Make, view, and cancel reservations
- Hotel admin: add/update/remove rooms and pricing
- Support overbooking (~10%)
Non-Functional
- Scale: 5,000 hotels, 1,000,000 rooms
- Concurrency: Handle simultaneous bookings for the same room type
- Latency: A few seconds acceptable for booking
- Availability: System must be highly available (24/7 bookings)
3. Capacity Estimation
Hotels: 5,000
Rooms: 1,000,000 (avg 200/hotel)
Occupancy rate: 70%
Average stay: 3 days
Daily reservations: 1,000,000 × 0.70 / 3 ≈ 240,000/day
Reservation TPS: 240,000 / 86,400 ≈ 3 TPS
Traffic funnel:
Hotel page views: ~300 QPS
Booking page views: ~30 QPS
Completed bookings: ~3 TPS
Key takeaway: Reservation TPS is LOW. The challenge is concurrency correctness, not raw throughput.
4. API Design
Hotel & Room Management (Admin)
GET /v1/hotels/{id} — Get hotel details
POST /v1/hotels — Create hotel
PUT /v1/hotels/{id} — Update hotel
DELETE /v1/hotels/{id} — Remove hotel
GET /v1/hotels/{id}/rooms/{id} — Get room details
POST /v1/hotels/{id}/rooms — Add room
PUT /v1/hotels/{id}/rooms/{id} — Update room
DELETE /v1/hotels/{id}/rooms/{id} — Remove room
Reservations (User-facing)
GET /v1/reservations — List my reservations
GET /v1/reservations/{id} — Reservation detail
POST /v1/reservations — Make a reservation ★
DELETE /v1/reservations/{id} — Cancel reservation
Reservation Request Body
{
"hotelID": "H123",
"roomTypeID": "RT456",
"startDate": "2024-07-01",
"endDate": "2024-07-03",
"roomCount": 1,
"reservationID": "UUID-abc-123"
}
Idempotency Key: The reservationID is generated by the client BEFORE submission. If a retry occurs (network failure, double-click), the server detects the duplicate ID and returns the existing reservation instead of creating a new one.
5. Architecture
┌──────────┐
│ CDN │
│ (images, │
│ static) │
└────┬─────┘
│
┌──────────┐ ┌─────┴──────┐
│ Users │─────────────→│API Gateway │
│(Web/App) │ │(auth, rate │
└──────────┘ │ limiting) │
└─────┬──────┘
│
┌─────────────┼─────────────┐
│ │ │
┌─────┴─────┐ ┌────┴────┐ ┌─────┴──────┐
│ Hotel │ │ Rate │ │Reservation │
│ Service │ │ Service │ │ Service │
│(metadata) │ │(pricing)│ │(booking + │
└─────┬─────┘ └────┬────┘ │ inventory) │
│ │ └─────┬──────┘
┌─────┴─────┐ ┌────┴────┐ ┌─────┴──────┐
│ Hotel DB │ │ Rate DB │ │Reservation │
│ (+ cache) │ └─────────┘ │ DB │
└───────────┘ └────────────┘
│
┌─────┴──────┐
│ Payment │
│ Service │
└────────────┘
Why Reservation + Inventory are in the same service: Allows atomic database transactions for booking. Splitting them would require distributed transactions (2PC or Saga), adding significant complexity.
6. Database Schema
room_type_inventory (core table)
| Column | Type | Description |
|---|---|---|
| hotel_id | VARCHAR | PK part 1 |
| room_type_id | VARCHAR | PK part 2 |
| date | DATE | PK part 3 |
| total_inventory | INT | Available rooms (minus maintenance) |
| total_reserved | INT | Currently booked rooms |
Primary Key: (hotel_id, room_type_id, date)
Scale: 5,000 hotels × 20 types × 730 days = ~73M rows — fits in a single MySQL instance
Pre-populated for 2 years ahead. A nightly batch job adds new dates and archives old ones.
reservation
| Column | Type | Description |
|---|---|---|
| reservation_id | VARCHAR | PK (also idempotency key) |
| hotel_id | VARCHAR | FK |
| room_type_id | VARCHAR | FK |
| guest_id | VARCHAR | FK |
| start_date | DATE | Check-in date |
| end_date | DATE | Check-out date |
| status | ENUM | See state machine below |
Reservation Status State Machine
┌──→ paid ──→ checked_in ──→ checked_out
│
pending ──────┤
│
└──→ rejected ──→ refunded
(any state) ──→ canceled
room_type_rate
| Column | Type | Description |
|---|---|---|
| hotel_id | VARCHAR | PK part 1 |
| room_type_id | VARCHAR | PK part 2 |
| date | DATE | PK part 3 |
| rate | DECIMAL | Price for that night |
Key insight: Guests reserve a room type (King, Standard, Suite), NOT a specific room number. Room numbers are assigned at check-in.
7. Concurrency — Preventing Double Booking
The Problem
User A reads: 1 room left User B reads: 1 room left
User A books: SUCCESS User B books: SUCCESS
↓
DOUBLE BOOKED!
Three Solutions
Option 1: Pessimistic Locking
BEGIN;
SELECT * FROM room_type_inventory
WHERE hotel_id='H123' AND room_type_id='king' AND date='2024-07-01'
FOR UPDATE; -- locks the row
-- other transactions WAIT here until this one commits
UPDATE room_type_inventory SET total_reserved = total_reserved + 1 ...;
COMMIT; -- lock released
| Pros | Cons |
|---|---|
| Guaranteed correctness | Blocks other transactions |
| Simple to understand | Deadlock risk |
| Poor performance if transactions are slow |
Option 2: Optimistic Locking
-- Read with version
SELECT total_reserved, version FROM room_type_inventory
WHERE hotel_id='H123' AND room_type_id='king' AND date='2024-07-01';
-- Returns: total_reserved=48, version=5
-- Update only if version hasn't changed
UPDATE room_type_inventory
SET total_reserved = 49, version = 6
WHERE hotel_id='H123' AND room_type_id='king'
AND date='2024-07-01' AND version = 5;
-- If 0 rows affected → conflict → retry with fresh data
| Pros | Cons |
|---|---|
| Non-blocking reads | Many retries under high contention |
| Better performance at low contention | Wasted work on conflicts |
Option 3: Database Constraints (Recommended)
-- Add constraint to the table
ALTER TABLE room_type_inventory
ADD CONSTRAINT check_room_availability
CHECK (total_inventory - total_reserved >= 0);
-- Booking is a simple UPDATE
UPDATE room_type_inventory
SET total_reserved = total_reserved + 1
WHERE hotel_id='H123' AND room_type_id='king' AND date='2024-07-01';
-- If constraint violated → booking fails → return error to user
| Pros | Cons |
|---|---|
| Simplest implementation | Limited to single-row checks |
| Leverages database guarantees | Database-specific syntax |
| No application-level locking |
For hotel reservations (~3 TPS): Database constraints or optimistic locking are the best fit. Contention is low, so pessimistic locking is unnecessary overhead.
8. Overbooking
Hotels overbook by ~10% to account for cancellations and no-shows.
-- Modified constraint allowing 10% overbooking:
CHECK (total_reserved <= FLOOR(1.10 * total_inventory))
-- Example: 50 king rooms → up to 55 reservations allowed
The overbooking percentage is configurable per hotel based on historical cancellation data.
9. Scaling Strategies
Redis Cache (for read-heavy traffic)
Key: hotelID_roomTypeID_date
Value: available_rooms_count
TTL: varies (shorter for dates closer to today)
Read path: User → Redis (fast) → DB on cache miss
Write path: Booking → Update DB → Invalidate cache
The database is ALWAYS the source of truth. Cache staleness is acceptable for reads because the actual booking always validates against the database.
Database Sharding
Shard by hotel_id:
┌────────────────────────┐
│ Shard 1: Hotels 1-312 │ ~1,875 QPS
│ Shard 2: Hotels 313-625│ ~1,875 QPS
│ ... │
│ Shard 16: Hotels 4689+ │ ~1,875 QPS
└────────────────────────┘
Why hotel_id? → All queries filter by hotel_id
Single hotel data fits in one shard
10. Quick Comparison: Key Trade-offs
| Decision | Chosen Approach | Alternative | Why |
|---|---|---|---|
| Data model | Room-type level | Specific room level | Matches real hotel operations |
| Database | Relational (MySQL) | NoSQL | Need ACID for booking correctness |
| Concurrency | DB constraints | Pessimistic/optimistic locking | Low contention (~3 TPS) |
| Service boundary | Combined reservation + inventory | Separate services | Avoid distributed transactions |
| Consistency | Strong (for writes) | Eventual | Can't double-book |
| Cache strategy | Cache-aside with Redis | Write-through | DB is source of truth |
| Sharding key | hotel_id | date, guest_id | Matches query patterns |
11. What to Mention in Wrap-Up
If you finish early or want to impress:
- Search & ranking: How users find hotels (Elasticsearch, ranking algorithms)
- Notifications: Confirmation emails, SMS reminders
- Analytics: Booking trends, pricing optimization
- Multi-region: Global deployment, data replication
- Monitoring: Alerting on double-bookings, inventory discrepancies
12. Further Reading
| Resource | Link |
|---|---|
| Alex Xu — System Design Interview Vol. 2 (Chapter 7) | Available on Amazon |
| ByteByteGo — Hotel Reservation Course | bytebytego.com/courses/.../hotel-reservation-system |
| System Design Handbook — Hotel Booking | systemdesignhandbook.com/.../design-hotel-booking-system |
| Martin Kleppmann — Designing Data-Intensive Applications | O'Reilly, 2017 — Ch. 7 (Transactions), Ch. 9 (Consistency) |
| Pragmatic Engineer — Book Review | blog.pragmaticengineer.com |
| ByteByteGo Newsletter | blog.bytebytego.com |