DiagramDB / Hotel Management System ERD

ER Diagram for Hotel Management System

Guests book reservations for one or more rooms, rooms have a type that sets the nightly rate, stays generate charges and payments. Booking several rooms on one reservation is the rule most first drafts miss.

Drawing 7 tables…

Why each relationship is drawn this way

reservations ↔ roomsA family can book two rooms on one reservation and a room is booked many times, so reservation_rooms is a junction table that also freezes the rate agreed at booking time.
room_type → roomsThe type sets capacity and the standard rate; rooms only store what is unique to them.
reservation → charges / paymentsMinibar, room service and the room itself become charge rows; the folio balance is charges minus payments.

The schema

CREATE TABLE room_types (
  id INT PRIMARY KEY,
  name VARCHAR(40) NOT NULL UNIQUE,
  max_guests INT NOT NULL,
  nightly_rate_cents INT NOT NULL
);
CREATE TABLE rooms (
  id INT PRIMARY KEY,
  room_type_id INT NOT NULL REFERENCES room_types(id),
  number VARCHAR(10) NOT NULL UNIQUE,
  floor INT NOT NULL
);
CREATE TABLE guests (
  id INT PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(255),
  phone VARCHAR(30)
);
CREATE TABLE reservations (
  id INT PRIMARY KEY,
  guest_id INT NOT NULL REFERENCES guests(id),
  check_in DATE NOT NULL,
  check_out DATE NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at TIMESTAMP NOT NULL
);
CREATE TABLE reservation_rooms (
  reservation_id INT NOT NULL,
  room_id INT NOT NULL,
  rate_cents INT NOT NULL,
  PRIMARY KEY (reservation_id, room_id),
  FOREIGN KEY (reservation_id) REFERENCES reservations(id),
  FOREIGN KEY (room_id) REFERENCES rooms(id)
);
CREATE TABLE charges (
  id INT PRIMARY KEY,
  reservation_id INT NOT NULL REFERENCES reservations(id),
  description VARCHAR(120) NOT NULL,
  amount_cents INT NOT NULL,
  charged_at TIMESTAMP NOT NULL
);
CREATE TABLE payments (
  id INT PRIMARY KEY,
  reservation_id INT NOT NULL REFERENCES reservations(id),
  amount_cents INT NOT NULL,
  method VARCHAR(20) NOT NULL,
  paid_at TIMESTAMP NOT NULL
);

Design notes

Availability for a date range: rooms with no reservation_rooms row whose reservation overlaps (check_in < :out AND check_out > :in).
Copy the rate onto the booking: changing room_types later must not change past invoices.

Tables at a glance

room_types: id, name, max_guests, nightly_rate_cents
rooms: id, room_type_id, number, floor
guests: id, full_name, email, phone
reservations: id, guest_id, check_in, check_out, status, created_at
reservation_rooms: reservation_id, room_id, rate_cents
charges: id, reservation_id, description, amount_cents, charged_at
payments: id, reservation_id, amount_cents, method, paid_at

Questions

What are the entities in a hotel management system ER diagram?

This design uses 7 tables: room_types, rooms, guests, reservations, reservation_rooms, charges, payments. A family can book two rooms on one reservation and a room is booked many times, so reservation_rooms is a junction table that also freezes the rate agreed at booking time.

How many relationships does the hotel management system ER diagram have?

6 foreign-key relationships. The many-to-many ones are resolved with junction tables, which is how they must be stored in a relational database.

Can I edit this ER diagram and export it?

Yes. “Edit in the ER diagram generator” opens the same SQL in the editor, where you can add tables, drag boxes and export SVG, PNG, Mermaid, DBML or SQL for PostgreSQL, MySQL, SQL Server or SQLite.

Which notation is used?

Crow’s foot: a fork means many, a bar means exactly one and a circle means optional (zero). A nullable foreign key gets the circle on the parent end.