Hotel Room Booking on Website

Implementing Hotel Room Booking on a Website

Hotel booking is one of the most complex booking subtypes: multi-night stays, rates with different cancellation terms, seasonal pricing, synchronization with external systems (OTA, PMS). Even basic implementation must handle date ranges correctly, not time slots.

Data Model

CREATE TABLE room_types (
    id              SERIAL PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,   -- 'Standard', 'Deluxe', 'Suite'
    description     TEXT,
    max_occupancy   SMALLINT NOT NULL,
    area_sqm        NUMERIC(5,1),
    amenities       TEXT[],
    images          JSONB DEFAULT '[]',
    base_price      NUMERIC(10,2)
);

CREATE TABLE rooms (
    id              SERIAL PRIMARY KEY,
    room_type_id    INTEGER REFERENCES room_types(id),
    room_number     VARCHAR(10) NOT NULL,
    floor           SMALLINT,
    is_active       BOOLEAN DEFAULT TRUE
);

-- Dynamic rates (seasonality, discounts, minimum stay)
CREATE TABLE rate_plans (
    id              SERIAL PRIMARY KEY,
    room_type_id    INTEGER REFERENCES room_types(id),
    name            VARCHAR(100),
    price           NUMERIC(10,2),
    valid_from      DATE NOT NULL,
    valid_until     DATE NOT NULL,
    min_stay_nights SMALLINT DEFAULT 1,
    cancellation_hours INTEGER DEFAULT 24,  -- free cancellation N hours before
    is_refundable   BOOLEAN DEFAULT TRUE,
    includes_breakfast BOOLEAN DEFAULT FALSE
);

CREATE TABLE reservations (
    id              BIGSERIAL PRIMARY KEY,
    room_id         INTEGER REFERENCES rooms(id),
    room_type_id    INTEGER,
    rate_plan_id    INTEGER REFERENCES rate_plans(id),
    check_in        DATE NOT NULL,
    check_out       DATE NOT NULL,
    adults          SMALLINT DEFAULT 1,
    children        SMALLINT DEFAULT 0,
    guest_name      VARCHAR(255) NOT NULL,
    guest_email     VARCHAR(255) NOT NULL,
    guest_phone     VARCHAR(50),
    total_amount    NUMERIC(12,2),
    status          VARCHAR(20) DEFAULT 'pending',
    -- pending | confirmed | checked_in | checked_out | cancelled | no_show
    payment_status  VARCHAR(20) DEFAULT 'unpaid',
    notes           TEXT,
    source          VARCHAR(30) DEFAULT 'website',
    external_id     VARCHAR(100),   -- ID in OTA/PMS on sync
    created_at      TIMESTAMP DEFAULT NOW(),
    CONSTRAINT no_room_overlap EXCLUDE USING gist (
        room_id WITH =,
        daterange(check_in, check_out, '[)') WITH &&
    ) WHERE (status NOT IN ('cancelled', 'no_show'))
);

Search for Available Rooms

def search_available_rooms(check_in: date, check_out: date, adults: int, children: int = 0):
    nights = (check_out - check_in).days
    guests = adults + children

    return db.fetchall("""
        SELECT
            rt.*,
            COUNT(r.id) AS available_count,
            rp.price AS nightly_price,
            rp.price * %(nights)s AS total_price,
            rp.is_refundable,
            rp.includes_breakfast,
            rp.min_stay_nights
        FROM room_types rt
        JOIN rooms r ON r.room_type_id = rt.id AND r.is_active = TRUE
        JOIN rate_plans rp ON rp.room_type_id = rt.id
            AND rp.valid_from <= %(check_in)s
            AND rp.valid_until >= %(check_out)s
            AND rp.min_stay_nights <= %(nights)s
        WHERE rt.max_occupancy >= %(guests)s
          AND r.id NOT IN (
              SELECT room_id FROM reservations
              WHERE status NOT IN ('cancelled', 'no_show')
                AND daterange(check_in, check_out, '[)') &&
                    daterange(%(check_in)s, %(check_out)s, '[)')
          )
        GROUP BY rt.id, rp.id
        HAVING COUNT(r.id) > 0
        ORDER BY rp.price ASC
    """, {'check_in': check_in, 'check_out': check_out,
          'nights': nights, 'guests': guests})

