I have a reservation table that has
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
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.