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


| ID | No.  | Type    | Price   | Beds | Hotel| Size  | TV          | Minibar | Bathroom | Images | Booked |
| ---|------|---------|---------|----- | ---  | ----  | --------    | -----   | ------   | -----  | -----  |
| 1  | 101  | Deluxe  | 5000.00 | 2    | A    | 31m^2 | 32inch(hdtv)| yes     | Private  |
| 2  | 102  | Economy | 4000.00 | 1    | A    | 25m^2 | 30inch(flat)| no      |
| 1  | 103  | Deluxe  | 5000.00 | 1    | A    | 31m^2 |             | yes     |
| 2  | 104  | Suite   | 9000.00 | 2    | A    | 50m^2 | 70inch(LED) | yes     |     

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?

closed as unclear what you're asking by mustaccio, joanolo, MDCCL, Michael Green, McNets Jun 25 '17 at 19:19

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    "should the booking time .. go .. in Booking table?" I think when you read that back to yourself out loud you'll know what the answer is. – Michael Green Jun 24 '17 at 11:08
  • 1
    Either change Size to SizeSquareMetres and remove the units from the value or add a new column called SizeUnits and put the m^2 bit in there. – Michael Green Jun 24 '17 at 11:11

Probably better to work with a metadata table. This, because the combination between different rooms and facilities can be quite hefty.

Have a look at this approach. I created an entity called Establishment so you can have Hotels, Hostels, B&B, etc under the same table, the difference comed from the type defined on your EstablishmentType. Specific details of addressing, for example, can be stored on another table that can be shared with the Individuals Entity (not represented in this example).

Anyway, test schema:

enter image description here

Note that I choose to store the datatype on the Facilities table, this is probably discouraged because its associated with the concept of the God Table, if all you want to do is present a table with the room facilities (frontend-wise) just forget about it.

This approach will allow you to have any number of facilities per room without crowding a single table with NULL values. The model allows, also, any number of Establishments and Rooms types. You could also add another table in between and give the option of separating rooms between different buildings, branches, etc.

Alternatively you could choose to store XML or JSON on the RoomFacilities and let the frontend do the magic and deserialize the content of that table.

    "facilities" : {
        "number" : "1"
        "beds" : "2",
        "tv" : "22inch",
        "size" : "20m^2"

About the images: You should probably keep them stored on a file server and reference them from within and additional media table.

  • 1
    I liked the idea of storing JSON but if I in future I need to do some data analytics, will it be useful? – CodeYogi Jun 24 '17 at 13:25

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