I'm trying to figure out best to handle building hours in a DB. The buildings all have default hours (e.g. M-F 8:00 - 19:00, Sa. 10:00 - 17:00, Su. closed), but may also have exceptions to the posted building hours, e.g. during holidays it may have shortened hours or no open hours at all.
Should I create separate tables for default hours and exceptions, e.g.:
| Default Hours Table |
|---|
| Building_ID |
| Day_of_Week_ID |
| Open_Time |
| Close_Time |
| Exceptions Table |
|---|
| Building_ID |
| Start_Date |
| End_Date |
| Open_Time |
| Close_Time |
I feel like I'm overlooking a solution that is elegant, simple, and obvious—I'm willing to settle for any one of the three.
