Avalability Table/View for Hall Booking

Question

I am doing a hall booking application.

There are many halls at an establishment.

There is a fixed start time and end time for the all the halls, within which the slots should fall.

These are defined in the Hall table with the fields HallID, OpeningTime, ClosingTime.

The table HallBooking contains the booking details. The fields are HallID, Duration, StartTime, EndTime.

The booking is not based on pre-defined slots. The start time and end time could be anywhere between the Hall OpeningTime and ClosingTime. The criteria is that the hall should have a free slot anywhere that day to accomodate the Duration.

So the summary is:

Table Hall (HallID, OpeningTime, ClosingTime) Table HallBooking (HallID, Duration, StartTime, EndTime)

Inputs for booking search: Date, duration

I am using SQL Server 2005. How can I get an availability table/view which shows all the available slots in all the halls? I think if i can get this availability table/view, I can take it up from there.

Answer 1

This is what I would do. I would create a table of available slots for each hall (by hour or half hour or what ever the minimum time you schedule for). Then I would mark them as used when the time is scheduled. I would initally populate for the next year or six months or however far out you are willing to schedule. Then each week I would run an automated job which would populate the next weeks worth of slots for the end period. So I I populated from 1 august 2009 until 1 Aug 2010, after a week, the automated job would add the slots for 2-8 August 2010.

The reason why I would do it this way is it is very fast to query a table of open slots and it is not so fast to calculate the open slots based on scheduled slots.