7

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.

Tables design


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.

  • 3
    This question would be off-topic on Code Review because it does not contain code (DDL in this context). It's on-topic here and Programmers (but please don't ask on both!) – Paul White Jan 4 '16 at 17:45
  • 1
    @rain kindly mention the definition for client and user. Is client is booking agent? – rathishDBA Jan 5 '16 at 6:05
13

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. Hotel room booking system

This model is a subset of Hotel Booking system. After the normalization process (check here), 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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.