Parking Spots Availability Design

Question

I have a reservation table that has from_date, to_date, and parking_location_id. What is the best way to calculate location spots availability for each location and each hour?

I tried to write a query that calculates availability from reservation table but this query is very slow when the period is long even though the from_date and to_date columns are indexed.

Another approach to speed things up was to create a location availability table

The contains all locations and all dates and hours in a range of years and whenever a reservation is saved a trigger updates the reserved spots in this table. However, in this approach, the table rows size grows exponentially and we need to add a range of dates with location ids whenever a new location is added. Any ideas how can I improve this design or what alternatives do we have?

The slow query tries to calculate number of reserved spots per date and then we find the max number in that date range.

select  sum(r.number_of_slots) as reserved_spots
from location l 
inner JOIN reservation  r on r.location_id=  l.id  , time_dimension t
where t.db_date between r.from_date and r.to_date and 
 t.db_date < r.to_Date and t.db_date > r.from_date  t.id=type_id and group by db_date;

Time dimension table contains a list of all dates in 10 years.

Answer 1

I'd suggest to use a calendar or schedule table.

In my example I've set up a 3-day table calendar with intervals of one hour:

(Do a google search to find out some date series generator, you'll find a lot)

    Hours table
===================
2017/01/01 00:00:00
2017/01/01 01:00:00
2017/01/01 02:00:00
2017/01/01 03:00:00
2017/01/01 04:00:00
2017/01/01 05:00:00
...
...

Then simply LEFT JOIN your table with the calendar table and get NULL rows:

    Hours table               Reservations
===================        ===================
2017/01/01 00:00:00        2017/01/01 00:00:00
2017/01/01 01:00:00  LEFT  2017/01/01 01:00:00
2017/01/01 02:00:00  JOIN  NULL                 <-- Free
2017/01/01 03:00:00        2017/01/01 03:00:00
2017/01/01 04:00:00        NULL                 <-- Free
2017/01/01 05:00:00        NULL                 <-- Free

SELECT    hdate 
FROM      calendar
LEFT JOIN reservations
ON        hdate = from_date
WHERE     parking_id is null;

dbfiddle here