Booking System Low-Level Design

By /

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.

{“@context”:”https://schema.org”,”@type”:”FAQPage”,”mainEntity”:[{“@type”:”Question”,”name”:”How does a PostgreSQL exclusion constraint prevent double-booking?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”The EXCLUDE USING GIST constraint uses a generalized search tree index to enforce that no two rows can simultaneously satisfy a set of conditions. For booking: EXCLUDE USING GIST (resource_id WITH =, tstzrange(start_time, end_time) WITH &&) WHERE status=’confirmed’. This says: no two confirmed bookings for the same resource_id can have overlapping time ranges (&&). The constraint is enforced atomically with every INSERT or UPDATE — the database engine rejects overlapping bookings before they are written, without any application-level SELECT for duplicate detection.”}},{“@type”:”Question”,”name”:”What is the hold-then-confirm pattern in a booking system?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Phase 1 (Hold): when the user selects a timeslot and proceeds to checkout, create a Booking with status=held and held_until=NOW()+10min. The exclusion constraint blocks any other booking for the same slot during the hold window. Return the booking_id to the client. Phase 2 (Confirm): after payment succeeds, update status from held to confirmed. If payment fails or the user abandons checkout, a background job resets expired holds to status=expired, releasing the slot for others. Without holds, two users can both see the slot as available, both pay, and collide at confirmation.”}},{“@type”:”Question”,”name”:”How do you query available resources efficiently?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Use a NOT IN subquery with a GiST range overlap check: SELECT * FROM Resource WHERE id NOT IN (SELECT resource_id FROM Booking WHERE status IN (‘confirmed’,’held’) AND tstzrange(start_time, end_time) && tstzrange(:start, :end)). Index Booking on (resource_id, tstzrange(start_time, end_time)) using GiST. For large datasets, consider the inverse: check a specific resource rather than all resources. For search UX (hotel availability calendars), precompute unavailable date ranges into a separate AvailabilityBlock table updated by triggers.”}},{“@type”:”Question”,”name”:”How do you implement a cancellation policy with partial refunds?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Calculate hours between now and the booking start_time. Apply tiered refund rules: >48h before = 100% refund, 24-48h = 50% refund, <24h = 0% refund. Store the policy_id on the booking at creation time (not hardcoded) so the correct policy at time of booking applies even if rates change later. Trigger the refund via the payment processor using the original charge_id. Update booking status to cancelled and record cancelled_at and refund_amount. Send a cancellation confirmation email with the refund amount and expected return timeline.”}},{“@type”:”Question”,”name”:”How do you handle timezone-aware bookings correctly?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Store all timestamps as TIMESTAMPTZ (timezone-aware) in UTC. When a user in Tokyo books a "9am-10am" appointment at a New York venue, store the UTC equivalent of 9am New York time — not 9am Tokyo time. Convert user input to UTC at the API boundary using the resource’s timezone (stored on the Resource record). Display times to users converted back to their local timezone. Never store "9:00" as a naive local time string — this breaks for users in different timezones and fails during DST transitions.”}}]}

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.

Scroll to Top