1

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?

0

1 Answer 1

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.

5
  • ok thanks. What if e.g. I want to get a user, it is obviously faster if you have to look through 1000 rows (hotels) than 50,000 rows (users). Do you think this is in favor of having a different hotels table?
    – daniell
    Aug 4, 2022 at 12:19
  • If the tables are indexed properly, this quantity of rows are unlikely to affect performance. Aug 4, 2022 at 12:20
  • ok thanks. could you please explain (or link somewhere) how indexing plays such a great role? to my (very little) understanding having to search through 1k vs 50k looks like a big difference. when do you think the ratio becomes important that you would consider having a separate table (10k vs. 10mil), if ever? most of the data I get is from an API, in the database I keep only certain data: so the database for the hotels is unlikely to get larger in terms of number of fields.
    – daniell
    Aug 4, 2022 at 12:26
  • 1
    @daniell Indexing plays a great role because instead of storing the data linearly (like you're thinking about it) it stores it in a B-Tree which divides the data algorithmically efficiently. Without an index, yes there's a difference between scanning 50k rows vs 1k rows (about a 49k row difference). With an index, the way the data is efficiently stored (with a B-Tree) shrinks that difference down to only needing to scan literally 15 vs 9 nodes to search the data. B-Trees aren't a single linear data structure, so the search time is Log2(n), much faster than a linear structure which is n.
    – J.D.
    Aug 4, 2022 at 12:34
  • 1
    hey @J.D. many thanks for the comment. really excited to dig into these new concepts.
    – daniell
    Aug 4, 2022 at 12:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.