Search code examples
phpsqlmysqlperformancesqlperformance

How should I create the most efficient database for a hotel prices?

Let's say we want to make the most efficient hotel website. On this website, rooms may have different prices according to "how many people", dates and days of the week. What is the most efficient way to store and process them?

The first method -> price for every specific date;

date room_id price price_for_2_people
01-01-2023 1 20.00 40.00
02-01-2023 1 20.00 40.00

or -> price for date range;

date_start date_end room_id price price_for_2_people price_for_weekend
01-01-2023 01-03-2023 1 20.00 40.00 30.00
01-03-2023 01-07-2023 1 30.00 50.00 40.00

Please note that from time to time admin users may enter prices for only a single day, and sometimes set prices in wide ranges.

Also, in the second option, calculating the price for a single day may seem much more efficient on the SQL side. Because if you are searching for specific date, instead of searching over 1000 lines, it is much faster to search in 20 30 lines. But if the customer wants to make a 7-day reservation, it will be necessary to collect the prices by searching the days one by one on the coding side. Because the start day and the end day can be in different rows (the price may be 30 euros for the first 3 days, then 40 euros).

A few more variables may come into this table, but these will be the main ones. How should I configure the database so that there are no errors and the algorithm runs the most efficient? Also I would like to escape date collisions and blank dates.

I expect finding best way to create hotel reservations database.

Solution

  • How about something like this?

    • Rooms Table: Contains each room's information.
    room_id room_type capacity
    1 Single 1
    2 Double 2
    3 Suite 4
    • Price Periods Table: Holds the date ranges with specific prices.
    period_id date_start date_end
    1 2023-01-01 2023-01-31
    2 2023-02-01 2023-02-28
    3 2023-03-01 2023-03-31
    • Prices Table: Stores the pricing details based on room type, period, and occupancy.
    price_id room_id period_id price price_for_2_people price_for_weekend
    1 1 1 20.00 40.00 30.00
    2 1 2 30.00 50.00 40.00
    3 2 1 40.00 60.00 50.00

    Key Points to Remember:

    • Avoid Overlapping Dates: When you create or update your price_periods table, ensure there are no overlapping periods for the same room. You should include a check in your application logic to prevent this.

    • Handle Date Gaps: If a room should be available every day, ensure there are no gaps between periods. The end date of one period should be one day before the start date of the next for a given room.

    • Optimize for Speed: If you often retrieve prices based on dates, consider indexing the date_start and date_end columns in the price_periods table. This would significantly speed up your queries.

    • Choose the Right Database Type: This design is suitable for SQL databases. However, if your usage pattern doesn't align with SQL, a NoSQL database might be a better option, offering more flexibility in data structures.

    Here are some Example SQL SELECT Queries to illustrate:

    1. Retrieve the details OF a specific room:
    SELECT * 
FROM rooms 
WHERE room_id = 1;
    1. Retrieve the prices FOR a specific room:
    SELECT r.room_id, r.room_type, p.price, p.price_for_2_people, p.price_for_weekend 
FROM rooms r 
JOIN prices p ON r.room_id = p.room_id 
WHERE r.room_id = 1;
    1. Retrieve the prices FOR a room FOR a specific date:
    SELECT r.room_id, r.room_type, p.price, p.price_for_2_people, p.price_for_weekend 
FROM rooms r 
JOIN prices p ON r.room_id = p.room_id 
JOIN price_periods pp ON p.period_id = pp.period_id 
WHERE r.room_id = 1 AND '2023-01-15' BETWEEN pp.date_start AND pp.date_end;
    1. Retrieve the prices FOR ALL rooms FOR a specific date:
    SELECT r.room_id, r.room_type, p.price, p.price_for_2_people, p.price_for_weekend 
FROM rooms r 
JOIN prices p ON r.room_id = p.room_id 
JOIN price_periods pp ON p.period_id = pp.period_id 
WHERE '2023-01-15' BETWEEN pp.date_start AND pp.date_end;
    1. Retrieve the prices FOR a room FOR a range of dates (this query will return prices for each period within the date range separately):
    SELECT r.room_id, r.room_type, p.price, p.price_for_2_people, p.price_for_weekend, pp.date_start, pp.date_end 
FROM rooms r 
JOIN prices p ON r.room_id = p.room_id 
JOIN price_periods pp ON p.period_id = pp.period_id 
WHERE r.room_id = 1 AND pp.date_start >= '2023-01-01' AND pp.date_end <= '2023-01-31';