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, and each booking holds one or more rooms between check-in and check-out. Rooms belong to room types, which carry the price. Availability is a query over bookings, not a column.

Below, ErdDocs draws this schema from its SQL, every table and every relation. The Data Dictionary tab has the printable reference, complete and free on this page. The SQL is further down.

Your schema

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
);

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
);

Your diagram appears here.

Hover a table to light up its relations, and click to pin them.

What each table does

guestsThe account, with contact details for the confirmation email.
room_types / roomsThe type carries capacity and price. The room is a numbered instance of a type: the books/copies split wearing a different hat.
bookingsThe date range and the lifecycle. The CHECK constraint outlaws zero-night stays.
booking_roomsA junction with a frozen night_price. Family trips book several rooms on one reservation.
paymentsDeposit, 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, so no query has to defend against them.

The SQL

PostgreSQL dialect. Paste it into the tool above (it is already there), into your own database, or into a migration. You are free to copy it.

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. The overlap test is existing.check_in < wanted.check_out AND existing.check_out > wanted.check_in.

Where would seasonal pricing go?

In a rate_calendar table with room_type_id, a date range and a price. base_price on room_types stays the fallback. The booking still freezes the resolved price into booking_rooms.

More example schemas

Banking System ER diagram
Inventory Management System ER diagram
All example schemas
Data dictionary generator: the documentation side of the same tool.