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

 

ER DIAGRAM

First, here is the entire ER diagram and a quick overview of the system.

 

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.

 

 

TABLE DETAILS

Now that we are done with the overview, let us get into more details on the entities or tables.

 

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”.

 

EXTRA BITS & LINKS

That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.

 

LINKS & REFERENCES

 

 

INFOGRAPHIC CHEAT SHEET

Hotel Reservation System ERD (click to enlarge)

 

THE END

Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

Leave a Comment

Your email address will not be published. Required fields are marked *