Dynamic Pricing

Price per night can change by day of week, occupancy, season:

def calculate_total_price(room_type_id: int, check_in: date, check_out: date) -> Decimal:
    total = Decimal(0)
    current = check_in
    while current < check_out:
        rate = get_rate_for_date(room_type_id, current)
        if rate is None:
            raise NoRateAvailable(f"No rate for {current}")
        total += rate.price
        current += timedelta(days=1)
    return total

def get_rate_for_date(room_type_id: int, d: date) -> Optional[RatePlan]:
    return db.fetchone("""
        SELECT * FROM rate_plans
        WHERE room_type_id = %s
          AND valid_from <= %s AND valid_until >= %s
        ORDER BY price DESC   -- priority — special (higher) rate
        LIMIT 1
    """, [room_type_id, d, d])

Assigning Specific Room

When booking is created, reservations records room_type_id, but specific number (room_id) is assigned at check-in (or by admin beforehand):

def assign_room(reservation_id: int) -> Room:
    res = get_reservation(reservation_id)
    room = db.fetchone("""
        SELECT r.* FROM rooms r
        WHERE r.room_type_id = %(type_id)s
          AND r.is_active = TRUE
          AND r.id NOT IN (
              SELECT room_id FROM reservations
              WHERE status NOT IN ('cancelled', 'no_show')
                AND daterange(check_in, check_out, '[)') &&
                    daterange(%(check_in)s, %(check_out)s, '[)')
                AND room_id IS NOT NULL
          )
        LIMIT 1
    """, {'type_id': res.room_type_id, 'check_in': res.check_in, 'check_out': res.check_out})

    if not room:
        raise NoRoomAvailable("No physical room available for this reservation")

    db.execute("UPDATE reservations SET room_id=%s WHERE id=%s", [room.id, reservation_id])
    return room

Channel Manager / OTA Integration

For sync with Booking.com, Expedia, Airbnb use Channel Manager (TravelLine, Bnovo, Wubook). Standard protocol — OTA XML (OpenTravel Alliance) or iCal for simple cases.

iCal sync for Airbnb:

def generate_ical_feed(room_id: int) -> str:
    bookings = get_confirmed_bookings(room_id)
    cal = Calendar()
    cal.add('prodid', '-//Hotel Booking//EN')
    cal.add('version', '2.0')

    for b in bookings:
        event = Event()
        event.add('uid', f"booking-{b.id}@hotel.example.com")
        event.add('dtstart', b.check_in)
        event.add('dtend', b.check_out)
        event.add('summary', 'BLOCKED')
        cal.add_component(event)

    return cal.to_ical().decode('utf-8')

Cancellation and Refunds

def cancel_reservation(reservation_id: int, initiator: str) -> dict:
    res = get_reservation(reservation_id)
    hours_to_arrival = (
        datetime.combine(res.check_in, time(14, 0)) - datetime.utcnow()
    ).total_seconds() / 3600

    rate = get_rate_plan(res.rate_plan_id)
    if rate.is_refundable and hours_to_arrival >= rate.cancellation_hours:
        refund_amount = res.total_amount
        refund_type = 'full'
    elif not rate.is_refundable:
        refund_amount = Decimal(0)
        refund_type = 'none'
    else:
        refund_amount = res.total_amount * Decimal('0.5')
        refund_type = 'partial'

    process_refund(res.payment_id, refund_amount)
    update_reservation_status(reservation_id, 'cancelled', initiator)
    send_cancellation_email(res, refund_amount, refund_type)

    return {'refund': refund_amount, 'type': refund_type}

Implementation Timeline

Basic module without dynamic rates and no PMS integration — 10–13 business days. Dynamic pricing, room assignment, iCal sync, rate plan management, guest portal — 16–22 business days.