Database design for sports centers - Manage reservations

Question

I need to design a database to manage reservations of installations in a system with several sports centers. The centers have different installations like swimming pools, tennis courts, basketball courts, etc. In a center maybe there are more than one swimming pool or tennis court and so on. And they can have different availabilities. For example "Swimming pool 1" and "Swimming pool 2" in "Center 1" are available Mondays, Wednesdays and Fridays from 9:00 to 10:00 and from 12:00 to 13:00. "Swimming pool 2" is also available Tuesdays and Thursdays from 08:00 to 09:00 and from 09:00 to 10:00. I can also add excluding dates (like holidays), for example Friday 12/25/2015 and Friday 01/01/2016. Then members can book and installation if is still available.

Now I have the following tables:

CENTER
Id  |   Name
----|-----------
1   |   Center 1
2   |   Center 2
3   |   Center 3

INSTALLATION TYPE
Id  |   Description
----|----------------
1   |   Swimming
2   |   Tennis
3   |   Basketball

INSTALLATION
Id_center   |   Id_installation |   Name            |   Id_type
------------|-------------------|-------------------|-----------
1           |   1               |   Swimming pool 1 |   1
1           |   2               |   Swimming pool 2 |   1
2           |   1               |   Tennis court 1  |   2

MEMBER
Id_center   |   Id_member   |   Name
------------|---------------|-------------
1           |   1           |   John Doe
2           |   1           |   Sarah Bole

And I'm thinking about the following ones:

SCHEDULE
Id_center   |   Id_installation |   Day_week    |   Time_begin  |   Time_end
------------|-------------------|---------------|---------------|------------
1           |   1               |   1           |   09:00       |   10:00
1           |   1               |   1           |   12:00       |   13:00
1           |   2               |   1           |   09:00       |   10:00
1           |   2               |   1           |   12:00       |   13:00
1           |   2               |   2           |   08:00       |   09:00
1           |   2               |   2           |   09:00       |   10:00

EXCLUDING DAYS
Id_center   |   Id_installation |   Date
------------|-------------------|-------------
1           |   1               |   06/10/2015
1           |   1               |   12/25/2015
1           |   2               |   12/25/2015

RESERVATION
Id_center   |   Id_installation |   Id_member   |   Date        |   Time_begin  |   Time_end
------------|-------------------|---------------|---------------|---------------|------------
1           |   2               |   2           |   05/28/2015  |   08:00       |   09:00
1           |   2               |   2           |   05/28/2015  |   09:00       |   10:00
1           |   1               |   1           |   05/25/2015  |   09:00       |   10:00

What would you add or modify?

Thanks.

Answer 1

Just at a quick glance, you seem to be issuing reservations in one-hour blocks. If so, consider doing away with the Time_End column.

Here's one idea:

create table Reservations(
    CenterID...,
    InstallationID...,
    MemberID ...,
    Date ...,
    Time_Begin ...
);

If, for instance, you make reservations up to six months in advance, you can make one entry for each available date as it crosses that six month threshold. It has the date, the earliest hour available on that date (from the Schedule table) and null for MemberID. The null means it's available. Since there are no other entries, it is available for the entire day. When a member makes a reservation, insert the record for that time or update the null record at that time and insert another null record with the time the reservation ends -- if there is not already another reservation for that time.

Like this. This is how each new day starts:

Center Install Member StartTime
====== ======= ====== ================
     1       1   null 05/28/2015 08:00

Member 14 makes a one hour reservation starting at 8am.

Center Install Member StartTime
====== ======= ====== ================
     1       1     14 05/28/2015 08:00
     1       1   null 05/28/2015 09:00

Member 27 makes a one hour reservation at noon:

Center Install Member StartTime
====== ======= ====== ================
     1       1     14 05/28/2015 08:00
     1       1   null 05/28/2015 09:00
     1       1     27 05/28/2015 12:00
     1       1   null 05/28/2015 13:00

This says member 1 has a reservation from 08-09, it's available from 09-12, member 27 has a reservation from 12-13, it's available from 13 on.

When member 42 want to reserve the 11am slot, only one insert is needed as the end of that slot, noon, already has a record.

Center Install Member StartTime
====== ======= ====== ================
     1       1     14 05/28/2015 08:00
     1       1   null 05/28/2015 09:00
     1       1     42 05/28/2015 11:00
     1       1     27 05/28/2015 12:00
     1       1   null 05/28/2015 13:00

This can be read thusly:

select  r.*, r1.StartTime as EndTime
from    Reservations r
left join Reservations r1
    on  r1.Center  = r.Center
    and r1.Install = r.Install
    and r1.StartTime =(
        select  Min( StartTime )
        from    Installation
        where   Center  = r.Center
            and Install = r.Install
            and trunc( StartTime ) = trunc( r.StartTime )
            and StartTime > r1.StartTime )
where   r.Center = :Center
    and r.Install = :Install
    and r.StartTime between :DayOfInterest and :DayOfInterest + 1;

This shows all entries for a particular day. Where Member is null, the time is available. Where EndTime is null, that reservation or availability goes to the end of the scheduled day. So just add to the filter:

    and r.Member is [not] null

to show only availabilities or only reservations.

The query may look a little scary at first glance, but it follows a consistent pattern for reading versioned tables.