Designing a Hotel Management System

Designing a Hotel Management System

This case study walks through modelling a Hotel Management System (HMS) end to end: first as an Entity-Relationship (ER) design, then mapped to a normalised relational schema. The goal is to show why each table looks the way it does, not just the final answer.

The system must support six core capabilities:

• Guest management — guest details, contact information, and identification.
• Room management — room availability, type, and the department that maintains it.
• Reservation management — bookings that may span multiple rooms, plus check-in/check-out tracking.
• Billing and payment — one or more payments per reservation.
• Staff management — staff records and their owning department.
• Service management — extra services (dining, spa) that guests consume during a stay.

Step 1 — Identify the entities

Entities are the nouns the system stores data about. For the HMS we have eight:

1. Guest — a person who books and stays.
2. Room — a physical room.
3. Room_Type — the category of a room (Single, Double, Suite) and its rate.
4. Reservation — a booking made by a guest.
5. Payment — money received against a reservation.
6. Service — an extra a guest can buy.
7. Staff — an employee.
8. Department — an organisational unit that owns rooms and staff.

Step 2 — Attach attributes

Each entity gets attributes, with primary keys (PK) and foreign keys (FK) called out.

Note that Phone_Number is multi-valued for both Guest and Staff, so it will become its own table when we map to a schema rather than living as a single column.

Step 3 — Define the relationships

Seven relationships connect the eight entities. The cardinality on each end drives how the schema is built.

The two M : N relationships — Reservation–Room and Guest–Service — cannot be drawn as a single foreign key. Each needs a junction entity that holds the pair of keys plus any data that belongs to the pairing itself (the room rate applied, the quantity of a service).

The complete ER diagram

Putting the entities, junctions, and relationships together gives the full picture below. Crow's-foot markers (legend above) show each end's cardinality; the amber boxes are the junction entities that resolve the two many-to-many relationships.

Mapping to a relational schema — with a normalisation trace

Rather than jump straight to the final tables, let us watch one wide, un-normalised booking record march through 1NF, 2NF, and 3NF. This is where the design earns its keep.

The starting point (un-normalised)

Imagine the booking desk recorded everything about a stay in one flat row, including every room on the reservation and the guest's phone numbers:

Reservation(
  Resv_ID, Guest_ID, Guest_Phones[],
  Room_No, Room_Type, Type_Price, Rate_Applied,
  Check_In, Check_Out
)

Two problems are visible immediately: Guest_Phones is a repeating group, and a single reservation row tries to hold several rooms at once.

1NF — remove repeating groups, make every cell atomic

We give each (reservation, room) its own row and lift the multi-valued phone numbers into a separate table. The reservation–room pairing now needs a composite key (Resv_ID, Room_No) because one reservation can list several rooms:

ResvRoom(
  Resv_ID, Room_No,            -- composite PK
  Guest_ID,
  Room_Type, Type_Price,
  Rate_Applied,
  Check_In, Check_Out
)
Guest_Phone( Guest_ID, Phone_Number )   -- repeating group removed

2NF — remove partial dependencies on the composite key

2NF asks: does every non-key attribute depend on the whole composite key (Resv_ID, Room_No), or only on part of it? Walking the columns:

• Guest_ID, Check_In, Check_Out depend on Resv_ID alone — a partial dependency on the first half of the key.
• Room_Type depends on Room_No alone — the room number alone fixes which type the room is, so this is a partial dependency on the second half of the key. (This is the subtle one: Room_Type is determined by Room_No, not by the pair.)
• Rate_Applied genuinely depends on the full pair — the rate negotiated for that room on that reservation — so it stays.

Splitting out each partial dependency to the part of the key it actually depends on:

Reservation( Resv_ID PK, Guest_ID, Check_In, Check_Out )
Room( Room_No PK, Room_Type, Type_Price )
ResvRoom( Resv_ID, Room_No, Rate_Applied,  PK(Resv_ID, Room_No) )

Note that Room_Type and Type_Price moved to a Room table at this step, because pulling them out is a 2NF action (a partial dependency on Room_No), not a 3NF one. Folding them into the later 3NF step would blur the boundary.

3NF — remove transitive dependencies

Now each table has a single-attribute key, so partial dependencies are gone. 3NF asks whether any non-key attribute depends on another non-key attribute. Look at Room:

Room_No → Room_Type → Type_Price

Type_Price does not depend on the room directly; it depends on the room's type. That is a transitive dependency: a non-key attribute (Type_Price) hanging off another non-key attribute (Room_Type). We break it by promoting type to its own table:

Room( Room_No PK, Room_Type_ID FK )
Room_Type( Room_Type_ID PK, Type_Name, Type_Price )

Now the room number determines only which type a room is; the price lives once, with the type. Change a suite's nightly rate in one place and every suite reflects it.

The 2NF/3NF distinction in one line: 2NF removed Room_Type because it depended on part of the composite key (Room_No); 3NF then removed Type_Price because it depended on another non-key column (Room_Type). Two different defects, fixed at two different steps.

The final schema

Applying the same reasoning across every entity and resolving both M:N relationships with junction tables yields these tables. Primary keys are bold; foreign keys are marked (FK).

The two amber junction tables from the ER diagram — Reservation_Room and Guest_Service — are exactly the tables that carry the M:N pairings, each with a composite primary key built from the keys it joins.

SQL for the load-bearing tables

The junction tables are where the design is easiest to get wrong, so here is the DDL for both, plus the two tables whose split was driven by the normalisation trace above.

CREATE TABLE Room_Type (
    Room_Type_ID    INT PRIMARY KEY,
    Type_Name       VARCHAR(20),
    Description     TEXT,
    Price_Per_Night DECIMAL(10,2),
    Max_Occupancy   INT
);

CREATE TABLE Room (
    Room_ID       INT PRIMARY KEY,
    Room_Number   VARCHAR(10),
    Floor         INT,
    Status        VARCHAR(20),
    Room_Type_ID  INT,
    Department_ID INT,
    FOREIGN KEY (Room_Type_ID)  REFERENCES Room_Type(Room_Type_ID),
    FOREIGN KEY (Department_ID) REFERENCES Department(Department_ID)
);

CREATE TABLE Reservation_Room (
    Reservation_ID INT,
    Room_ID        INT,
    Rate_Applied   DECIMAL(10,2),
    Notes          TEXT,
    PRIMARY KEY (Reservation_ID, Room_ID),
    FOREIGN KEY (Reservation_ID) REFERENCES Reservation(Reservation_ID),
    FOREIGN KEY (Room_ID)        REFERENCES Room(Room_ID)
);

CREATE TABLE Guest_Service (
    Guest_ID       INT,
    Service_ID     INT,
    Reservation_ID INT,
    Quantity       INT,
    Total_Cost     DECIMAL(10,2),
    PRIMARY KEY (Guest_ID, Service_ID, Reservation_ID),
    FOREIGN KEY (Guest_ID)       REFERENCES Guest(Guest_ID),
    FOREIGN KEY (Service_ID)     REFERENCES Service(Service_ID),
    FOREIGN KEY (Reservation_ID) REFERENCES Reservation(Reservation_ID)
);

Source

Adapted from the “Designing a Hotel Management System” normalisation case study, with the ER diagram redrawn in crow's-foot notation and an explicit 1NF→2NF→3NF trace added to separate the partial-dependency (2NF) and transitive-dependency (3NF) steps on the room attributes.