Hotel Reservation System ERD (Simple Version)

Welcome to a quick sharing of an ERD of a hotel reservation system. Need to build a reservation system for a hotel business? Well, it doesn’t have to be complicated – Here is a simple version, hope it helps to speed up your design process. Read on!

 

 

TABLE OF CONTENTS

 

 

HOTEL RESERVATION SYSTEM

That’s right, 5 tables should be enough to drive an entire reservation system.

• Users – Users of the system. Customers, managers, staff, etc…
• Rooms – Captain Obvious to the rescue, the rooms that are available for reservation.
• Reservation – Which rooms are reserved for which periods of time.

 

FOR REFERENCE ONLY

Before the trolls scream “this diagram is not accurate” and “this is stupid” – Here’s a quick disclaimer before we go any further. This ERD is only for reference and it is highly subjective.

For example – Some people may have more than one location. In this case, feel free to add a “building” field to the rooms and reservations tables. You are building your own system now, you decide what works for you.

 

 

 

PART 1) USERS TABLE

COLUMN	DATA TYPE	NOTES
user_id	INTEGER	Primary key.
user_name	VARCHAR	User’s full name.
user_email	VARCHAR	Unique, user’s email.
user_tel	VARCHAR	User’s telephone number.
user_password	VARCHAR	Password. Encrypt in your own system.
user_role	VARCHAR	User’s role. Invent your own – “C”ustomer, “M”anager, “S”taff.

Some people prefer to create multiple tables for different roles. I question that decision, wouldn’t a single user_role field do the trick? So unless it is absolutely necessary, or all the different “types” of users have wildly different fields – Keep things simple, and keep all the users in a single table.

 

PART 2) ROOMS TABLE

COLUMN	DATA TYPE	NOTES
room_id	VARCHAR	Primary key. Room number.
room_type	VARCHAR	Invent your own. Could be “S”ingle, “D”ouble, “T”win, “S”uite, etc…
room_price	FLOAT	Price to reserve this room.

This table should be pretty self-explanatory, but a couple of notes:

• The room types can be tricky. That is, a double room can also be reconfigured into a twin. Larger single rooms can also be reconfigured as twins/doubles if required.
• You may want to add more “flags” for options such as “extra bed” and “reconfiguration” to this table.
• As for the room price – It can be hourly, AM/PM, daily, or even periodic packages… It may make sense to keep an entirely separate table to deal with this.

 

 

PART 3) RESERVATIONS

3A) RESERVATIONS TABLE

COLUMN	DATA TYPE	NOTES
resv_id	INTEGER	Primary Key.
room_id	INTEGER	Foreign Key.
resv_start resv_end	DATE TIME	Reservation start and end date/time.
resv_status	VARCHAR	The reservation status, up to you to decide. For example – “P”ending, “R”eserved, “C”anceled.
user_id	INTEGER	Foreign key.
user_name user_email user_tel	VARCHAR	Customer’s name, email, telephone.
bill_name bill_email bill_tel	VARCHAR	“Bill to”.

The purpose of this table is obvious… But to answer the question marks that some of you sharp system ninjas have:

• There are 2 sets of name/email/tel for a good reason. Example – John booked multiple rooms for his company. The customer will be John, but it will be billed to his company instead.
• “Duplicating” the user name/email/tel is not “redundancy”. Consider this:
  • Joker made a reservation. The reservations table only recorded his user ID.
  • He did not complete the payment and changed his account name/email/telephone to a complete stranger as a prank. There is no way to trace Joker.
  • Saving the name/email/tel at the time of booking is “integrity”, not “redundancy”.
• You can also consider using this table for “internal use”. E.G. An internal “reservation” for room maintenance.

 

 

3B) RESERVATION HISTORY TABLE

COLUMN	DATA TYPE	NOTES
resv_id	INTEGER	Primary and foreign key.
history_date	DATE TIME	Primary Key. Time of entry.
history_status	VARCHAR	Reservation status.
history_notes	VARCHAR	Notes, if any.

To keep track of what happened at each stage of the reservation. You may also want to add the user ID to this table.

 

3C) RESERVATION TOTALS

COLUMN	DATA TYPE	NOTES
total_id	INTEGER	Primary key.
resv_id	INTEGER	Foreign key.
total_title	VARCHAR	Total name/title.
total_amt	FLOAT	Amount.

Lastly, we have a table to store the total amounts. Even though it is possible to calculate the total amount from the start/end time, we cannot expect every reservation to be the same. That is – Promotions, discounts, rate changes, and all kinds of things can happen over time. It is best to keep the totals “at the time of checkout”.

 

 

 

LINKS & REFERENCES

• Simple Hotel Booking Management PHP MYSQL – Code Boxx
• Entity-Relationship Diagram Symbols and Notation – Lucid Chart