Design a Hotel Booking System: No Double Bookings, No Partial Stays

"Design a hotel booking system" sounds like a search problem, and part of it is. But the question interviewers care about is narrower and harder: two guests press book for the last room in the same second. Exactly one of them must win. And a guest who asked for four nights must get all four or none, because three nights out of four is not what anybody bought.

The problem it solves

The requirements that shape the design:

• Search for availability by city, dates and room type. Read-heavy, and a few seconds of staleness is acceptable.
• Book a room type for a date range. Must be exact: never sell a night twice, never sell part of a stay.
• Cancel, which returns every night of the stay to inventory.
• Pay, which is slow and external, and must not hold the inventory hostage while it runs.

Searches vastly outnumber bookings, so the paths are separate: search reads replicas or a cache, booking goes to the primary database.

The intuition

Model inventory as one row per room type per night, with a total and a booked count. A stay is a range of those rows, and the whole design follows from treating the range as one unit:

1. Dates are half-open. A stay from the 3rd to the 6th is the nights of the 3rd, 4th and 5th; the check-out day is not a night. That makes back-to-back stays share nothing, the same convention as interval overlap checks.
2. One transaction for the whole range. Every night is updated, or none is. That is the atomicity of ACID transactions.
3. The check and the write are one statement. UPDATE ... SET booked = booked + 1 WHERE ... AND booked < total either takes the night or touches nothing. If the number of rows updated is not the number of nights, roll back.
4. A unique constraint underneath. If rooms are assigned, a unique key on (room, night) refuses a second row even when the code above it is wrong.
5. Holds, not long transactions, around payment. Take the inventory in a short transaction as a hold with an expiry, then confirm it once payment succeeds; a sweeper releases expired holds. A retry of the confirm carries an idempotency key, as in the payment system design.

Watch it run

The animation follows the lesson's design. A stay is not a night but a range of them, and every one has to be secured together. Guest A asks for the third to the fifth: one request, three rows behind it. A transaction opens and every night in the range is locked, not just checked. Guest B wants the fourth; it is inside the range, so that request waits. All three nights still have a room, so the count is raised on each: 3 of 3 rows updated. Commit, and guest A holds the whole stay, never part of it. Had the middle night been sold, the whole thing would roll back, with 2 of 3 rows updated, because two of three is not a stay. Guest B is let through now and finds the fourth already gone: zero rows updated, and nothing double booked. Under all of it, one constraint refuses a second row for the same room and night, the last defence. The bill is contention: a longer stay locks more rows, for longer.

The code

A toy model on Python's built-in SQLite, so the transactions are real. Availability is counted per room type and night; room assignment is guarded by a primary key on (room, night):

import random
import sqlite3
from datetime import date, timedelta

def nights(check_in, check_out):
    """Half-open range: the check-out day is not a night of the stay."""
    d, end = date.fromisoformat(check_in), date.fromisoformat(check_out)
    out = []
    while d < end:
        out.append(d.isoformat())
        d += timedelta(days=1)
    return out

SCHEMA = """
CREATE TABLE availability (room_type TEXT, night TEXT, total INTEGER, booked INTEGER,
                           PRIMARY KEY (room_type, night));
CREATE TABLE rooms (room TEXT PRIMARY KEY, room_type TEXT);
CREATE TABLE room_nights (room TEXT, night TEXT, booking INTEGER,
                          PRIMARY KEY (room, night));          -- the last defence
"""

def open_hotel(rooms, first, last):
    db = sqlite3.connect(":memory:", isolation_level=None)      # we issue BEGIN/COMMIT
    db.executescript(SCHEMA)
    db.executemany("INSERT INTO rooms VALUES (?, ?)", rooms)
    for rtype in sorted({t for _, t in rooms}):
        total = sum(t == rtype for _, t in rooms)
        db.executemany("INSERT INTO availability VALUES (?, ?, ?, 0)",
                       [(rtype, n, total) for n in nights(first, last)])
    return db

