Hotel rates table design for Online Travel Agency (OTA)

A hotel has many rooms and a room has many rate plans.

A rate plan is simply a variant of the room, for example, RoomOnly, RoomWithBreakfast, RoomPlusBreakfastPlusLunch etc. Usually each higher in price compared to the previous one.

In each rate plan, a rate is defined for everyoccupancyTypes. Occupancy types are like these - Single, Double, Triple, Quad. ExtraChild, ExtraAdult etc.

Rates can be different for different dates / date ranges.

rates_table

from_date
to_date
rate_type_id
occupancy_type_id
rate

One more dimension can be added as well, which is contract_type. (B2C, B2B, OfflineRates etc.). In that case the table will grow more.

How rates are set

• User can manually set rates
• User can define a formula using which the app calculates different rates for every single day (minor changes)
• User define a master rate and all other rates are linked to it based on percentage or fixed values.

In any of these cases, user/machine will be updating the rates.

Note that, rates are not overlapped in rates_table. So insertion has some overload as well.

The reason for showing how rates are set is to tell that rates keep changing rapidly. By user or by machine. There are complex rate queries as well, such as change rates for 1st december to 20th december excluding weekends. In this case many entries will go in the rates_table and each entry would ensure that it does not overlap any other entries.

Questions

1. If it turns out that 80/90% of the time, rates will be different every single day, then shall i just keep one day column instead of from_date and to_date ? Will that improve performance for select queries ?
2. The table size we be in millions and billions if we follow this approach. Is there any dedicated way to achieve some performance enhancement ? For example how about partitioning the table based on some formula, for example hotelId or roomId ?
3. hotel > room > ratePlan > rate tables are connected like this. Thats why I am passing only ratePlanId in rates_table. Is it worth passing hotelId and roomId also in the rates table? Will that improve the performance ?