Finding Free Slots in a Booking System

Finding free slots in a booking system

Probably overkill for your application - but:

A relatively simple way of improving your searches at the expense of making the 'write' process more complicated, would be to change the Booking table to make it an 'Availability' table.

Add in a boolean column to indicate if the slot is free or booked (or better still put in the id of the customer who's booked it, and use 0 if the slot is free).

Start off with a single free slot, 1st Jan 2009 -> 31st Dec 20??

When you get a booking split the free slot into 3 (two inserts and one update), the booked slot and the two available slots.

Keep doing that and as the time frame gets more fragmented the booking process will consist of one of the following:

• Assigning an entire 'available slot' to someone (one update)
• Splitting an 'available slot' into two (one update and one insert)
• Splitting a slot into 3 (as above) if someone books the middle section out of an available slot.

That's not incredibly complicated to manage and the search process becomes a simple query: finding any slots in the required time frame that are available (booked=false or customerid=0, whichever way you go with it) where enddate - startdate >= the number of days you want.

It doubles the size of the booking/availability table, and makes bookings less simple, but the trade off is that the search process is about as easy as it gets.

SQL - Find free slots for booking system

I assume that you have some kind of Rooms table

http://sqlfiddle.com/#!9/fe977/1

SELECT r.*
FROM rooms r
LEFT JOIN Bookings b
ON r.id = b.post_id
   AND (
     (b.StartTime <= '2018-10-01'
   AND b.EndTime >= '2018-10-01')
     OR
     (b.StartTime >= '2018-10-01'
   AND b.StartTime <= '2018-10-15')
   )     
WHERE b.id IS NULL

UPDATE I am still not sure if I've got your goal. But here is another approach. If you want start date to be flexible, I would recommend to set calendar table for all dates in the year. This will allow to avoid unnecessary calculations when you run query.

Here is a fiddle: http://sqlfiddle.com/#!9/29926/3

SELECT r.*,
c.date
FROM rooms r
LEFT JOIN calendar c
ON c.date BETWEEN '2018-10-01' AND '2018-10-15'
LEFT JOIN Bookings b
ON r.id = b.post_id
   AND (
     (b.StartTime <= c.date
   AND b.EndTime >= c.date)
     OR
     (b.StartTime >= c.date
   AND b.StartTime <= (c.date + INTERVAL 3 MONTH))
   )     
WHERE b.id IS NULL

UPDATE 2 Combining with @Strawberry answer I guess we can modify query to:

http://sqlfiddle.com/#!9/29926/5

SELECT r.*,
c.date
FROM rooms r
LEFT JOIN calendar c
ON c.date BETWEEN '2018-10-01' AND '2018-10-15'
LEFT JOIN Bookings b
ON r.id = b.post_id
   AND b.StartTime <= (c.date + INTERVAL 3 MONTH)
   AND b.EndTime >= c.date     
WHERE b.id IS NULL

Calculating number of bookings per slot in booking system

Does something like this return the appropriate values?

SELECT Agg.T, COUNT(*) 
FROM(SELECT s.T FROM CollectionSchedule as c
INNER JOIN ScheduleTimes as s 
ON s.T >= c.CollectionStart AND s.T < c.CollectionEnd) as Agg
GROUP BY Agg.T;

SQL Fiddle

---

Related Topics