Maintaining referential integrity in a booking system

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

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

• Add CourseSizeMax column to your dbo.Session table, and add a UNIQUE constraint on [dbo].Session - it is needed later.

• Add CourseSizeMax column, and BookingNumber column to your dbo.Booking table.

• Add a FK constraint on dbo.Booking(CourseId, CourseSizeMax) referring to Session

• Add a CHECK(BookingNumber BETWEEN 1 AND CourseSizeMax)

• 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.

StackExchange Database Administrators Q#15757, answer score: 5