Database design of Room Booking system in a Hotel

Question

Intro and System Description

---

I am currently designing a Database for a Room Booking system in a Hotel.

Client fills a Form / Request with following information about Room:

• amount of people in room
• rating of a room
• check in and check out date

Administrator has a dashboard with list of Forms from Clients, he will assign each Room for every Client manually. After this User gets a Bill.

Database Design

---

This is actually a Sketch of my DB. I'll have following tables:

• Users
• Forms
• Rooms
• Bills

I am not considering table Passwords which contains Password's Hashes.

Questions

---

I would like to hear from you what is missing in my DB Design, what do you think about overall logic and correctness of relations between tables.

Answer 1

Without knowing exact and whole requirements we cannot design a complete data model. Let us assume the following business rules, it is based on your question.

• Each Customer can request for one or more Room.
• Each Room can be requested by one Customer
• Each Admin can assign one or many Room.
• Each Room must be assigned by one Admin.
• Each Room must be belongs to one Room Type.
• Each Room Type can contain one or many Room.
• Each Booking must be belongs to one Date.
• Each Date can contain one or more Booking.
• Each Room Type can contain one or many Rent.
• Each Rent must belongs to one Room Type.
• Each Booking must include one or many Room.
• Each Room can be included in one Booking.
• Each Customer can hold one or many Booking.
• Each Booking must belongs to one Customer.
• Each Rent can be included in one or many Bill.
• Each Bill must include one Rent.
• Each Bill Payment must include one Payment Type.
• Each Payment Type can contain one or many Bill Payment.
• Each Date can contain one or many Bill Payment.
• Each Bill Payment must belongs to one Date.
• Each Room can be rated one or many Room Rating.
• Each Room Rating must belongs to one Room.
• Each Rating can contain one or many Room Rating.
• Each Room Rating must belongs to one Rating.

Based on the above rules we have designed the following data model.

This model is a subset of Hotel Booking system. After the normalization process (check my blog), you will have more detailed and complete model.

In the above model Room rent is calculated based on the room type such as Single Room, Doubles Room, Family Room or Meeting Hall rather than number of people in the room.

And Room rent is changed from time to time, so we have included the from date and to date to have history of room rents. Also customer can use many payment types such as credit card, debit card, cash payment, so we are included payment type entity.

This model is based on assumption. I hope this model will help you in someway.

Thank you.

Answer 2

Take the guess work out of your queries and design by using descriptive names and titles. Rooms has People but I would assume this is actually something like Capacity or Room Size. This avoids confusion with the already used People column in Forms. Of course this could also be improved... something like NumberOfGuests would likely be more informative to you now, and anyone querying your database later. I'd double check all column and table names to ensure they are concise and clear.

Rooms doesn't have an auto-increment, likely because you expect to use integers to identify rooms. This may work, but you could avoid problems by separating your business/user room identification from your database logic. When a grisly murder occurs in room 13 rendering it inoperable and the new manager, not in the least superstitious or concerned for such matters, turns that broom closet on floor 14 to room 13, because it's big enough and frankly our hotel's quality is going down.... you're screwed. With a separate DB key from your room designation you can maintain all records and DB integrity along with the fact that there was a major change (what other kinds of change tracking and details should we support?). The Room 13 | RoomPK 13 historic records remain intact while new records for what the staff knows as Room 13 but is actually now Room 13 | RoomPK 132 can also be recorded. It matters when someone tries to sue you when they stayed in room 13 and the entire case depends on which room 13 they stayed in. Sorry, your hotel has a lot of problems.

The final issue is where are the transactions? I would expect that a Form needs to also have FKs to Bill(s?) and Rooms(s?). People need to check in and out from somewhere and we need to charge them specifically for that occurrence and association. A family may get two rooms for two days then check out in the morning and back in again in the evening. They are then adamant that they only receive a single bill... Or someone staying two nights requires two bills due to two different payment methods (credit card, cash, work, personal). You probably need lookup tables now to support the intricacies of billing and booking. People split bills, switch rooms, and hopefully return again - all of which you need to support.

Answer 3

We’ll focus on the following set of requirements while designing the Hotel Management System:

The system should support the booking of different room types like standard, deluxe, family suite, etc.

Guests should be able to search the room inventory and book any available room.

The system should be able to retrieve information, such as who booked a particular room, or what rooms were booked by a specific customer.

The system should allow customers to cancel their booking and provide them with a full refund if the cancellation occurs before 24 hours of the check-in date.

The system should be able to send notifications whenever the booking is nearing the check-in or check-out date.

The system should maintain a room housekeeping log to keep track of all housekeeping tasks.

Any customer should be able to add room services and food items. Customers can ask for different amenities. The customers should be able to pay their bills through credit card, check or cash.

Here is a class diagram for hotel booking system. Hope this helps.