Ensuring correct data in MS SQL Server, DB Level

Question

Hey Stackoverflow.

Say I have a table with the following layout:

Id  Int PRIMARY KEY IDENTITY
DateFrom datetime NOT NULL
DateTo datetime NOT NULL
UserId Int

UserId is a foreign key to a users table, and well obviously there's more data which is irrelevant to this question.

What I want to do, is to make sure that per User, there can not be any overlapping "periods", that is, say we have a row of data for User 1, from the 15th of May 2009 to the 18th of May 2009, then there cannot be any other rows in the database that contains any of those dates (15-18th of May 2009, both dates included).

How would I go about this in the best way? My initial thinking would be to use triggers, but I'm not exactly sure how and/or if there's a better way of doing some more "custom" data validation like this?

Answer 1

My friend I beleive you are spot on. Triggers are designed for this exact reason, to implement Business Rules.

The Books Online documentation has a few examples that will help to get you started.

http://msdn.microsoft.com/en-us/library/ms189799.aspx

Answer 2

You need to use an INSERT/UPDATE trigger. Make sure when you write the trigger that it handles the case where more than one row is being inserted or updated at a time.

Answer 3

A common problem. You can use constraints:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/03/08/storing-intervals-of-time-with-no-overlaps.aspx

Answer 4

One way to do it, especially if your granularity is at the day level (and by the way, if that is the case and you're SQL Server 2008, then you should probably use the date type instead of datetime), is to have another table like: UserId int, Day datetime, Primary Key (UserId, Day) And then insert, delete, update all the days for a user on any insert, delete, update in your original table and just let the server's uniqueness constraint on the PK do the validation for you.