How would you design a table for a booking system?

Question

I have a design database for a booking system. The booking system requirement are as follows:

1. The booking system allows user to book a time slot (1 hour) between 8 am to 1 pm for Monday to Friday.
2. The user can book a venue for that 1 hour time slot.
3. 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)

Answer 1

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.

Answer 2

The simplest way is to only store actual bookings in the database.

The table you provided can be used if you change Week to Date. The status field can have more than one value, such as Booked, Not Available, etc.

If you query the database for both Date and Time and no records are returned it means the room is available. Cancelled bookings can be implemented by deleting records, or marking the status field as Cancelled and eliminating these records from the availability query.

I have used a similar schema on a taxi booking system.