ErdDocs

Example schemas / Hotel Management System

Hotel Management System ER diagram

A hotel management system database books a room for a date range: guests make bookings, each booking holds one or more rooms between check-in and check-out, and rooms belong to room types that carry the price. Availability is a query over bookings, not a column.

Your schema

Hover a table to light up its relations, click to pin. Switch to Data Dictionary for the printable reference version of this schema.

What each table does

  • guests — The account; contact details for the confirmation email.
  • room_types / rooms — Type carries capacity and price; the room is a numbered instance of a type — the books/copies split wearing a different hat.
  • bookings — The date range and the lifecycle; the CHECK constraint outlaws zero-night stays.
  • booking_rooms — Junction with a frozen night_price — family trips book several rooms on one reservation.
  • payments — Deposit, balance, refund: rows, not columns.

Design decisions worth copying

  • There is no is_available column on rooms. Availability depends on the dates you ask about — it is a query over booking_rooms with overlapping ranges, and a stored flag is stale the moment it is written.
  • Price lives on room_types but is FROZEN into booking_rooms per night at booking time — the room_types price is an offer, the booking_rooms price is a contract.
  • The CHECK (check_out > check_in) makes impossible stays unrepresentable — cheaper than every query defending against them.

The SQL

PostgreSQL dialect; paste it into the tool above (it is already there), into your own database, or into a migration. Copy freely — example schemas are meant to be taken.

CREATE TABLE guests (
    id         bigserial    PRIMARY KEY,
    email      varchar(255) NOT NULL UNIQUE,
    full_name  varchar(120) NOT NULL,
    phone      varchar(30)
);

CREATE TABLE room_types (
    id          bigserial     PRIMARY KEY,
    name        varchar(60)   NOT NULL UNIQUE,
    capacity    int           NOT NULL,
    base_price  numeric(10,2) NOT NULL
);
COMMENT ON TABLE room_types IS 'Standard / Deluxe / Suite — the price lives here, not on the room';

CREATE TABLE rooms (
    id            bigserial   PRIMARY KEY,
    room_type_id  bigint      NOT NULL REFERENCES room_types(id),
    number        varchar(10) NOT NULL UNIQUE,
    floor         int         NOT NULL
);

CREATE TABLE bookings (
    id         bigserial   PRIMARY KEY,
    guest_id   bigint      NOT NULL REFERENCES guests(id),
    check_in   date        NOT NULL,
    check_out  date        NOT NULL,
    status     varchar(20) NOT NULL DEFAULT 'confirmed',
    CHECK (check_out > check_in)
);
COMMENT ON COLUMN bookings.status IS 'confirmed -> checked_in -> checked_out, or cancelled';

CREATE TABLE booking_rooms (
    booking_id  bigint        NOT NULL REFERENCES bookings(id),
    room_id     bigint        NOT NULL REFERENCES rooms(id),
    night_price numeric(10,2) NOT NULL,
    PRIMARY KEY (booking_id, room_id)
);
COMMENT ON COLUMN booking_rooms.night_price IS 'Frozen at booking time, like an order line price';

CREATE TABLE payments (
    id          bigserial     PRIMARY KEY,
    booking_id  bigint        NOT NULL REFERENCES bookings(id),
    amount      numeric(10,2) NOT NULL,
    paid_at     timestamptz   NOT NULL DEFAULT now(),
    method      varchar(20)   NOT NULL
);

FAQ

How do I query whether a room is free for given dates?

A room is free if no booking_rooms row joins to a non-canceled booking whose range overlaps the requested one: existing.check_in < wanted.check_out AND existing.check_out > wanted.check_in. Date-range overlap is the interview question hiding inside every hotel schema.

Where would seasonal pricing go?

A rate_calendar table: room_type_id, date range, price. base_price on room_types stays the fallback; the booking still freezes the resolved price into booking_rooms.

More example schemas