Maintaining referential integrity in a booking system

Question

I am developing a second version of a corporate training booking system on Microsoft SQL Server 2005.

I have 3 tables (simplified for this question).

Table 1- CourseSize table- this determines the maximum number of participants able to book into the session.
Table 2- Session- the course to be provided, the date/time and a reference to CourseSize record.
Table 3- Booking table- the person who made the booking with reference to the relevant Session record.

The SQL is as follows-

CREATE TABLE [dbo].[CourseSize]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CourseSizeMax] [tinyint] NOT NULL,
    CONSTRAINT [PK_CourseSize] PRIMARY KEY CLUSTERED ([ID] ASC)
);

CREATE TABLE [dbo].[Session]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CourseSizeID] [int] NOT NULL,
    CONSTRAINT [PK_Session] PRIMARY KEY CLUSTERED ([ID] ASC)
);
GO

ALTER TABLE [dbo].[Session] WITH CHECK 
ADD CONSTRAINT [FK_Session_CourseSize] 
FOREIGN KEY([CourseSizeID])
REFERENCES [dbo].[CourseSize] ([ID]);
GO

ALTER TABLE [dbo].[Session] 
CHECK CONSTRAINT [FK_Session_CourseSize];
GO

CREATE TABLE [dbo].[Booking]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [SessionID] [int] NOT NULL,
    CONSTRAINT [PK_Booking] PRIMARY KEY CLUSTERED ([ID] ASC)
);
GO

ALTER TABLE [dbo].[Booking] WITH CHECK
ADD CONSTRAINT [FK_Booking_Session] 
FOREIGN KEY([SessionID])
REFERENCES [dbo].[Session] ([ID]);
GO

ALTER TABLE [dbo].[Booking] 
CHECK CONSTRAINT [FK_Booking_Session];
GO

Problem- I need to ensure that inserts into the Booking table are stopped if the number of bookings against a session has reached the maximum course size. In the past I have used something like this:

INSERT INTO Booking
SELECT 1 AS SessionID    
WHERE 
    (
        SELECT COUNT(*) 
        FROM Booking 
        WHERE SessionID = 1
    ) <= 
    (
        SELECT CourseSizeMax 
        FROM CourseSize 
            INNER JOIN Session ON CourseSize.ID = Session.CourseSizeID 
        WHERE Session.ID = 1
    );

This fails when multiple users make bookings at the same time.

I tested the above query by setting the CourseSizeMax to 2, and using WAITFOR time. I scheduled the query to run 3 times; 2 at the same time and the last 1 ms before the other two. They all were able to insert a row, thereby exceeding the session size limit.

I would really like to avoid using a trigger to handle this; my preference is to use constraints. I have total control of the design of the database so there is no problem with changing the structure if need be.

How do I prevent simultaneous updates from breaking the course size limitation?

Answer 1

If you only had two tables, Session and Booking, you could do this:

1. Add CourseSizeMax column to your dbo.Session table, and add a UNIQUE constraint on [dbo].[Session]([ID], [CourseSizeMax]) - it is needed later.
2. Add CourseSizeMax column, and BookingNumber column to your dbo.Booking table.
3. Add a FK constraint on dbo.Booking(CourseId, CourseSizeMax) referring to Session
4. Add a CHECK(BookingNumber BETWEEN 1 AND CourseSizeMax)
5. Add a UNIQUE constraint on dbo.Booking(CourseId, BookingNumber)

and you are all set, as long as all your constraints are trusted.

I am not sure why would you need the third table CourseSize at all.

Answer 2

What you are asking for is a constraint across 2 tables or a table-level constraint with a subquery that involves another table (and that is a limitation of SQL-Server table-level constraints). Check the CREATE TABLE syntax, paragraph Check Constraints:

A column-level CHECK constraint can reference only the constrained column, and a table-level CHECK constraint can reference only columns in the same table.

In SQL-92 standard, there is ASSERTION (a constraint across more than 1 table), which is actually what you would use if it was available. See the asnwers in this question: Why don't DBMS's support ASSERTION for details and for info about some products (MS-Access) that have such functionality with limitations.

Firebird documentation says it allows subqueries in CHECK constraints.

In SQL-Server, your only solution would be a trigger, I'm afraid.