byrmol Blog

After the introduction of our Booking table and its overlap constraint, all is going well when the rules are suddenly shifted.

The requirement now is that each resource (Room) has a certain availability and can only be booked during those times. Each room will have its own unique available times expressed as a Day of the week and the start time and end time during that day. Non consecutive times are all so permitted.
As an example Room 1 is available between 9AM and 5PM Monday to Friday, but Room 2 is only available on a Monday and there is a 1 hour gap during the day when it is not available.

The solution we proposed required a new table, "RoomHours", and a calculated RI constraint. The main problem is that SQL Server has only 2 representations for dates and both are insufficient for our needs. The Day of the week problem was overcome by constraining the StartTime and EndTime columns in the RoomHours table to allow "known" dates representing the day of the weeks. We picked the 1st of January 2001 to the 7th January 2001 as the 1st is a Monday and the 7th a Sunday.

Below is a shortened form of the DDL for the RoomHours table. I have omitted the overlap constraint that is implemented to ensure consistency, as well as the Constraint names. The Duration column is irrelevant....

CREATE TABLE RoomHours(RoomID INT NOT NULL, StartTime SMALLDATETIME NOT NULL,
    EndTime SMALLDATETIME NOT NULL, Duration SMALLINT NOT NULL DEFAULT(15),
    PRIMARY KEY (RoomID, StartTime),
    CHECK (StartTime < EndTime),
    CHECK (Duration >= 5),
    CHECK (StartTime >= '20010101' AND StartTime < '20010108'
AND EndTime >= '20010101' AND EndTime <'20010108'))
GO

Next is the UDF that will enforce the calculated RI between the Bookings table and the RoomHours table.

CREATE FUNCTION dbo.IsValidRoomTime
(
@RoomID SMALLINT,
@StartTime DATETIME,
@EndTime DATETIME
)
RETURNS BIT
AS
BEGIN
DECLARE @Bit BIT
SET @Bit = 0
IF EXISTS
(
SELECT 1
FROM RoomHours
WHERE Roomid = @RoomID
AND @StartTime BETWEEN DATEADD(dd, DATEDIFF(dd, StartTime, @StartTime), StartTime)
    AND DATEADD(dd, DATEDIFF(dd, EndTime, @EndTime), EndTime)
AND @EndTime BETWEEN DATEADD(dd, DATEDIFF(dd, StartTime, @StartTime), StartTime)
    AND DATEADD(dd, DATEDIFF(dd, EndTime, @EndTime), EndTime)
AND DATEPART(dw, StartTime) = DATEPART(dw, @StartTime)
AND DATEPART(dw, EndTime) = DATEPART(dw, @EndTime)
)
SET @Bit = 1
RETURN @Bit
END

The UDF above says if the StartTime and EndTime of the Booking is between the hours of the day of the week for the Room then return 1 else return 0.
Before the final modification to the Booking table, all future bookings that break this new rule are deleted and notifications sent out to the various parties...

Finally the modifications to the Bookings table can be done..
2 existing constraints are dropped CHK_Bookings_NotWeekends and CHK_Bookings_BusinessHours and the new constraint is added.

I don't think anyone has even noticed the changes....