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?