Database design : hotel room and its detail

Question

Right now i'm creating DB schema for a hotel. I stumbled upon on my hotel rooms table design. The requirement of hotel rooms would have data like : columns like room_type, room_size, room_number.

room_type can have values like economic, standard, deluxe

room_size can have values like single bed, double bed

My question is, what the best way of creating this tables? Right now i'm thinking of two ways of doing it, which is :

Type 1

Basically i create these tables :

rooms_master (room_no, room_type_code, room_size_code)
rooms_type (room_type_code, room_type_description)
rooms_size (room_size_code, room_size_description)

with rooms_type and rooms_size act as master reference table for rooms_master.
rooms_master will have FK (room_type_code, room_size_code) to PK in table rooms_type/size

or this one :

Type 2

Basically i combine two reference table (rooms_type and rooms_size) into one general table

rooms_master (room_no, room_type_code, room_size_code)
general_table (key, code, description) with column "key" and "code" act as primary keys

sample data would be like this :

|Room_No|Room_Type_Code|Room_Size_Code |
----------------------------------------
|100    |1             |1              |
|101    |2             |2              |


|key      |code|description|
----------------------------
|ROOM_TYPE|1   |Standard   |
|ROOM_TYPE|2   |Deluxe     |
|ROOM_SIZE|1   |Single Bed |
|ROOM_SIZE|2   |Double Bed |

I'm weighing the pro and cons for those two ways.

Type 1 :
Pro :
1. rooms_master will have a strong relationship with rooms_type and rooms_size
2. easier to query data
Cons :
1. too many reference table like this (or is it okay to have much tables like this ? 
   because sometimes i felt overwhelmed by a lot of reference tables like these)

Type 2
Pro :
1. A lot less table, because i can just put all reference data to a general table
   (not only rooms_type and rooms_size but others tables which only act as reference data)
Cons :
1. Harder to query, but it can be done (by query room size to general table,
   the room type and then join these two queries).
2. Doesn't have a strong relationship between rooms_master and general table. 
3. Prone to error, if the values in column "key" in general tables is maintained wrong. 

Right now i'm thinking toward to try using type 2, because much data in there, such as room type and room size aren't going to change (there will be no addition in near future).

So, please give me some insight of what ways should i chooes or are there any better ways of doing this ?

Answer 1

You basically answered the question already regarding the Type 2 approach: Looking at the cons of Type 2, you already stated that this solution would violate referential integrity and therewith a normalized database structure. So I don't recommend using it.

I'd start with analyzing the requirement: There are different rooms and different room descriptions. Different rooms may refer to the same description. So we have a 1..n relationship between descriptions and rooms. A single room does not only have a description. It might also have a status (e.g. occupied, booked, not available, ...) and a base price (depending on the requirement the base price may be part of the room description). A description of a room may have attributes like room size and room type, but also attributes like smoker/non-smoker, available for wheel-chairs y/n, dogs allowed y/n, ...

I'd probably go with the following schema: rooms (room_no, room_desc_code, room_status_code, ...) room_descriptions (room_desc_code, Room_Type, Room_Size,...)

Depending on the number of allowed values and the "expected change rate" I'd decide either to use a constraint or a lookup table. Especially for "Yes/No" columns (e.g. dogs allowed) I'd go for the constraint. The room_status_code would most probalby refer to a lookup table, since there are several allowed values and a "risk" of new values being required in the near future.