0

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 ?

  • A table of code tables (your Type 2) is generally considered to be an anti-pattern. You should probably avoid it. If you are concerned about the number of tables in your database you might use something like schemas to allow you to focus on an area of interest while "ignoring" the other areas in your database. Alternatively, you could (and probably should) just come to terms with your aversion to many tables. Any real world business system will have lots of tables. – Joel Brown Sep 28 '14 at 11:51
  • well yeah, i guess i shall have to "ignore" most of these tables. Thanks for pointing out to me. – ayam_bakar Sep 28 '14 at 13:20
0

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.

  • It looks to me like you are suggesting blending room type and room size? Or are you creating a three tier structure: room -> room_description -> room_type (and other lookup tables, such as room_size)? If the former, that would not be third normal form. If the latter, why introduce a middle tier? What non-key attribute do you expect to depend on a description code? – Joel Brown Sep 28 '14 at 11:48
  • Care to explain why room type and room size as columns of a room descriptions would violate 3NF? In plain theory, lookup tables for each column of this table would be required (as I mentioned already). But I've rarely seen a system where allowed values like Yes/No would be stored in a separate lookup table. Hence my recommendation to use constraints. Stressing the definition of a lookup table I'd argue a constraint is an "embedded lookup table, with a single column, the primary key" ;-) Following your logic it wouldn't be allowed to store first name and last name in a persons table either... – Lmu92 Sep 28 '14 at 12:54
  • I see. Thanks for your explanation. I just hesitate for a moment and thinking that too much lookup tables are considered back practice. And also thanks for giving the example, i think i'm gonna to implement the scheme you mentioned above – ayam_bakar Sep 28 '14 at 13:17
  • Room type and room size on a room description table might violate 3NF if the type and size depend on the room, not on the description of the room. You don't state your assumptions about dependencies, you just split a bunch of properties of rooms off from the rooms table in a way that might be creating transitive dependencies. Your inference of my logic is in exactly the opposite direction. – Joel Brown Sep 28 '14 at 16:06

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.