Hotel reservation schema template

Tables in this data model

• hotels — 3 columns
• room_types — 4 columns
• rooms — 4 columns
• rate_plans — 4 columns
• guests — 4 columns
• reservations — 7 columns
• stays — 5 columns
• folio_charges — 4 columns

How to use this ER diagram template

1. Click Open in the editor. The ER diagram opens on a new page in FlowDiagram — nothing is uploaded and there is no sign-up.
2. Rename, add or delete shapes and drag them where you want; connectors stay attached as things move.
3. Drop in vendor logos from the library — 1,160 of them, including every AWS, Azure and Google Cloud service icon — or restyle it with the colour and theme controls.
4. Prefer text? Edit the SQL DDL below and paste it in through ☰ → Import / export SQL; the finished model exports back to DDL.
5. Export to PNG, SVG or PDF, or press Ctrl+L for a link that carries the whole diagram.

SQL DDL source

The whole template is the text below. Paste it into FlowDiagram, keep it in Git next to the code it describes, or hand it to an AI assistant to extend.

CREATE TABLE hotels (
  hotel_id INTEGER      NOT NULL PRIMARY KEY,
  name     VARCHAR(150) NOT NULL,
  city     VARCHAR(100) NOT NULL
);

CREATE TABLE room_types (
  room_type_id INTEGER     NOT NULL PRIMARY KEY,
  hotel_id     INTEGER     NOT NULL REFERENCES hotels(hotel_id),
  name         VARCHAR(60) NOT NULL,
  max_guests   INTEGER     NOT NULL
);

CREATE TABLE rooms (
  room_id      INTEGER     NOT NULL PRIMARY KEY,
  room_type_id INTEGER     NOT NULL REFERENCES room_types(room_type_id),
  room_number  VARCHAR(10) NOT NULL,
  status       VARCHAR(20) NOT NULL
);

CREATE TABLE rate_plans (
  rate_plan_id INTEGER       NOT NULL PRIMARY KEY,
  room_type_id INTEGER       NOT NULL REFERENCES room_types(room_type_id),
  stay_date    DATE          NOT NULL,
  nightly_rate DECIMAL(10,2) NOT NULL
);

CREATE TABLE guests (
  guest_id  INTEGER      NOT NULL PRIMARY KEY,
  full_name VARCHAR(150) NOT NULL,
  email     VARCHAR(255),
  loyalty_no VARCHAR(30)
);

CREATE TABLE reservations (
  reservation_id INTEGER     NOT NULL PRIMARY KEY,
  guest_id       INTEGER     NOT NULL REFERENCES guests(guest_id),
  room_type_id   INTEGER     NOT NULL REFERENCES room_types(room_type_id),
  check_in       DATE        NOT NULL,
  check_out      DATE        NOT NULL,
  status         VARCHAR(20) NOT NULL,
  channel        VARCHAR(30)
);

CREATE TABLE stays (
  stay_id        INTEGER   NOT NULL PRIMARY KEY,
  reservation_id INTEGER   NOT NULL REFERENCES reservations(reservation_id),
  room_id        INTEGER   NOT NULL REFERENCES rooms(room_id),
  checked_in_at  TIMESTAMP NOT NULL,
  checked_out_at TIMESTAMP
);

CREATE TABLE folio_charges (
  charge_id INTEGER       NOT NULL PRIMARY KEY,
  stay_id   INTEGER       NOT NULL REFERENCES stays(stay_id),
  category  VARCHAR(30)   NOT NULL,
  amount    DECIMAL(10,2) NOT NULL
);

Related: Database schema diagram tool · ER diagram from SQL · All database & er models templates · All 521 templates