How would you design a table for a booking system? I have a design database for a booking system. The booking system requirement are as follows:


The booking system allows user to book a time slot (1 hour) between 8 am to 1 pm for Monday to Friday.
The user can book a venue for that 1 hour time slot.
The booking system is open for 13 weeks, then rest for 13 weeks and open for another 13 weeks and rest for 13 weeks for each year.


The problem that I face when I design the table is that there are 3 important controlling variable - timeslot, venue and 13 weeks.

I have draft the design by having a table that contain a fixed amount of rows. (So, for a 8 am to 1 pm, there will be 5 slots, and thus each week will have 25 slots. Suppose there are 3 venue, then I will have 25 slots x 3 venue, which give me 75 rows. Then I multiply 75 to 26 weeks, because there are two 13 weeks that the system will be open. Thus, this will give me 1950 fixed rows.

But, the problem will be that if I increase the number of hours by ending it at 10pm, then I will have 14 slots per day, which mean that each week will have 70 slots. What if the venue increase to 10 venue, then, I will have 70 slots x 10 venue, which give me 700 rows. Of course, multiply it by 26 weeks, I will need to have a table that contain a 18,200 fixed rows. The design will be hard to manage since there are so many rows.

The table would look something like the following:


+----------------------------------------------------------------------+
|  ID      |   Week    |   Time   |   Venue   |   User   |    Status   |
+----------------------------------------------------------------------+
|  000001  |   1       |  8 am    |  Room 1   |          |  Available  |
|  000002  |   1       |  9 am    |  Room 1   |          |  Available  |
|  000003  |   1       |  10 am   |  Room 1   |          |  Available  |
|                                                                      |
|  018200  |   26      |  10 pm   |  Room 10  |          |  Booked     |
+----------------------------------------------------------------------+


The ID will be the PK for this table.

Is there a way that I can keep track of each booking slot but without creating a fixed amount of rows for each booking slot? (Each booking slot will contain 3 important data - the week, time, venue)
If the concern is manual maintenance of a large number of rows, you could solve this (potentially) by creating tables for each of your dimensions: weeks, time slots, and venues.  The complete set of possible slots for booking would be the cross product of these three dimensions. Actual bookings would be another table with foreign keys pointing to all three of these dimension tables.   

With this type of design, instead of maintaining 3 x 25 x 26 records you will maintain 3 + 25 + 26 records.  Note that if you segregate day of week and time of day into two tables you can reduce the number of records to be maintained even further (3 + 5 + 5 + 26).

The problem with this approach is when (and if) you have an exception.  This design assumes that there are no blackouts in your schedule.  For example, what if you close a room for a couple of weeks to be renovated?  One way to handle this issue is to create booking records that cover the blackouts.  If you have enough exceptions, then managing them may be almost as bad as just using the brute force method.

The question I would seriously consider is whether or not generating the initial list of slots available for booking is really that big a deal.  You could easily automate the  process to generate your big pile of available slots.  This is really just a single query.