Rental Management System

SQLite Database Architecture

SQLiteSQLTriggersViews

About

Database architecture for a hotel property management system. The core concept is "one night = one record" in reservation_line table, which prevents double-booking at database level via UNIQUE constraint. The system handles multi-channel reservations (Booking, Airbnb, direct), automatic access code generation for smart locks, housekeeping task workflows, and complete audit logging via triggers. Designed for small to medium hotels (up to 100 rooms) with split-stay support (room changes during reservation).

Code

schema.sqlsql
-- Booker vs Guest separation (B2B pattern)
CREATE TABLE reservation (
    res_id TEXT PRIMARY KEY,

    -- Booker: who pays (person or company)
    booker_type TEXT NOT NULL DEFAULT 'individual',
    booker_company_name TEXT NULL,
    booker_company_nip TEXT NULL,
    booker_email TEXT NOT NULL,

    -- Guest: who actually stays
    guest_first_name TEXT NOT NULL,
    guest_last_name TEXT NOT NULL,
    guest_phone TEXT NULL,  -- for access codes!

    send_codes_to TEXT NOT NULL DEFAULT 'guest',

    CHECK (booker_type IN ('individual', 'company')),
    CHECK (send_codes_to IN ('guest', 'booker', 'both'))
);

Separation of booker (who pays) and guest (who stays) - crucial for B2B corporate bookings

schema.sqlsql
-- Reservation Lines: one night = one record
-- Prevents double-booking at database level
CREATE TABLE reservation_line (
    line_id TEXT PRIMARY KEY,
    res_id TEXT NOT NULL,
    room_id TEXT NOT NULL,
    stay_date TEXT NOT NULL,
    price_night REAL NULL,

    -- THE MOST IMPORTANT CONSTRAINT!
    -- A room cannot be sold twice for the same night
    UNIQUE (room_id, stay_date),

    FOREIGN KEY (res_id) REFERENCES reservation(res_id),
    FOREIGN KEY (room_id) REFERENCES room(room_id)
);

UNIQUE constraint on (room_id, stay_date) prevents double-booking at database level

schema.sqlsql
-- Access codes for smart locks
CREATE TABLE access_code (
    code_id TEXT PRIMARY KEY,
    res_id TEXT NOT NULL,
    room_id TEXT NOT NULL,
    code TEXT NOT NULL,           -- e.g. "123456"
    valid_from TEXT NOT NULL,     -- check-in time
    valid_to TEXT NOT NULL,       -- check-out time
    status TEXT NOT NULL,         -- 'planned' → 'sent' → 'blocked'
    sent_at TEXT NULL,
    revoked_at TEXT NULL,

    FOREIGN KEY (res_id) REFERENCES reservation(res_id),
    FOREIGN KEY (room_id) REFERENCES room(room_id),
    CHECK (status IN ('planned', 'sent', 'blocked'))
);

Access codes generated automatically via triggers when reservation is paid