Price per date database design for hotel reservation system (store each date vs calcul it at runtime)

Question

I have a requirement similar to this question, I need to store prices per date (prices change seasonally) and I have designed the bellow model:

  room_calendar : 
         room_id :
         date :
         price:

I must store dates up ~one year then run a query to get all prices for x ranges of date and sum() all prices.

I think that approach is fine, but the downside is the volume of data in the database and the performance during queries when the database has many records.

I have another second approach in mind:

(calcul date at runtime) Yes I know! it may sound crazy

db model:
   room_calendar : 
         room_id :
         date_rule: <- store a dateutil rrule
         price:
results:
{'room_id': {'dates': <dateutil.rrule.rrule object at 0x7fa2a8e3bda0>, 'price': 100}}
{'room_id': {'dates': <dateutil.rrule.rrule object at 0x7fa2a8e3bda0>, 'price': 150}}

Then:

dates_to_reserve = [datetime(2020, 1, 1), datetime(2020, 1, 2), datetime(2020, 1, 3)]

room_price.objects.filter(date_rule__in=dates_to_reserve)

Of course I would have to create a custom field that supports store dateutil.rrule and when I query it with __in hit the __contains__ method of dateutil.rrule

sorry I can't express everything in a better way, SORRY FOR MY BAD ENGLISH

Do you have something better than the first approach?

Would there be any point in getting so involved with the second approach?

How would you do it? store each date or calcul it at runtime

EDIT

Thanks for your comment @iain-shelvington:

How complicated are the rules that affect the price and are they different for Each room?

1. Each rule is different for each room
2. A room has multiple rules
3. Each rule may be MONTHLY or WEEKLY, mainly

Can you give an example of a room, it's base price and the eventual price for each date? Ej.:

Room standard > price: 150 > date_rule: from Jan 2020 weekly on weekdays until Feb 25 2020
Room standard > price: 170 > date_rule: from Jan 2020 weekly on weekends until Feb 25 2020
Room standard > price: 180 > date_rule: from Feb 2020 weekly on weekends until May 26 2020

I have developed a dateutil.rrule based application that correctly handles even more complex rules, so the rules would not be a problem. Writing an efficient algorithm that calculates prices by date at runtime would be the problem

thanks

Answer 1

I use dateutil.rrrule for a task manager application. I store it in a varchar field and then have functions in the database written in plpythonu3 that can expand that value on demand. That is one way to go.

One example of such a function is:

CREATE OR REPLACE FUNCTION public.task_occurrences(t_rrule character varying, start_dt timestamp with time zone, end_dt timestamp with time zone)
 RETURNS timestamp with time zone
 LANGUAGE plpython3u
 SECURITY DEFINER
AS $function$
from datetime import datetime
from dateutil.parser import parse
from dateutil.rrule import rrulestr

rule = rrulestr(t_rrule, ignoretz=True)
next_occ = rule.between(parse(start_dt, ignoretz=True),
                        parse(end_dt, ignoretz=True), inc=True, count=1)

if next_occ:
    return next_occ[0]
else:
    return None

$function$
;

This finds the first occurrence of a task in a date range or returns None/NULL if there is not one.

Another option would be to store the seasonal prices in a separate table that has start and end dates for the season and the associated price. You could use Postgres date range math range types, range operators to join that table to a room date to fetch the appropriate price.

Example:

CREATE TABLE seasonal_price(id serial, start_date date, end_date date, price numeric);

SELECT price FROM seasonal_price, room_calendar WHERE room_calendar.date <@ daterange(start_date, end_date, '[]');