Hotel Booking ER Diagram

A booking database separates what is sold from what is bookable: room types describe the offer (double, suite…), rooms are the physical units, and bookings connect a guest to a room for a date range with a price snapshot.

The schema separates offer from inventory. room_types is what is sold — a name, a capacity, a base_price — while rooms are physical units with a room_number and a floor. One type maps to many rooms, which is why amenities attach to the type through room_type_amenities rather than to each room: you describe a category of room once, not once per door. base_price lives on the type because pricing is per room category, and its composite-join pattern is identical to the tag tables in the blog and e-commerce examples.

bookings points directly at a room_id, with check_in and check_out dates and a total_price snapshotted at booking time, so repricing the room type never rewrites a confirmed reservation. payments is one-to-one with bookings via the UNIQUE on booking_id, and paid_at is nullable, leaving room for a payment row that exists before the charge settles — the same pattern the e-commerce and SaaS examples use. Guests are modeled separately from any user account system: guests needs only an email, a name and a phone.

Nothing in this schema prevents two confirmed bookings for the same room on overlapping dates — the database has no way to know that check_in and check_out describe a range. Production systems enforce it either with a PostgreSQL exclusion constraint on daterange, or by checking overlaps inside the booking transaction. The other subtlety: bookings.room_id has no ON DELETE rule, so a room with any booking history cannot be removed, and rooms cascading from room_types means deleting a type fails while its rooms still hold history.

Rate plans come first: a rate_plans table (room_type_id, date range, nightly price) replaces the flat base_price the moment weekends cost more than weekdays. booking_guests is the second — bookings records who pays, but hotels need every occupant's name at check-in. Room blocks (out of order for maintenance, held for a group) are a small table on rooms with a date range and a reason, and they slot directly into the overlap check described above.

The diagram

1:N 1:N 1:N 1:N 1:1 N:M hotels PKid integer name varchar(150) city varchar(80) country varchar(60) star_rating integer amenities PKid integer Ulabel varchar(60) room_types PKid integer FKhotel_id integer name varchar(80) capacity integer base_price decimal(10,2) room_type_amenities PKroom_type_id integer PKamenity_id integer rooms PKid integer FKroom_type_id integer room_number varchar(10) floor integer guests PKid integer Uemail varchar(255) full_name varchar(120) phone varchar(30) bookings PKid integer FKroom_id integer FKguest_id integer check_in date check_out date total_price decimal(10,2) status varchar(20) created_at timestamp payments PKid integer FKbooking_id integer amount decimal(10,2) method varchar(30) paid_at timestamp

Tables (8)

hotels

  • idintegerPK
  • namevarchar(150)
  • cityvarchar(80)
  • countryvarchar(60)
  • star_ratinginteger

amenities

  • idintegerPK
  • labelvarchar(60)U

room_types

  • idintegerPK
  • hotel_idintegerFK
  • namevarchar(80)
  • capacityinteger
  • base_pricedecimal(10,2)

room_type_amenities

  • room_type_idintegerPK
  • amenity_idintegerPK

rooms

  • idintegerPK
  • room_type_idintegerFK
  • room_numbervarchar(10)
  • floorinteger

guests

  • idintegerPK
  • emailvarchar(255)U
  • full_namevarchar(120)
  • phonevarchar(30)

bookings

  • idintegerPK
  • room_idintegerFK
  • guest_idintegerFK
  • check_indate
  • check_outdate
  • total_pricedecimal(10,2)
  • statusvarchar(20)
  • created_attimestamp

payments

  • idintegerPK
  • booking_idintegerFKU
  • amountdecimal(10,2)
  • methodvarchar(30)
  • paid_attimestamp

Relationships (6)

  • 1:Nhotels.id→room_types.hotel_idON DELETE CASCADE
  • 1:Nroom_types.id→rooms.room_type_idON DELETE CASCADE
  • 1:Nrooms.id→bookings.room_id
  • 1:Nguests.id→bookings.guest_id
  • 1:1bookings.id→payments.booking_idON DELETE CASCADE
  • N:Mroom_types.id→amenities.id

The SQL

Copy this, paste it in the SQL tab, or just open it below.
SQL
CREATE TABLE hotels (
  id SERIAL PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  city VARCHAR(80) NOT NULL,
  country VARCHAR(60) NOT NULL,
  star_rating INTEGER
);

CREATE TABLE amenities (
  id SERIAL PRIMARY KEY,
  label VARCHAR(60) NOT NULL UNIQUE
);

CREATE TABLE room_types (
  id SERIAL PRIMARY KEY,
  hotel_id INTEGER NOT NULL REFERENCES hotels(id) ON DELETE CASCADE,
  name VARCHAR(80) NOT NULL,
  capacity INTEGER NOT NULL DEFAULT 2,
  base_price DECIMAL(10,2) NOT NULL
);

CREATE TABLE room_type_amenities (
  room_type_id INTEGER NOT NULL REFERENCES room_types(id) ON DELETE CASCADE,
  amenity_id INTEGER NOT NULL REFERENCES amenities(id) ON DELETE CASCADE,
  PRIMARY KEY (room_type_id, amenity_id)
);

CREATE TABLE rooms (
  id SERIAL PRIMARY KEY,
  room_type_id INTEGER NOT NULL REFERENCES room_types(id) ON DELETE CASCADE,
  room_number VARCHAR(10) NOT NULL,
  floor INTEGER
);

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

CREATE TABLE bookings (
  id SERIAL PRIMARY KEY,
  room_id INTEGER NOT NULL REFERENCES rooms(id),
  guest_id INTEGER NOT NULL REFERENCES guests(id),
  check_in DATE NOT NULL,
  check_out DATE NOT NULL,
  total_price DECIMAL(10,2) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'confirmed',
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE payments (
  id SERIAL PRIMARY KEY,
  booking_id INTEGER NOT NULL UNIQUE REFERENCES bookings(id) ON DELETE CASCADE,
  amount DECIMAL(10,2) NOT NULL,
  method VARCHAR(30) NOT NULL,
  paid_at TIMESTAMP
);

Keep learning