4

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

DEHAAS
  • 1,294
  • 11
  • 17
  • 1
    What database system are you using? That will help define what we can offer in terms of solutions (via constraints, triggers, calculated fields, etc.) – Russell Steen Jan 25 '11 at 18:01

7 Answers7

2

Consider storing your StartDay and StartTime, but then have a value for the number of hours that it's open. This will ensure that your closing datetime is after the opening.

OpenDate -- day of week? e.g. 1 for Monday
OpenTime -- time of day. e.g. 08:00
DurationInHours -- in hours or mins. e.g.   15.5
p.campbell
  • 98,673
  • 67
  • 256
  • 322
  • I have thought about this solution as well. However, although it solves the problem of overlapping timespans for a single opening hour definition, the problem still exists when an entity have multiple opening hour definitions. – DEHAAS Jan 25 '11 at 18:03
1

Detecting and preventing overlapping time periods will have to be done at the application level. Of course you can attempt to use a trigger in the database but in my opinion this is not a database issue. The structure that you came up with is fine, but your application logic will have to take care of the overlap.

Vincent Ramdhanie
  • 102,349
  • 23
  • 137
  • 192
  • Naturally this would be a possible solution. However, I would prefer to be ensured that inconsistent data is impossible, if such a solution is at all possible. – DEHAAS Jan 25 '11 at 18:10
1

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
Russell Steen
  • 6,494
  • 6
  • 38
  • 56
  • 1
    Since the trigger is fired after the insert or update has been executed, I will have to somehow roll back the changes. Just as you describe. It seems I cannot be sure to delete the right inserted row, also I do not see a way to roll back an update? – DEHAAS Jan 25 '11 at 18:21
  • This seems to be a pretty awesome solution. Can I be ensured that concurrency issues will not have the trigger be executed at the wrong time? – DEHAAS Jan 25 '11 at 18:32
  • That's where the TRANSACTION part comes in. SQL does a good job of handling each transaction separately such that rolling back one call to a table doesn't affect other calls to the same table at the same time. – Russell Steen Jan 25 '11 at 19:42
  • This doesn't work if inserted.startdate < mytable.startdate < mytable.enddate < inserted.enddate (if the inserted data includes entirely the table entry). – Will59 May 05 '21 at 09:16
1

There's an article by Joe Celko on the SimpleTalk website, over here, that discusses a similar issue, and presents am elegant if complex solution. This is probably applicable to your situation.

Philip Kelley
  • 39,426
  • 11
  • 57
  • 92
0

A table with a single column TimeOfChangeBetweenOpeningAndClosing?

More seriously though, I would probably not worry too much about coming up with a single database structure for representing everything, eventually you'll probably want want a system involving recurrences, planned closures etc. Persist objects representing those, and then evaluate them to find out the closing/opening times.

mcintyre321
  • 12,996
  • 8
  • 66
  • 103
  • How would only storing the delta value prevent overlap? – Russell Steen Jan 25 '11 at 18:00
  • You'd have to have a reference day as your starting point, but this may not be a bad solution. The biggest issue I see here is that to adjust one time period you also have to adjust the two on either side of it to compensate. – Kendrick Jan 25 '11 at 18:03
  • +1 for thinking outside the box and for steering the question to the big picture. –  Jan 25 '11 at 18:18
0

This looks like a good solution, but you'll have to write a custom validation function. The built in database validation (i.e. unique, less than x, etc.) isn't going to cut it here. To ensure you don't have overlapping spans, every time you insert a record into the database, you're going to have to select existing records and compare...

Kendrick
  • 3,747
  • 1
  • 23
  • 41
  • Is it possible to write this kind of validation within the database, or do I have to put it in my business logic at the application level? – DEHAAS Jan 25 '11 at 18:11
  • You can write it at the database level, such as what @Russell Steen suggested. – Kendrick Jan 25 '11 at 19:51
0

First the logic, two spans will overlap if the start value of one falls between the start/end of the other. This is much easier if we have datetimes combined, instead of date1,time1 and date2,time2. So a query to find an overlap looks like this.

select openingId
  from opening o1
  join opening o2 on o1.startDateTime 
             between o2.startDateTime
                 AND o2.endDateTime

You can put this into a trigger and throw an error if a match is found.

Ken Downs
  • 4,707
  • 1
  • 22
  • 20