Booking System Low-Level Design

Booking System — Low-Level Design

A booking system manages reservations for time-slotted resources: hotel rooms, restaurant tables, meeting rooms, or appointment slots. The core challenge is preventing double-booking under concurrent requests. This design is asked at Airbnb, OpenTable, and Lyft (for ride scheduling).

Core Data Model

Resource
  id              BIGSERIAL PK
  name            TEXT NOT NULL       -- 'Room 101', 'Table 5'
  resource_type   TEXT NOT NULL       -- 'room', 'table', 'court'
  capacity        INT
  metadata        JSONB               -- amenities, photos, location

Booking
  id              BIGSERIAL PK
  resource_id     BIGINT FK NOT NULL
  user_id         BIGINT NOT NULL
  start_time      TIMESTAMPTZ NOT NULL
  end_time        TIMESTAMPTZ NOT NULL
  status          TEXT DEFAULT 'confirmed'  -- confirmed, cancelled, completed
  external_ref    TEXT                -- payment or order reference
  created_at      TIMESTAMPTZ

BlockedPeriod
  id              BIGSERIAL PK
  resource_id     BIGINT FK NOT NULL
  start_time      TIMESTAMPTZ NOT NULL
  end_time        TIMESTAMPTZ NOT NULL
  reason          TEXT               -- 'maintenance', 'owner_block'

-- Constraint: no two confirmed bookings for same resource can overlap
CREATE UNIQUE INDEX no_double_booking
  ON Booking USING EXCLUDE USING GIST (
    resource_id WITH =,
    tstzrange(start_time, end_time, '[)') WITH &&
  )
  WHERE status = 'confirmed';

Preventing Double-Booking: PostgreSQL Exclusion Constraint

The EXCLUDE USING GIST constraint is the cleanest solution. It enforces at the database level that no two confirmed bookings for the same resource can have overlapping time ranges:

-- This INSERT will FAIL if any confirmed booking overlaps [check_in, check_out)
INSERT INTO Booking (resource_id, user_id, start_time, end_time, status)
VALUES (101, 42, '2024-03-15 14:00', '2024-03-15 16:00', 'confirmed');
-- ERROR: conflicting key value violates exclusion constraint "no_double_booking"

-- Requires btree_gist extension
CREATE EXTENSION IF NOT EXISTS btree_gist;

Availability Query

def get_available_resources(resource_type, start_time, end_time, capacity=None):
    return db.execute("""
        SELECT r.*
        FROM Resource r
        WHERE r.resource_type = %(type)s
          AND (%(capacity)s IS NULL OR r.capacity >= %(capacity)s)
          AND r.id NOT IN (
            -- Resources with overlapping confirmed bookings
            SELECT resource_id FROM Booking
            WHERE status = 'confirmed'
              AND tstzrange(start_time, end_time, '[)')
                  && tstzrange(%(start)s, %(end)s, '[)')
          )
          AND r.id NOT IN (
            -- Resources with overlapping blocks
            SELECT resource_id FROM BlockedPeriod
            WHERE tstzrange(start_time, end_time, '[)')
                  && tstzrange(%(start)s, %(end)s, '[)')
          )
        ORDER BY r.name
    """, {'type': resource_type, 'capacity': capacity,
          'start': start_time, 'end': end_time})

Two-Phase Booking: Hold then Confirm

-- Phase 1: Create a temporary hold (status='held', expires in 10 minutes)
INSERT INTO Booking (resource_id, user_id, start_time, end_time, status, held_until)
VALUES (%(rid)s, %(uid)s, %(start)s, %(end)s, 'held', NOW() + INTERVAL '10 minutes');
-- The exclusion constraint blocks conflicting holds AND confirmations
-- Returns booking_id to client

-- Phase 2: Confirm after payment
UPDATE Booking
SET status='confirmed', external_ref=%(payment_ref)s, held_until=NULL
WHERE id=%(booking_id)s AND status='held' AND held_until > NOW();
-- Fails silently if hold expired or already confirmed

-- Background cleanup: release expired holds
UPDATE Booking SET status='expired'
WHERE status='held' AND held_until <= NOW();

Include held bookings in the exclusion constraint so the hold prevents others from booking the same slot while the user completes payment.

Cancellation and Refund Policy

def cancel_booking(booking_id, user_id):
    booking = db.get(Booking, booking_id)
    if booking.user_id != user_id:
        raise Forbidden()
    if booking.status != 'confirmed':
        raise InvalidState(f'Cannot cancel booking in status {booking.status}')

    hours_until_start = (booking.start_time - now()).total_seconds() / 3600

    # Determine refund amount based on cancellation policy
    if hours_until_start >= 48:
        refund_pct = 100
    elif hours_until_start >= 24:
        refund_pct = 50
    else:
        refund_pct = 0

    db.execute("""
        UPDATE Booking SET status='cancelled', cancelled_at=NOW()
        WHERE id=%(id)s
    """, {'id': booking_id})

    if refund_pct > 0:
        issue_refund(booking.external_ref, refund_pct)

    return {'refund_percentage': refund_pct}

Key Interview Points

• Exclusion constraints over application-level locking: SELECT FOR UPDATE + application logic is error-prone and slow. The GIST exclusion constraint enforces correctness at the database level with a single INSERT.
• Hold pattern for multi-step checkout: Without a hold, two users can both see a slot as available, proceed to payment, and collide at confirmation. The hold (with TTL) reserves the slot during checkout.
• Time range representation: Use half-open intervals [start, end) so consecutive bookings do not overlap. A 2pm–3pm booking and a 3pm–4pm booking should both be valid for the same resource.
• Index on (resource_id, start_time, end_time): Availability queries do range scans. Without this index, every availability check is a full table scan.

Booking system and reservation management design is discussed in Airbnb system design interview questions.

Booking system and slot reservation design is covered in Uber system design interview preparation.

Booking system and ride scheduling design is discussed in Lyft system design interview guide.