Database design for multiple offers - postgres 9.5
|Added at||2016-11-28 09:11|
I have a booking system which currently supports a single discount for each reservation. I want to extend that and support multiple offers per reservation.
The purpose is for the user to be able to select one of two types of offers on checkout:
Currently I have a table OFFER which holds every offer that a venue is willing to make available:
I have a table SCHEDULE that holds the weekly schedule specifications for each venue:
The first thought is to fully normalize the design and create another table with the name OFFER_TO_SCHEDULE and move every offer there:
Another thought, as I am using Postgres 9.5, is to create a new column inside the SCHEDULE table with jsonb datatype and store the multiple offers there as a json payload. But if I do that, I lose the referential integrity in case of changes in the OFFER table and I am not really sure about read performance gain.
I have to keep in mind that for getting the availability (based on date and time), I need a fast query. Right now my schedule records are 21 for each venue (7 days with 3 availability zones) and multiplied by 16k venus is close to 340k schedule records and growing. In parallel there are joined tables in this query like schedule overrides for a specific date frame, property venue records (music type, styles, venue type etc etc).
Which one is the best approach based on the desired functionality? Is there a better solution?