Sql-server – How would you design a table for a booking system

database-designsql-server-2008

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)

Best Answer

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.

Related Solutions

Sql-server – Is it possible to generate table with generated data without manual INSERT statements

You can use 3 CTE expressions to generate the data, then cartesian product them together.

Test table:

create table calendar
(
week integer,
time varchar(20),
venue varchar(20)
);

Weeks:

SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM weeks WHERE n < 10;

Venues:

SELECT 1 AS v
UNION ALL
SELECT v+1 FROM venues WHERE v < 5;

Times:

SELECT '11 am' AS t
UNION
SELECT '12 pm' AS t;

Put them all together to make a test query:

WITH weeks
AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM weeks WHERE n < 10
),
venues AS (
SELECT 1 AS v
UNION ALL
SELECT v+1 FROM venues WHERE v < 5
),
times AS (
  SELECT '11 am' AS t
  UNION
  SELECT '12 pm' AS t
)
SELECT n as week, t as time, 'Room '+CAST(v AS VARCHAR) as venue
FROM weeks, venues,times
;

Convert to an INSERT statement:

WITH weeks
AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM weeks WHERE n < 10
),
venues AS (
SELECT 1 AS v
UNION ALL
SELECT v+1 FROM venues WHERE v < 5
),
times AS (
  SELECT '11 am' AS t
  UNION
  SELECT '12 pm' AS t
)
INSERT INTO calendar (week, time, venue)
SELECT n as week, t as time, 'Room '+CAST(v AS VARCHAR) as venue
FROM weeks, venues,times
;

Db Design of a Room Booking System

How do you know how long a room is booked for? Your model indicates when a booking starts, but when does it end?

Also, your ERD shows foreign keys from BOOKING to USER and ROOM but these columns aren't acknowledged in your BOOKING table. Some other answers have interpreted that as the columns being missing. You should be explicit in your diagram to avoid confusion (or add the FK columns if you missed them!)

Best Answer

Related Solutions

Sql-server – Is it possible to generate table with generated data without manual INSERT statements

Db Design of a Room Booking System

Related Question