Mysql – Parking Spots Availability Design

database-designMySQL

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.

Best Answer

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

Related Solutions

Beginner design of biological sampling database

I would rename TreeData to measurements.

Also, unless you really need the Visits table, I wouldn't use it. If designed properly your database would look something like this if you included the visits table:

tree db design

Using the visits table would complicate some of the queries that you're wanting answered. For example, to answer number one you might use something like this in sql server:

select
     t.treeid,
     max(m.height)-min(m.height) as Growth
from trees t
     inner join measurements m
          on t.treeid=m.treeid
     inner join visits v
          on m.visitid=t.visitid
where v.date>=dateadd(y,-2,getdate())
group by t.treeid

without the visits table you could rewrite that like this:

select
     m.treeid,
     max(m.height)-min(m.height) as Growth
from measurements m
where m.date>=dateadd(y,-2,getdate())
group by m.treeid

Mongodb – Relational vs Non-Relational Database for Events Database

This seems a little outside the scope of a StackExchange question. However.....

NoSQL databases are, typically, build to resolve specific issues with the relational model. The most common issue addressed is scalability. However, because they're all designed to address different aspects of certain problems that some applications have with the relational model, there really isn't something you can say about all of them as a whole. Maybe you need to handle terabytes of data? Or maybe you need to handle a data schema that is very dynamic across the board and an EAV type schema would just kill performance? Maybe you want a data store that cares about availability more than consistency (see CAP theorem)? Each one is good at something that could be wildly different from what another is good at, while an RDBMS is a much more generalized database.

The answer I hear from people I trust on this topic is that if you don't already know why you need to abandon a traditional RDBMS for NoSQL, then you almost certainly should stick to an RDBMS. There's a reason it took 30 years before people began to seriously consider data store models beyond the RDBMS. If your queries are requiring features not available in MySQL/MariaDB, you may wish to consider another RDBMS that has a more robust feature set. That could be PostgreSQL, or Oracle, or MS SQL Server.

Your proposed schema here already suits the relational model fairly well. You may need a few junction tables for many-to-many relationships, but I don't think you necessarily will see many of those. If you properly size the DB, properly index your tables, and analyze your common queries -- things you have to do with NoSQL, too -- I don't see that you'd have a huge problem. You may even be able to get away with views for the most common queries.

Best Answer

Related Solutions

Beginner design of biological sampling database

Mongodb – Relational vs Non-Relational Database for Events Database

Related Question