Database uptime

Question

We are currently developing an application in which several objects have associated opening hours. Hours may span several days or may be contained within one day.

Ex. It opens on Monday at 6:00 and closes on Friday at 18:00.

or

It opens on Monday at 06:00 and closes on Monday at 15:00.

In addition, an entity can have several sets of hours of work per day. So far, the best design I have found is to determine the opening hour, which is as follows:

StartDay, StartTime, EndDay and EndTime.

This design provides the necessary flexibility. However, data integrity is becoming a problem. I cannot find a solution that would prohibit overlapping gaps (in the database).

Share your thoughts.

EDIT: Microsoft SQL Server 2008 R2 Database

Answer 1

Assuming a robust trigger structure

When inserting / updating, you should check to see if the new start or end date falls within any existing range. If this happens, you will discard 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 

Answer 2

Consider storing StartDay and StartTime, but then specify a value for the number of open hours. This will ensure that the date and time are closed after opening.

 OpenDate -- day of week? eg 1 for Monday OpenTime -- time of day. eg 08:00 DurationInHours -- in hours or mins. eg 15.5 

Answer 3

There is an article by Joe Selco on the SimpleTalk website, over here , which addresses a similar issue, and is an elegant if complex solution. This is probably applicable to your situation.

Answer 4

Single-column table TimeOfChangeBetweenOpeningAndClosing?

More seriously, although I would probably not be too worried about coming up with a single database structure to represent everything, in the end you probably want to create a system that includes repetitions, scheduled closures, etc. Persist the objects representing them, and then evaluate to find out the closing / opening time.

Answer 5

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

Answer 6

This seems like a good solution, but you have to write a special validation function. Built-in database validation (i.e. Unique, less than x, etc.) is not collected here. To make sure that you do not have overlapping intervals, every time you insert a record into the database, you will have to select existing records and compare ...

Answer 7

First logic, two gaps will overlap if the initial value of one falls between the beginning / end of the other. It is much easier if we have time together, instead of date1, time1 and date2, time2. Thus, the query to search for overlap is as follows.

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

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