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:
- discount OR a free item
- discount OR one of the special menus
- a free item OR one of the special menus
Currently I have a table OFFER which holds every offer that a venue is willing to make available:
offer_id
store_id
type (freebie OR special_menu)
title
I have a table SCHEDULE that holds the weekly schedule specifications for each venue:
schedule_id
store_id
zone_id (noon, afternoon, night)
option_id (this is currently the discount ex. 30%)
day_number
start_time
stop_time
num_tables
The first thought is to fully normalize the design and create another table with the name OFFER_TO_SCHEDULE and move every offer there:
offer_to_schedule_id
offer_id
schedule_id
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?