Hands on: How to solve a speed-critical problem

Here we store the date in the first column as before. In the second column (StartSlot), we store the first free slot of any run of free slots and in the third (FreeSlots), we store the number of contiguous free slots. The data in this SpeedyBookings table stores exactly the same booking pattern as the DefaultBookings table. Slots one and two are booked, so the first row shows that the first free slot on 1 September is three. Slot four is booked, so the run that starts with slot three is just one slot long and the entry in the table looks like this:
01/09/2008 3 1
The next empty slot is five, the next booked one is 20, so there are 15 free slots:
01/09/2008 5 15
A day with no bookings is indicated like this:
02/09/2008 1 32

When a request is made for, say, a three-quarter hour slot, we simply search the FreeSlots column for the number of slots required. The NextAvailable query grid looks like screen 4.

It identifies occurrences of three or more free slots using the criterion >2 in the FreeSlots column. The BookingDate rows are sorted in ascending order to list the earliest date first, and StartSlot is also sorted in ascending order so starting slots are listed in time order through the day. On the menu ribbon, set the Return option to 1 so that it will return just the first row.

The SQL looks like this:
SELECT TOP 1 BookingDate, StartSlot, FreeSlots
FROM SpeedyBookings
WHERE FreeSlots >2
ORDER BY BookingDate, StartSlot;

Is this a good structure? Well, that depends. Like many database design decisions, it achieves the desired outcome by moving the workload around. We need a structure that can be queried quickly to find single and multiple contiguous slots: this table answers that need and querying will be fast. Adopting this structure will, however, add to the workload of updating. Each time a booking is made, an existing row will have to be deleted and either one or two new rows added. For example, if a booking was made on 1 September for slots 31 and 32, one existing row would have to be deleted:
01/09/2008 23 10
and one new row:
01/09/2008 23 8
added, but if slots 25, 26 and 27 were booked instead, two new rows:
01/09/2008 23 2
01/09/2008 28 5
would have to be added.
Also, despite being normalised to third normal form, there is the potential for ‘conflicting rows’ to occur in the table. For example:
01/09/2008 3 1
01/09/2008 4 1
are possible in this table and ‘mask’ the fact that there are two contiguous slots starting with slot three. And two like these:
01/09/2008 3 2
01/09/2008 4 1
contain confusing information because the state of slot four is recorded twice. We can work around all these problems by error-trapping and writing code, but that adds complexity that the simpler structures we used at the beginning don’t have. But then, those structures are too slow.

So whether you consider this a good structure depends on whether you think the gain in querying speed is worth the pain of the added update and delete anomalies. In this particular case, speed was of the essence and this design was adopted