Database Design – Hotel Room and Details Schema

database-design

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 ?

Best Answer

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.

Related Solutions

MySQL Database Design – Clarification for Database Creation and Relations

Just for a start:

Usually, this should become clear from normalizing the data. You need a separate table for each entity. And you require an intermediate table for many to many (m:n) relations.

With hotel and room/reservation, it's unlikely that a specific room/reservation relates to more than a single hotel. For meals, you need to decide, what exactly defines one. If all you care is, e.g., whether or not cheese sandwiches are served in some particular hotel, it'd look like m:n. You will need to ask you(r domain expert), what will be true for the lifetime of the system, you are working on.

Foreign keys will be needed, no matter, whether you have intermediate tables or not - the difference is, where they are stored.

Looking at your current schema: Two things come to mind. Either you are storing optional attributes in the intermediate tables - and you take the shortcut, if no optional attribute is filled in. Or you do actually have multiple relations between these entities. You would need to check your data to differentiate between the two. I'd usually vote for consistency - thus using one approach for storing a particular type of fact.

Update regarding "hotel_meal": With all this detail in the meal record, I'd doubt one dish to be offered in more than one hotel (unless you have hotel chains in your system, or several hotels' kitchen is run by the same caterer).
You could check:

SELECT
  meal_id
  , COUNT(hotels_id)
FROM meal_hotel
GROUP BY meal_id
HAVING COUNT(COUNT(hotels_id)) > 1;

If it returns any record, you have a reason for an intermediate table - if you decide the records returned do actually make sense in your domain (hotel chains, same caterer - you should know it).

Update II regarding "hotel_meal": If no meal is ever served in more than a single hotel, you should not need the additional table; just have the hotel_id as foreign key in the meal table. Just as with rooms and reservations…
To list all meals for a particular hotel:

SELECT * FROM meals WHERE hotel_id=1;

Feel free to come back with more detail.

Database Design – Scalable Relational Database Design

So you're going to want to store your images on a fast CDN like Cloudfront. Normally I recommend storing pictures in-database, but not 240M of them :)

I'd avoid storing the whole URL, as it's likely to be repetitive. Just store the file name (a five-char case-sensitive name using a-z 0-9 would give you ~900M combinations. If your CDN is case-insensitive wrt URLs then use 6 chars ).

Are there really going to be 240M unique images, or can they be "shared" among entries? Do you expect every user to fill every entry with the max # of images?

You'd want some tables like this:

create table users (
  user_id serial primary key,
  ...
);

create table entries (
  entry_id serial primary key,
  user_id int not null references users(user_id), --index me
  description text,
  ...
);


create table pictures (
  file_name char(5) primary key,
  entry_id int not null references entries(entry_id) --index me
);

or perhaps:

create table pictures (
  picture_id serial primary key,
  file_name char(5) not null unique,
  entry_id int not null references entries(entry_id) -- index me
);

Use connection pooling!