Hotel booking database design

All we need is an easy explanation of the problem, so here it is.

I am building an online travel agency and I have a concern with the database design. Suppose users will be of two types:

  1. Standard (users looking to book hotels) and
  2. Hotels. Obviously, there will be many more Standard users than hotels (suppose 1:50).

Now, besides the standard user data (user_id, email, password, etc.) hotels have hotel_id, hotel name, city, country, location_geo, images, and some other fields.

My concern is whether hotels should be a separate table. IMO, having a separate hotels table would be better performance-wise because:

  1. Hotels will be the main ‘attraction’ of the site, i.e. hotels will be read the most and required by the database the most. Hence, IMO, it would be wise to have a separate hotels table, so that there are fewer data to look through and thus data is served more quickly. E.g. if I want to get a user, it is faster if you have to look through 1000 rows (hotels) than 50,000 rows (users)
  2. If I add the hotel-specific fields (hotel_id, hotel name, etc.), most of these fields would be empty (since Standard users do not need them), adding more load to the database.

What do you think should be the right solution?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

It depends on what database you use. Null data fields have no impact on the speed of access on most databases.

  1. You do need to default the additional Hotel fields to Null
  2. Do not execute select *
  3. Do not use fixed length columns

On the other hand, my experience is that over time you may end up adding more and more fields to the hotel record. In which case, it may pay to separate them now.

For more information on indexing look at this link An in-depth look at Database Indexing and SQL Sever Indexes.

Simplistically, indexes are stored in pages and the pages are cached in memory. The indexes are in a binary tree of some sort. The depth or the number of pages that are used to search impact the speed of fetch. A well indexed table will not show significant slowdown for 100s of thousands of records. Millions, maybe another story.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply