Search code examples
sqldatabasetimespandata-integrityintegrity

Opening Hours Database Design

We are currently developing an application in which multiple entities have associated opening hours. Opening hours may span multiple days, or may be contained within a single day.

Ex. Opens Monday at 6:00 and closes at Friday at 18:00.

Or

Opens Monday at 06:00 and closes Monday at 15:00.

Also, an entity may have multiple sets of opening hours per day. So far, the best design I have found, is to define an opening hour to consist of the following:

StartDay, StartTime, EndDay and EndTime.

This design allows for all the needed flexibility. However, data integrity becomes an issue. I cannot seem to find a solution that will disallow overlapping spans (in the database).

Please share your thoughts.

EDIT: The database is Microsoft SQL Server 2008 R2

Solution

  • Presuming a robust trigger framework

    On insert/update you would check if the new start or end date falls inside of any existing range. If it does then you would roll back the change.

    CREATE TRIGGER [dbo].[mytable_iutrig] on [mytable] FOR INSERT, UPDATE AS

IF (SELECT COUNT(*)
FROM inserted, mytable
WHERE (inserted.startdate < mytable.enddate 
          AND inserted.startdate > mytable.startdate)
      OR (inserted.enddate < mytable.enddate 
          AND inserted.enddate > mytable.startdate)) > 0 
BEGIN
    RAISERROR --error number
    ROLLBACK TRANSACTION
END