Friday, October 27, 2006

Child Intervals Of Time Inside Parent Ones - Enforced By RI

I needed to enforce a business rule that appointments fit into shifts.
The only one watertight way of doing it that i know about is to use RI, as follows:

CREATE Table Shifts(ShiftID INT NOT NULL PRIMARY KEY,
StartedAt DATETIME NOT NULL,
FinishedAt DATETIME NOT NULL,
CONSTRAINT UK_Shifts UNIQUE(ShiftID, StartedAt, FinishedAt))
go
SET NOCOUNT ON
INSERT Shifts VALUES(1, '20061025 08:00AM', '20061025 05:00PM')
INSERT Shifts VALUES(2, '20061026 08:30AM', '20061026 05:00PM')
INSERT Shifts VALUES(3, '20061027 08:30AM', '20061027 05:00PM')
go
CREATE Table Appointments ( ShiftID INT NOT NULL,
StartedAt DATETIME NOT NULL,
FinishedAt DATETIME NOT NULL,
ShiftStartedAt DATETIME NOT NULL,
ShiftFinishedAt DATETIME NOT NULL,
CONSTRAINT FK_Appointments FOREIGN KEY(ShiftID, ShiftStartedAt, ShiftFinishedAt)
REFERENCES Shifts(ShiftID, StartedAt, FinishedAt) ON UPDATE CASCADE,
CONSTRAINT CHK_TimesInRange CHECK((StartedAt BETWEEN ShiftStartedAt AND
ShiftFinishedAt) AND (FinishedAt BETWEEN ShiftStartedAt AND ShiftFinishedAt)))
go
INSERT Appointments VALUES(1, '20061025 09:00AM', '20061025 05:00PM', '20061025
08:00AM', '20061025 05:00PM')
INSERT Appointments VALUES(2, '20061026 08:30AM', '20061026 01:00PM', '20061026
08:30AM', '20061026 05:00PM')
INSERT Appointments VALUES(3, '20061027 10:30AM', '20061027 01:00PM', '20061027
08:30AM', '20061027 05:00PM')
---- these updates succeed: appointments still fit into their shifts
UPDATE Shifts SET StartedAt = '20061027 09:31AM' WHERE ShiftID = 3
UPDATE Shifts SET FinishedAt = '20061027 04:31PM' WHERE ShiftID = 2
go
-- all fail: range not completely inside the parent range
INSERT Appointments VALUES(3, '20061027 7:30AM', '20061027 01:00PM', '20061027
08:30AM', '20061027 05:00PM')
INSERT Appointments VALUES(3, '20061027 10:30AM', '20061027 08:00PM', '20061027
08:30AM', '20061027 05:00PM')
-- fails because there is an appoinment starting at 10:30
UPDATE Shifts SET StartedAt = '20061027 10:31AM' WHERE ShiftID = 3

0 Comments:

Post a Comment

<< Home