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
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.
Now that we are done with the overview, let us get into more details on the entities or tables.
PART 1) USERS TABLE
|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
|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
|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.|
|VARCHAR||Customer’s name, email, telephone.|
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
|resv_id||INTEGER||Primary and foreign key.|
|history_date||DATE TIME||Primary Key. Time of entry.|
|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
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
- Simple Hotel Booking Management PHP MYSQL – Code Boxx
- Entity-Relationship Diagram Symbols and Notation – Lucid Chart
INFOGRAPHIC CHEAT SHEET
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!