def book(db, booking, rtype, check_in, check_out):
    want = nights(check_in, check_out)
    marks = ",".join("?" * len(want))
    db.execute("BEGIN IMMEDIATE")
    try:
        cur = db.execute(f"UPDATE availability SET booked = booked + 1 WHERE room_type = ? "
                         f"AND night IN ({marks}) AND booked < total", [rtype, *want])
        if cur.rowcount != len(want):              # some night is full: not a stay
            db.execute("ROLLBACK")
            return f"sold out ({cur.rowcount} of {len(want)} nights free)"
        room = db.execute(f"SELECT room FROM rooms WHERE room_type = ? AND room NOT IN "
                          f"(SELECT room FROM room_nights WHERE night IN ({marks})) "
                          f"ORDER BY room LIMIT 1", [rtype, *want]).fetchone()
        if room is None:                           # free every night, never in one room
            db.execute("ROLLBACK")
            return "no single room for the whole stay"
        db.executemany("INSERT INTO room_nights VALUES (?, ?, ?)",
                       [(room[0], n, booking) for n in want])
        db.execute("COMMIT")
        return room[0]
    except Exception:
        db.execute("ROLLBACK")
        raise

def cancel(db, booking):
    db.execute("BEGIN IMMEDIATE")
    rows = db.execute("SELECT r.room_type, n.night FROM room_nights n JOIN rooms r "
                      "ON r.room = n.room WHERE n.booking = ?", (booking,)).fetchall()
    db.executemany("UPDATE availability SET booked = booked - 1 "
                   "WHERE room_type = ? AND night = ?", rows)
    db.execute("DELETE FROM room_nights WHERE booking = ?", (booking,))
    db.execute("COMMIT")
    return len(rows)

print(nights("2026-03-03", "2026-03-06"))       # ['2026-03-03', '2026-03-04', '2026-03-05']
db = open_hotel([("101", "double"), ("102", "double")], "2026-03-01", "2026-03-10")
print(book(db, 1, "double", "2026-03-03", "2026-03-06"))    # 101
print(book(db, 2, "double", "2026-03-04", "2026-03-05"))    # 102
print(book(db, 3, "double", "2026-03-02", "2026-03-05"))    # sold out (2 of 3 nights free)
print(book(db, 4, "double", "2026-03-06", "2026-03-08"))    # 101
print(db.execute("SELECT SUM(booked) FROM availability").fetchone()[0])   # 6
try:                                            # a buggy path that skips every check
    db.execute("INSERT INTO room_nights VALUES ('101', '2026-03-04', 99)")
except sqlite3.IntegrityError as e:
    print(type(e).__name__)                     # IntegrityError

Booking 3 found the 4th full, so its two successful updates were rolled back: six booked nights in total. Booking 4 starts the day booking 1 checks out and shares no night with it. The buggy insert is refused by the key. Next, two connections run the naive "look, then take" for the last room, interleaved, then the guarded version:

a = sqlite3.connect("file:race?mode=memory&cache=shared", uri=True, isolation_level=None)
b = sqlite3.connect("file:race?mode=memory&cache=shared", uri=True, isolation_level=None)
a.execute("CREATE TABLE availability (night TEXT PRIMARY KEY, total INTEGER, booked INTEGER)")
a.execute("INSERT INTO availability VALUES ('2026-03-04', 1, 0)")
check = "SELECT booked < total FROM availability WHERE night = '2026-03-04'"
take = "UPDATE availability SET booked = booked + 1 WHERE night = '2026-03-04'"
saw_a, saw_b = a.execute(check).fetchone()[0], b.execute(check).fetchone()[0]   # both look
a.execute(take); b.execute(take)                                                # both take
print(saw_a, saw_b, a.execute("SELECT booked, total FROM availability").fetchone())  # 1 1 (2, 1)
a.execute("UPDATE availability SET booked = 0")
guarded = take + " AND booked < total"
print(a.execute(guarded).rowcount, b.execute(guarded).rowcount)                 # 1 0

two = open_hotel([("A", "twin"), ("B", "twin")], "2026-03-01", "2026-03-10")
print(book(two, 1, "twin", "2026-03-01", "2026-03-02"), book(two, 2, "twin", "2026-03-01", "2026-03-03"),
      book(two, 3, "twin", "2026-03-03", "2026-03-04"), cancel(two, 1))   # A B A 1
