Crowsfoot

Hotel booking database schema: ER diagram and SQL

Reservation systems separate what a room is (its type and price) from which physical room a guest gets. This schema keeps that split, which makes pricing and availability much easier to manage.

Open this schema in the editor Jump to the SQL
guests PK id bigint name varchar email varchar hotels PK id bigint name varchar city varchar room_types PK id bigint FK hotel_id bigint name varchar nightly_rate numeric capacity int rooms PK id bigint FK room_type_id bigint number varchar floor int bookings PK id bigint FK guest_id bigint FK room_id bigint check_in date check_out date status varchar

Tables in this schema

guests

People who make reservations.

hotels

Properties in the system, each in one city.

room_types

Kinds of room a hotel offers, such as “Double” or “Suite”, with a nightly rate and capacity.

rooms

Individual physical rooms. Each is of one type.

bookings

A guest staying in a specific room between two dates.

How the tables relate

  • room_types.hotel_id references hotels.id: one row in hotels can have many rows in room_types.
  • rooms.room_type_id references room_types.id: one row in room_types can have many rows in rooms.
  • bookings.guest_id references guests.id: one row in guests can have many rows in bookings.
  • bookings.room_id references rooms.id: one row in rooms can have many rows in bookings.

SQL (PostgreSQL)

Copy it into your database, or open it in Crowsfoot to edit the diagram, add notes, and export again.

CREATE TABLE guests (
  id bigint PRIMARY KEY,
  name varchar(120) NOT NULL,
  email varchar(255) NOT NULL
);

CREATE TABLE hotels (
  id bigint PRIMARY KEY,
  name varchar(160) NOT NULL,
  city varchar(80) NOT NULL
);

CREATE TABLE room_types (
  id bigint PRIMARY KEY,
  hotel_id bigint NOT NULL REFERENCES hotels (id),
  name varchar(80) NOT NULL,
  nightly_rate numeric(10,2) NOT NULL,
  capacity int NOT NULL
);

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

CREATE TABLE bookings (
  id bigint PRIMARY KEY,
  guest_id bigint NOT NULL REFERENCES guests (id),
  room_id bigint NOT NULL REFERENCES rooms (id),
  check_in date NOT NULL,
  check_out date NOT NULL,
  status varchar(20) NOT NULL DEFAULT 'confirmed'
);

Design notes

  • Pricing lives on room_types rather than rooms, so every “Double” shares one rate.
  • To prevent double-booking, PostgreSQL can enforce an exclusion constraint on room_id and the date range of check_in to check_out.
  • Splitting types from rooms lets you sell a type of room first and assign a specific room later.

More example schemas

New to ER diagrams? Read the crow’s foot notation guide.