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 :
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 :
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 ?