print(book(two, 4, "twin", "2026-03-02", "2026-03-04"))   # no single room for the whole stay

The naive pair sold one room twice. Guarded, the second update matches zero rows. The last two lines show a subtler failure: after a cancellation, each requested night has a free twin, but in different rooms, which is why many systems sell a room type and assign the actual room at check-in (from memory). Finally, 150 seeded hotels each take 40 random bookings and cancellations, compared with a reference written in plain Python, and the counts are checked against the assigned rows:

def reference(owner, rooms, op):
    if op[0] == "cancel":
        mine = [key for key, bk in owner.items() if bk == op[1]]
        for key in mine:
            del owner[key]
        return len(mine)
    _, bk, rtype, ci, co = op
    want = nights(ci, co)
    of_type = sorted(r for r, t in rooms if t == rtype)
    free = sum(any((r, n) not in owner for r in of_type) for n in want)
    if free != len(want):
        return f"sold out ({free} of {len(want)} nights free)"
    for r in of_type:
        if all((r, n) not in owner for n in want):
            owner.update({(r, n): bk for n in want})
            return r
    return "no single room for the whole stay"

ok = True
for seed in range(150):
    rng = random.Random(seed)
    rooms = [(f"r{i}", rng.choice(["single", "double"])) for i in range(rng.randint(1, 4))]
    db, owner, ids = open_hotel(rooms, "2026-03-01", "2026-03-15"), {}, []
    for bk in range(40):
        if ids and rng.random() < 0.25:
            op = ("cancel", ids.pop(rng.randrange(len(ids))))
            got = cancel(db, op[1])
        else:
            start = rng.randint(1, 12)
            op = ("book", bk, rng.choice(["single", "double"]),
                  f"2026-03-{start:02d}", f"2026-03-{start + rng.randint(1, 3):02d}")
            got = book(db, *op[1:])
            ids.append(bk)
        ok &= got == reference(owner, rooms, op)
    counted = dict(((t, n), c) for t, n, c in db.execute(
        "SELECT r.room_type, n.night, COUNT(*) FROM room_nights n JOIN rooms r "
        "ON r.room = n.room GROUP BY 1, 2"))
    for t, n, total, booked in db.execute("SELECT * FROM availability"):
        ok &= booked == counted.get((t, n), 0) <= total
print(ok)                                       # True

The complexity

• A booking: one transaction touching k inventory rows for k nights, plus k inserts; locks are held for its duration.
• Search: served from replicas or a cache, so it never competes with booking locks.
• Storage: room types × nights in the booking horizon, small even for large chains.

Where it goes wrong

• Check-then-act. A SELECT followed by an UPDATE is the race above. Put the condition in the UPDATE, or lock with SELECT ... FOR UPDATE under a suitable isolation level.
• One transaction per night. A failure halfway leaves a partial stay.
• Holding locks across payment. A slow card network then blocks every neighbouring booking. Use a short hold with an expiry.
• Locking nights in arbitrary order. Two overlapping stays can deadlock. Update nights in date order, the lock ordering rule.
• Closed date ranges and time zones. Counting the check-out day double-books turnovers; nights belong to the hotel's local calendar, not the server's.

When it shows up in interviews

As "design a hotel booking system", and in the same shape as cinema seats, event tickets and restaurant tables. Follow-ups: the last-room race, partial ranges, holds during payment, a hot date, and intentional overbooking to offset no-shows, which is just a total set above the room count (from memory). As of October 2026, conditional updates and unique constraints work the same way in every mainstream relational database.

How to say it in an interview

"Inventory is one row per room type per night, with a total and a booked count, and dates are half-open. A booking runs one transaction over the whole range: a conditional UPDATE that increments only where booked < total, and if the rows updated are fewer than the nights, I roll back. A unique key on room and night is the last defence. Search reads replicas or a cache; booking hits the primary. Payment happens after a short hold with an expiry, confirmed with an idempotency key. The cost is contention on hot dates, so transactions stay short and lock nights in date order."