Database design for business hours considering holidays and specials cases

Question

I saw a few examples on stackoverflow on how to design a database table to store business hours but they doesn't answer all my needs. They doesn't support defining different hours depending of the periode of the year and also doesn't support holidays and special time of the year were they can be closed.

My needs

• support business hours that overlap 2 days. Example, a bar can open at 6pm and close at 3am
• support multiple opening hours in the same day
• support dates were they are closed
• support different cases where the open/close hours are different during a certain period of time

Scenarios

In general, the store #1 will have these open hours

• Monday, 9am to 12pm and 1pm to 5pm
• Tuesday, 9am to 12pm and 1pm to 5pm
• Wednesday, 9am to 12pm and 1pm to 5pm
• Thursday, 9am to 12pm and 1pm to 9pm
• Friday, 9am to 12pm and 1pm to 9pm
• Saturday, 10am to 5pm
• Sunday, closed

During the month of december, the opens hours are different

• Monday, 9am to 12pm and 1pm to 9pm
• Tuesday, 9am to 12pm and 1pm to 9pm
• Wednesday, 9am to 12pm and 1pm to 9pm
• Thursday, 9am to 12pm and 1pm to 9pm
• Friday, 9am to 12pm and 1pm to 9pm
• Saturday, 10am to 5pm
• Sunday, 10am to 5pm

They are closed on these dates:

• december 25
• january 1

And for some reasons, they have specials cases were the open hours can be different:

• july 10, 1pm to 9pm
• september 20, 1pm to 9pm

My solution so far

StoreId  BeginDate   EndDate     DayOfWeek  OpenHour  Duration
1        2015-01-01  2015-11-30  2          09:00     180
1        2015-01-01  2015-11-30  2          13:00     240
1        2015-01-01  2015-11-30  3          09:00     180
1        2015-01-01  2015-11-30  3          13:00     240
...
1        2015-12-01  2015-12-31  2          09:00     180
1        2015-12-01  2015-12-31  2          13:00     480
1        2015-12-01  2015-12-31  3          09:00     180
1        2015-12-01  2015-12-31  3          13:00     480
...

The problem that i see

• I'm not sure that the BeginDate/EndDate should be in that table. Maybe i should have another table that will define Periods and have a foreign key on the OpenHours table that will link to a period.
• Where should i define closed dates (holidays)?
• Where should i define a special date where the open/close hours is different? Like an override of what is defined?

Answer 1

Im working on the same problem, and so far I have designed a similar approach to yours. But in regards to your questions.

1. Yes, it helps to move the start and end dates to a related table, for reasons seen in #3.
2. Closed dates could just be when the open hour is null
3. Default hours would have no start and end dates. All special hours would have defined start and end dates. The app looks up which groups of dates include todays date. It calculates the interval of the start and end of all groups. The smallest interval wins.

BTW, I am probably going to store my open hour by minute of the day, and leave formatting for later...

Answer 2

Stop thinking about rules. Think about rows.

This is dead simple if you just store open hours. PostgreSQL has particularly good support for this kind of thing.

create table business_hours (
  open tstzrange primary key,
  exclude using gist (open with &&)
);

The exclusion constraint guarantees no overlapping open hours. If it takes two rows per day, a year's data is little more than 700 rows. 100 years of data is only 70k rows. This is the most flexible option, development and testing time is almost nil, and a minimum-wage clerk can verify that the hours you're about to advertise match the hours you're going to be open.

The normal hours

-- The "normal" hours for the week starting Apr 13, 2015 (a Monday).
insert into business_hours values
-- Mon
(tstzrange('2015-04-13 09:00', '2015-04-13 12:00')),
(tstzrange('2015-04-13 13:00', '2015-04-13 17:00')),
-- Tue
(tstzrange('2015-04-14 09:00', '2015-04-14 12:00')),
(tstzrange('2015-04-14 13:00', '2015-04-14 17:00')),
-- Wed
(tstzrange('2015-04-15 09:00', '2015-04-15 12:00')),
(tstzrange('2015-04-15 13:00', '2015-04-15 17:00')),
-- Thu
(tstzrange('2015-04-16 09:00', '2015-04-16 12:00')),
(tstzrange('2015-04-16 13:00', '2015-04-16 21:00')),
-- Fri
(tstzrange('2015-04-17 09:00', '2015-04-17 12:00')),
(tstzrange('2015-04-17 13:00', '2015-04-17 21:00')),
-- Sat
(tstzrange('2015-04-18 10:00', '2015-04-18 17:00'));
-- Sun
-- Closed.

It should be clear from inserting just the "normal" hours that this kind of table can accommodate any kind of logic, whether good or bad.

You can wrap that kind of statement in a stored function in such a way that you can generate a week, a month, or a year of "normal" hours at one time. Update as needed.

In other dbms, you can use two timestamp columns and some check constraints. Checking for overlapping rows would probably have to be done as an exception report rather than as a constraint enforced by the dbms.

create table business_hours (
  opens timestamp not null,
  closes timestamp not null,
  check (closes > opens),
  primary key (opens, closes)
);

Using the pair of columns as a primary key lets the optimizer use index-only scans.

Answer 3

I would store the opening hours as RFC 5445 RRules and ExRules.

Some libraries have functions to show the rule in English text.

I find it's easy to generate RRules and ExRules using Google Calendar.