Designing hotel room persistence layer

Question

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.

Answer 1

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.

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

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