time slot database design

Question

I am creating a database which need to allow booking a resource from start time to end time on a particular day. For example, I have 11 badminton courts. These courts can be booked for 1 hour and it can very also and in a day each court takes 18 bookings from morning 6 am till night 12 pm. (considering each booking is for one hour). The price of booking also varies from day to day, for example morning charges are more than day charges. Weekend charges are more than weekdays charges.

Now my question is, is it advisable to pre-populate slots and then book it for user depending on the availability. But in this case for the abobe example If I need to store slots for next 1 month then I will have to store 11*18*30 = 5940 records in advance without any real bookings.Every midnight I will need to run script to create slots. If no of clubs increases this number can become huge. Is this good design for such systems? If not then what is the better designs in these scenerios.

club name||court || date || start_time || end_time || status || charge ||

a           c1    20/04/2015   6:00        7:00       available
a           c1    20/04/2015   7:00        8:00       available
.
.
.
a           c1    20/04/2015   11:00       24:00      available

.
.
a           c11   20/04/2015   11:00       24:00      available

Answer 1

Assuming that each booking is for one hour (that is, if someone wants two hours on the court, they're taking two bookings of one hour each) it seems to me the most efficient storage mechanism would be a table Booking with columns Court, Date, and Hour (and additional columns for the person who booked, payment stated, etc..) You would insert one record each time a court was booked for an hour.

This table would be sparsely populated, in that there would only be records for the booked hourly units, not for the available ones. No data would be pre-generated; you would only create records when a booking occurred.

To produce an daily or weekly calendar your application would retrieve the booked hours from the database and join this with its knowledge of your hours (6am to midnight) to produce a visualization of court availability.

Answer 2

Now my question is, is it advisable to pre-populate slots and then book it for user depending on the availability. But in this case for the abobe example If I need to store slots for next 1 month then I will have to store 11x18x30 = 5940 records in advance without any real bookings.Every midnight I will need to run script to create slots. If no of clubs increases this number can become huge.

Yes. that is a horrible method. For the reasons you have stated, plus many more.

• The storage of non-facts is absurd

• The storage of masses of non-facts cannot be justified

• If the need to write simple code is an issue, deal with that squarely, and elevate your coding skills, such that it isn't an issue (instead of downgrading the database to a primitive filing system, in order to meet your coding skills).

Notice that what you are suggesting is a calendar for each court (which is not unreasonable as a visualisation, or as a result set), in which most of the slots will be empty (available).

Is this good design for such systems?

No, it is horrible.

It is not a design. It is an implementation without a design.

If not then what is the better designs in these scenerios.

We use databases. And given its unequalled position, and your platform, specifically Relational Database.

We store only those Facts that you need, about the real world that you need to engage with. We need to get away from visualising the thing we need for the job we have to do (thousands of calendars, partially empty) and think of the data, as data, and only as data. Including all the rules and constraints.

Following that, the determination of Facts, or the absence of a Fact, is dead easy. I can give you the Relational Database that you will need, but you have to be able to write SQL code, in order to use the database effectively.

Data Model

Try this:

Resource Reservation Data Model

That is an IDEF1X data model. IDEF1X is the Standard for modelling Relational Databases. Please be advised that every little tick; notch; and mark; the crows foot; the solid vs dashed lines; the square vs round corners; means something very specific and important. Refer to the IDEF1X Notation. If you do not understand the Notation, you will not be able to understand or work the model.

I have included:

• Storage of Facts (Reservations) only. The non-fact or absence of a Fact (Availability) is easy enough to determine.

• club_resource_slot.duration in the Key to allow any duration, rather than assuming one hour, which may change. It is required in any case, because it delimits the time slot.

• resource_code, rather than court number. This allows any club resource (as well as a court number) to be reserved, rather than only a badminton or squash court. You may have meeting rooms in the future.

• Joel's reply re the rate table is quite correct in terms of answering that specific question. I have given a simpler form in the context of the rest of the model (less Normalised, easier to code).

If you would like the Predicates, please ask.

Code/General

You seem to have problems with some aspects of coding, which I will address first:

But the problem in this approach is if I need to find the availability of court based on game,location, date and time slot then I will have to load this rate table for all the clubs and the look into actual booking table if someone has already booked the slots. Is nt the better approach be if I keep the slots in advance and then someone book , jst change the status to booked. so That query will be performed entirely in DB without doing any computation in memory.

• The existence of the rate table, or not, does not create an issue. That can be accomplished via a join. The steps described are not necessary.

• Note that you do not need to "load this whole table" as a matter of course, but you may have to load one table or other in order to populate your drop-downs, etc.

• When someone books a court, simply INSERT reservation VALUES ()

• When someone cancels a reservation, simply DELETE reservation VALUES ()

Code/Data Model

1. Printing your matrix of Reserved slots should be obvious, it is simple.

2. Printing your matrix of Available or Available plus Reserved (your calendar visual) requires Projection. If you do not understand this technique, please read this Answer. Once you understand that, the code is as simple as [1].

3. You need to be able to code Subqueries and Derived tables.

4. Determination of whether a slot is Reserved or Available requires a simple query. I will supply some example code to assist you. "Game" isn't specified, and I will assume location means club.

   IF (
       SELECT COUNT(*)                -- resources/courts reserved
           FROM reservation
           WHERE club_code = $club_code
           AND   date_time = $date_time
       ) = 0
   THEN PRINT "All courts available"
   ELSE IF (
           SELECT COUNT(*)            -- resources/courts that exist
               FROM club_resource_slot
               WHERE club_code = $club_code
               AND   date_time = $date_time
           ) = (
           SELECT COUNT(*)            -- resources/courts reserved
               FROM reservation
               WHERE club_code = $club_code
               AND   date_time = $date_time
           )
       THEN PRINT "All courts reserved"
       ELSE PRINT "Some courts available"
   

Please feel free to comment or ask questions.