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?

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.

This looked very good - the only issue here is that I cannot delete a row in the middle of a sequence - because there's a foreign key relation back to the table itself - I want to be able to do that.
–
kastermesterMay 20 '09 at 16:39

On 2008, you can use MERGE. Before that, you first move the row that you want to delete to the end, then delete.
–
A-KMay 20 '09 at 17:32

I can see how potentially I would be able to use this, but as opposed to triggers this brings a few negative points: 1. The use of an extra column that is otherwise not needed. 2. Harder Insert, Update, Delete logic - would probably require use of stored procedures for all to be somewhat sane in the rest of my app, with triggers I can use regular statements (harder because stuff might need moved around). All in all I want to be able to insert, update and delete rows in any order I want, I just don't want overlapping records in the table - triggers allows that and it seems to be working now.
–
kastermesterMay 20 '09 at 19:30

I am working on that at the moment, writing the INSERT trigger, while starting to think of ideas of how to handle the UPDATE one (seeing as it needs to run all it's checks on the final outcome) but I'm running into a dead end on the UPDATE trigger, any hints?
–
kastermesterMay 20 '09 at 16:40