An overlap constraint in SQL2K

A future requirement of 3 applications we support, is that they will be competing for a resource that they all share. In this case it is a series of Rooms for appointments.
The overlap constraint will ensure that a room cannot be overbooked.
After a discussion with the middle and interface tier developers, it was decided that this should be enforced in the Database at DDL time and not via code, Stored Procedures or triggers,
No surprise that they where happy with that decision...

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.
When we implement business rules via CHECK constraints, we always try not to bundle them but keep them separate so the middle tier can identify which rule is broken...

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)?

Print | posted on Wednesday, March 10, 2004 2:34 PM

Feedback

# re: An overlap constraint in SQL2K

left by Lavos at 3/10/2004 3:48 PM Gravatar
Depends on your definition of what would be more elegant.

I imagine it would be to say "time BETWEEN '08:00 AM' AND '5:00 PM'"? Having seperate date and time fields would be nice for this, but might muddy your other constraints.

You can strip the date leaving the time using stupid cast tricks like this:

CREATE FUNCTION GetTimeFromSmallDateTime(@time smalldatetime)
returns smalldatetime
AS
BEGIN

RETURN (SELECT CAST(CAST(@time as float) - CAST(@time as int) as smalldatetime))

END


And then rewrite the constraint as:

CONSTRAINT CHK_Bookings_BusinessHours CHECK(owner.GetTimeFromSmallDateTime(StartDate) BETWEEN '9:00 AM' AND '4:00 PM' AND GetTimeFromSmallDateTime(EndDate) <= '05:00 PM')

# Take Outs for 9 March 2004

left by Enjoy Every Sandwich at 3/10/2004 1:43 AM Gravatar
You've been Taken Out! Thanks for the good post.

# re: An overlap constraint in SQL2K

left by Nicole Calinoiu at 3/11/2004 12:13 AM Gravatar
Adding the start and end dates as parameters to the "no overlap" function will go even further to improve performance. e.g.:

CREATE FUNCTION dbo.IsNotOverlapped
(
@RoomID SMALLINT,
@StartDate SMALLDATETIME,
@EndDate SMALLDATETIME
)
RETURNS BIT
AS
BEGIN
DECLARE @Bit BIT
SET @Bit = 0

IF (SELECT COUNT(*)
FROM Bookings
WHERE
(RoomID = @RoomID) AND
(StartDate < @EndDate) AND
(EndDate > @StartDate)
) < 2 SET @Bit = 1

RETURN @Bit
END

Assuming that the contraint has already been evaluated for all existing rows (which it should have been), you will lose nothing with respect to rule enforcement, but you will likely gain quite a bit in terms of execution time, particularly as the number of existing rows grows.

Also, if you're going to follow Lavos' suggestion for the time part extraction, don't use a direct integer cast to grab the date portion since it'll round values with time portions after noon up to the next day. A safer date portion extraction would use the FLOOR() function:

cast(floor(cast(@time as float)) as smalldatetime)


One last note... For non-overlap logic to work correctly, it's important that bookings have duration > 0. The CHK_Bookings_Duration_MinMax already covers this, but the constraint enforces a business rule rather than a strict data rule. Given that the business rule might be altered or removed at any time, it would probably be a good idea to add a constraint to enforce the EndDate > StartDate rule. This will ensure that all the various queries that make this assumption will continue to work even if the business rule is modified at some time in the future.

# re: An overlap constraint in SQL2K

left by DavidM at 3/11/2004 8:43 AM Gravatar
Thanks for the great feedback Nicole..

I was trying to keep it as close to the syntax of what it would look like if it was a proper relational constraint. In hindsight, once I added one parameter, I should have kept going....
It is a considerable speed difference by adding the other parameters.. at least 50% faster..

Although not a high volume table.. ~100 rooms, ~4 appointments per day/room, the rest of the databases on the server would appreciate the relief..

As you said, the constraint (yours and mine) are not true relational constraints as they only enforce per row or room and not the table itself....I think we might enforce that other candidate key now..

We are not going to screw around with the business hours constraint.. It works.

We are fully aware that the duration must be non-zero.. Point taken though....
Comments have been closed on this topic.