DavidM

The attributes we are concerned with are RoomID SMALLINT, StartDate SMALLDATETIME and EndDate SMALLDATETIME
Someone asked why we aren't using DATETIME and reminded us of the Y2K problem.. A valid question I suppose, but considering there are at least another 75 years before smalldatetime expires and the fact that we don't need the resolution of datetime we thought it was overkill.

The first debate (as always in our shop) is Key selection.
While the overlap constraint itself enforces uniqueness we need a Primary Key to support replication from this table.
To cut to the chase, it was a battle between (RoomID, StartDate) or (RoomID, EndDate)
Both will be unique, so the debate was now about what queries are used and why not both.
It was decided that (RoomID, StartDate) would be marked as the PK and the other candidate key was pointless due to the Overlap constraint providing uniqueness.

The other business rules we where asked to enforce where..

• Bookings must be greater than 30 minutes and less than 4 hours
• No Bookings on Weekends
• Bookings during business hours only
• Appointment times and duration are based on quarter hours. That is appointments can start on the hour, at quarter past, half past, quarter too.

CREATE FUNCTION dbo.IsNotOverlapped
(@RoomID SMALLINT)
RETURNS BIT
AS
BEGIN
DECLARE @Bit BIT
SET @Bit = 0
IF NOT EXISTS(SELECT 1
   FROM Bookings B
   INNER JOIN Bookings X on B.RoomID = X.RoomID
     AND B.StartDate < X.Enddate AND B.Enddate > X.StartDate
   WHERE B.RoomID = @RoomID
   GROUP BY B.Roomid, B.Startdate
   HAVING COUNT(*) > 1)
   SET @Bit = 1
RETURN @Bit
END
GO
CREATE TABLE Bookings
   (RoomID SMALLINT NOT NULL,
   StartDate SMALLDATETIME NOT NULL,
   EndDate SMALLDATETIME NOT NULL,
   CONSTRAINT CHK_Bookings_Duration_MinMax CHECK(DATEDIFF(n,StartDate,EndDate) BETWEEN 30 AND 240),
   CONSTRAINT CHK_Bookings_Duration_Interval CHECK(DATEDIFF(n,StartDate,EndDate)%15 = 0),
   CONSTRAINT CHK_Bookings_NotWeekends CHECK(DATENAME(dw,StartDate) NOT IN ('Saturday','Sunday')),
   CONSTRAINT CHK_Bookings_BusinessHours CHECK(DATEPART(hh,StartDate) BETWEEN 9 AND 16 AND (DATEPART(hh,EndDate) < 17 OR (DATEPART(hh,EndDate) = 17 AND DATEPART(n,EndDate) = 0))),
   CONSTRAINT CHK_Bookings_StartTime CHECK(DATEPART(n,StartDate) IN (0,15,30,45)),
   CONSTRAINT CHK_Bookings_NotOverlapped CHECK(dbo.IsNotOverlapped(RoomID) = 1),
   CONSTRAINT PK_Bookings PRIMARY KEY (RoomID, StartDate)
   )
GO
--OK
INSERT Bookings
     SELECT 1, '20040106 12:00', '20040106 13:00'
     UNION ALL
     SELECT 1, '20040106 13:00', '20040106 13:30'
--Fail - Max Broken
INSERT Bookings SELECT 1, '20040104', '20040105'
--Fail - Min Broken
INSERT Bookings SELECT 1, '20040105 12:59', '20040105 13:24'
--Fail - Overlap
INSERT Bookings SELECT 1, '20040106 11:00', '20040106 14:00'
--Fail - Outside business hours
INSERT Bookings SELECT 1, '20040107 8:30', '20040107 10:00'
--Fail - Interval
INSERT Bookings SELECT 1, '20040106 13:40', '20040106 14:15'
--Fail - Weekend
INSERT Bookings SELECT 1, '20040103 13:15', '20040103 14:00'
--OK
INSERT Bookings SELECT 1, '20040106 16:30', '20040106 17:00'
GO
--Clean up
DROP TABLE Bookings
DROP FUNCTION dbo.IsNotOverlapped

It is worth noting that the UDF doesn't actually need any parameters.
This is because a CHECK Constraint is evaluated after the insert/update.
Have a look in the execution plan and you will notice that the ASSERT method is after the corresponding update/insert action.
It is purely for performance reasons that we add the RoomID to use the Primary Keys index.

As a side note, could anybody find a more elegant way to enforce the "during business hours" rule (9am-5pm)?