4

I tried to give it a chance without thinking much and this is what I came up with:

Room
----

| ID | No.  | Type    | Price   | Beds | Hotel| Size  | Minibar | Images | Booked |
| ---|------|---------|---------|----- | ---  | ----  | -----   | -----  | -----  |
| 1  | 101  | Deluxe  | 5000.00 | 2    | A    | 31m^2 | yes     |
| 2  | 102  | Economy | 4000.00 | 1    | A    | 25m^2 | no      
| 1  | 103  | Deluxe  | 5000.00 | 1    | A    | 31m^2 | yes     
| 2  | 104  | Suite   | 9000.00 | 2    | A    | 50m^2 | yes  

Room continued

| ID | TV            | Bathroom 
| ---| --------      | ------   
| 1  | 32inch(hdtv)  | Private  
| 2  | 30inch(flat)  |
| 1  |               |
| 2  | 70inch(LED)   |

    I see that there are many facilities which belongs to a room and I tried to create a column for each but it started looking hairy. Also, Hotel column will be a many2one field as well as Images column too.

I know that the booking information shouldn't belong to Room instead it should go to Booking table. I am thinking where should the booking time and checkout time go? in Booking table?

Apart from these information, what other information do I need to persist?

Update

While designing persistence layer I observed that the problem is same as in programming I need to keep a good balance between tables such that I maintain low coupling and high cohesion.

Use Case

There is a merchant which can have many hotels probably at many locations. Given a user it can check availability of the rooms at any given hotel and should be able to book a room for any particular time frame.

|improve this question
  • 2
    That really depends on which problem you are trying to solve. Do you want to help a guest find a good room? Perform the actual booking? Create historical statistics, etc? You shouldn't attempt to make models model reality, but serve as an aid in the problem you need to solve. – Pete Jun 23 '17 at 11:31
  • @Pete I agree but there are some basic rules which is same, for starting yes I just want to add rooms only. Booking is allowed but not now. – CodeYogi Jun 23 '17 at 11:33
  • Does price is fixed all year round (maybe price column should be in other table?)? Are there rooms with extra features maybe features should be separate so you can easily add them? Images need to have separate table too so you can add variable amount of them (you can keep thumbnail in room table). Booked needs to be in other table. – Volvox Jun 23 '17 at 11:56
  • @Volvox no, price changes a lot. Could you explain more about features? I also thinking to add a description column which could contain some general information or special features, this column would be HTML formatted. Good suggestion regarding thumbnail. – CodeYogi Jun 23 '17 at 11:59
  • You should not be designing the database before the domain layer – TheCatWhisperer Jun 23 '17 at 14:15
8

Your design is not normalized, meaning you will have a lot of problems with it. I will not explain here why normal forms are important in ralational databases.

Pricing and booking are sub-systems in themselves with their own complexity. I will give you a naïve solution for brevity. This answer gives you some things to consider for the pricing sub-system.

Also, you don't have to include the unit inside the datum for the column "SIZE", i.e., you insert the number 31 and not the string "31m^2". The unit can be a convention, it can be indicated on the column name or it can point to a UNITS table.

A PNG is worth 1024 words:

This is a conceptual ER diagram, meaning it doesn't have all the details. It is assumed that every table has its own ID and that join tables have the IDs of their parents.

enter image description here

This is another diagram I made for a course with booking of services, rooms and recording the actual stay of customers:

enter image description here

And regarding whether or not database modeling is a part of software modeling, this answer gives some insight.

|improve this answer
  • Suppose I know my business needs then how to decide how many tables we need, I see on you second example you are used some tables which I may have never thought of like Stay etc – CodeYogi Jun 26 '17 at 14:58
  • @CodeYogi You don't decide how many tables you will need. Tables are the result of the process of modeling. You just simply adhere to at least 3NF. That and your business needs will yield the tables you need. – Tulains Córdova Jun 26 '17 at 15:05
  • 1
    It warms my heart to see people giving the database the respect it deserves. Great answer. – jleach Jun 27 '17 at 17:34

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.