CODEWITHSUNDEEP
databasedatabase-designcalendar
lulalala
lulalala

Reputation: 17981

How to design a schema for periods of dates with exceptions?

The site is about special discount events. Each event contains a period of time (dates to be more precise) that it is valid. However there will often be a constrain that the deal is not valid in say Saturdays and Sundays (or even a specific day).

Currently my rough design would be to have two tables:

Event table store EventID, start and end date of the duration and all other things.

EventInvalidDate table stores EventID, and specific dates which the deals are not valid. This requires the application code to calculate invalid dates upfront.

Does anyone know of a better pattern to fit this requirement, or possible pitfall for my design? This requirement is like a subset of a general calender model, because it does not require infinite repeating events in the future (i.e. each event has a definite duration).

UPDATE

My co-worker suggested to have a periods table with start and end dates. If the period is between 1/Jan and 7/Jan, with 3/Jan being an exception, the table would record: 1/Jan~2/Jan, 4/Jan~7/Jan.

Does anyone know if this is better the same as the answer's approach, in terms of SQL performance. Thanks

Upvotes: 1

Views: 561

Answers (1)

Bevan
Bevan

Reputation: 44307

Specifying which dates are not included might keep the number of database rows down, but it makes calculations, queries and reports more difficult.

I'd turn it upside down.

  • Have a master Event table that lists the first and last date of the event.

  • Also have a detail EventDates table that gets populated with all the dates where the event is available.

Taking this approach makes things easier to use, especially when writing queries and reports.

Update

Having a row per date allows you to do exact joins on dates to other tables, and allows you to aggregate per day for reporting purposes.

select ...
from sales
     inner join eventDates
     on sales.saleDate = eventDates.date

If your eventDates table uses start and end dates, the joins become harder to write:

select ...
from sales
     inner join eventDates
     on sales.saleDate >= eventDates.start and sales.SaleDate < eventDates.finish

Exact match joins are definately done by index, if available, in every RDBMS I've checked; range matches, as in the second example, I'm not sure. They're probably Ok from a performance perspective, unless you end up with a metric ton of data.

Upvotes: 1

Related Questions