Online Booking System for Website

Developing an Online Booking System for a Website

A booking system is not just a form with date selection. It's slot management, real-time availability checking, hold management, concurrent request handling, and notification chains. Incorrect implementation leads to double bookings or "stuck" slots.

Key Entities

-- Bookable resource (hall, specialist, room, table, etc.)
CREATE TABLE bookable_resources (
    id              SERIAL PRIMARY KEY,
    type            VARCHAR(50) NOT NULL,  -- 'specialist', 'room', 'table', 'car'
    name            VARCHAR(255) NOT NULL,
    config          JSONB,                 -- resource-specific settings
    is_active       BOOLEAN DEFAULT TRUE
);

-- Resource availability schedule
CREATE TABLE resource_schedules (
    id              SERIAL PRIMARY KEY,
    resource_id     INTEGER REFERENCES bookable_resources(id),
    weekday         SMALLINT,              -- 0=Mon, 6=Sun; NULL = specific date
    specific_date   DATE,
    start_time      TIME NOT NULL,
    end_time        TIME NOT NULL,
    slot_duration   INTERVAL,             -- NULL = resource not divided into slots
    is_available    BOOLEAN DEFAULT TRUE
);

-- Actual bookings
CREATE TABLE bookings (
    id              BIGSERIAL PRIMARY KEY,
    resource_id     INTEGER REFERENCES bookable_resources(id),
    user_id         INTEGER,
    guest_name      VARCHAR(255),
    guest_email     VARCHAR(255),
    guest_phone     VARCHAR(50),
    starts_at       TIMESTAMP NOT NULL,
    ends_at         TIMESTAMP NOT NULL,
    status          VARCHAR(20) DEFAULT 'pending',
    -- pending | confirmed | cancelled | no_show | completed
    notes           TEXT,
    metadata        JSONB,
    created_at      TIMESTAMP DEFAULT NOW(),
    confirmed_at    TIMESTAMP,
    cancelled_at    TIMESTAMP,
    CONSTRAINT no_overlap EXCLUDE USING gist (
        resource_id WITH =,
        tsrange(starts_at, ends_at, '[)') WITH &&
    ) WHERE (status NOT IN ('cancelled'))
);

The EXCLUDE USING gist constraint with tsrange is the most reliable way to prevent double booking at the database level. It works atomically and doesn't depend on application logic.

Availability Checking Algorithm

def get_available_slots(resource_id: int, date: date) -> list[TimeSlot]:
    # 1. Get the resource schedule for this day
    schedule = get_schedule(resource_id, date)
    if not schedule or not schedule.is_available:
        return []

    # 2. Generate all theoretical slots
    all_slots = generate_slots(
        start=schedule.start_time,
        end=schedule.end_time,
        duration=schedule.slot_duration or timedelta(hours=1),
    )

    # 3. Get already booked intervals from the database
    booked = get_booked_intervals(resource_id, date)

    # 4. Filter out booked slots
    return [
        slot for slot in all_slots
        if not any(slot.overlaps(b) for b in booked)
    ]

Temporary Slot Hold

Between slot selection and payment, time passes. To prevent the slot from being taken during this time, a hold mechanism is implemented:

HOLD_TTL = 600  # 10 minutes

def hold_slot(resource_id: int, starts_at: datetime, session_id: str) -> str:
    hold_key = f"hold:{resource_id}:{starts_at.isoformat()}"
    # SET NX — only if not already taken
    success = redis.set(hold_key, session_id, nx=True, ex=HOLD_TTL)
    if not success:
        existing = redis.get(hold_key)
        if existing and existing.decode() != session_id:
            raise SlotAlreadyHeld("Slot is taken by another user")
    return hold_key

def confirm_booking(hold_key: str, booking_data: dict) -> Booking:
    session_id = redis.get(hold_key)
    if not session_id:
        raise HoldExpired("Slot hold time expired")

    with db.transaction():
        booking = create_booking(booking_data)
        redis.delete(hold_key)
    return booking

Handling Concurrent Requests

Even with EXCLUDE constraint, a race condition is possible: two requests check availability simultaneously, both see the slot as free. The constraint will catch the second INSERT and throw ExclusionViolationError. The application must handle this:

try:
    booking = create_booking(data)
except ExclusionViolationError:
    raise BookingConflict("This slot was just booked. Please select another time.")

Notifications

Reminders are sent via scheduled jobs — cron every hour selects bookings with starts_at in 24h or 1h and no corresponding notification sent.

Cancellation and Change Rules

Flexible cancellation policies:

{
  "cancellation_policy": {
    "free_cancellation_hours": 24,
    "partial_refund_hours": 12,
    "partial_refund_percent": 50,
    "no_refund_hours": 2
  }
}

The policy is stored at the resource or booking type level and applied automatically when calculating the refund.

Payment Integration

Booking may require prepayment or full payment. Stripe integration:

def create_payment_intent(booking: Booking, amount: int, currency: str):
    intent = stripe.PaymentIntent.create(
        amount=amount,
        currency=currency,
        metadata={
            'booking_id': str(booking.id),
            'resource_id': str(booking.resource_id),
        },
        capture_method='manual',  # auto-charge on confirmation
    )
    booking.payment_intent_id = intent.id
    booking.save()
    return intent.client_secret

With capture_method='manual', funds are frozen on the card but only charged when capture() is called — this is convenient for bookings with manual confirmation.

Implementation Timeline

Basic system with one resource type, without payment — 8–10 business days. Multiple resource types, schedule management through CMS, payment integration, SMS notifications, cancellation policies, mobile view — 14–18 business days.