Price rules database design for hotel reservation system

Question

Right now I am developing Hotel reservation system.

so I need to store prices on certain date/date range for future days, so the price varies on different days/dates. so I need to store those price & date details in to db. i thought of 2 structures .

1st model :

room_prices : room_id : from_date : to_date : price: is_available: 2nd design: room_prices : room_id : date: price: is_available

so i found the 2nd method is easy. but data stored grows exponentially as my hotel list grows.

say suppose if i want to store next(future) 2 months price data for one hotel i need to create 60 records for every hotel.

in case of 1st design, i don't require that many records.

ex:

``` price values for X hotel :

1-Dec-15 - 10-Dec-15 -- $200,

1st design requires : only 1 row

2nd design requires : 10 rows ```

i am using mysql,

does it have any performance degradation while searching room_prices table.

would someone suggest me any better design ?

Answer 1

I have actually worked on designing and implementing a Hotel Reservation system and can offer the following advice based on my experience.

I would recommend your second design option, storing one record for each individual Date / Hotel combination. The reason being that although there will be periods where a Hotel's Rate is the same across multiple days it is more likely that, depending on availability, it will change over time and become different (hotels tend to increase the room rate as the availability drops).

Also there are other important pieces of information that will need to be stored that are specific to a given day:

1. You will need to manage the hotel availability, i.e. on Date x there are y rooms available. This will almost certain vary by day.
2. Some hotels have blackout periods where the hotel is unavailable for short periods of time (typically specific days).
3. Lead Time - some Hotels only allow rooms to be booked a certain number of days in advance, this can differ between Week days and Weekends.
4. Minimum nights, again data stored by individual date that says if you arrive on this day you must stay x number of nights (say over a weekend)

Also consider a person booking a week long stay, the database query to return the rates and availability for each day of that stay is a lot more concise if you have a pricing record for each Date. You can simply do a query where the Room Rate Date is BETWEEN the Arrival and Departure Date to return a dataset with one record per date of the stay.

I realise with this approach you will store more records but with well indexed tables the performance will be fine and the management of the data will be much simpler. Judging by your comment you are only talking in the region of 18000 records which is a pretty small volume (the system I worked on has several million and works fine).

To illustrate the extra data management if you DON'T store one record per day, imagine that a Hotel has a rate of 100 USD and 20 rooms available for the whole of December:

You will start with one record:

1-Dec to 31st Dec Rate 100 Availability 20

Then you sell one room on the 10th Dec.

Your business logic now has to create three records from the one above:

1-Dec to 9th Dec Rate 100 Availability 20 10-Dec to 10th Dec Rate 100 Availability 19 11-Dec to 31st Dec Rate 100 Availability 20

Then the rate changes on the 3rd and 25th Dec to 110

Your business logic now has to split the data again:

1-Dec to 2-Dec Rate 100 Availability 20 3-Dec to 3-Dec Rate 110 Availability 20 4-Dec to 9-Dec Rate 100 Availability 20 10-Dec to 10-Dec Rate 100 Availability 19 11-Dec to 24-Dec Rate 100 Availability 20 25-Dec to 25-Dec Rate 110 Availability 20 26-Dec to 31-Dec Rate 100 Availability 20

That is more business logic and more overhead than storing one record per date.

I can guarantee you that by the time you have finished your system will end up with one row per date anyway so you might as well design it that way from the beginning and get the benefits of easier data management and quicker database queries.

Answer 2

I think that the first solution is better and as you already noticed it reduce the number of storage you need to store prices. Another possible approach could be : having a single date and assuming that the date specified is valid up until a new date is found, so basically the same structure you designed in the second approach but implementing an internal logic in which you override a date if you find a new date for a specified period.

Let's say that you have ROOM 1 with price $200 from 1st December and with price $250 from 12 December, then you will have only two rows :

1-Dec-15  -- $200
12-Dec-15  -- $250

And you will assume in your logic that a price is valid from the specified date up until a new price is found.