Database design for multiple offers - postgres 9.5

82 Views Asked by At

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:

  1. discount OR a free item
  2. discount OR one of the special menus
  3. 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?

0

There are 0 best solutions below