'Table/Trigger design to prevent time overlaps in booking table using SQL
I'm trying to design a table (or possibly trigger) which consists of a roomNum, startTime, endTime, and a groupName. It is supposed to keep track of which group (represented by groupName) books which room and when. I have the create table statement as such:
CREATE TABLE ProjectRoomBookings
(roomNum INTEGER,
startTime INTEGER,
endTime INTEGER,
groupName CHAR(10),
PRIMARY KEY (roomNum, startTime),
CONSTRAINT Check_time CHECK (startTime<endTime));
For simplicity we express times in the 24h military-style format, eg. 9 for 9AM, 17 for 5PM, etc. and use only whole hours (eg. 9:30 not valid, only 11,12,13...etc. valid). The Primary Key is a combination of the roomNum and startTime. So we can have entries like
roomNum startTime endTime groupName
~~~~~~~ ~~~~~~~~~ ~~~~~~~ ~~~~~~~~~
1. 2 3 A
1 4 5 B
However, a new entry (for a new group) could be accidentally put in to occupy a room, even before the existing group in that same room is done using it. As seen here:
roomNum startTime endTime groupName
~~~~~~~ ~~~~~~~~~ ~~~~~~~ ~~~~~~~~~
1. 2 4 A
1 3 4 B
How will I redesign the table (or write a trigger) to avoid SQL inserts like the second record above.
I have already checked this out, but my composite key preferably needs to remain a combination of the roomNum and startTime.
PS - New to SQL. Some help would be much appreciated.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
