Finding time period based overlaps for Parking Spot booking application

The Problem

I recently came across a problem where I had to design parking spot booking app.

Given a parking spot and time period he/she needs, A user should be able to book a particular parking spot. And before booking we need to make sure that someone else has not already booked in that spot.

For example:

Say parking spot A1 in Floor No 1, A user has booked already for July 25th from 4pm to 5pm.

Now another user is trying to book the same spot for July 25th 3:30 pm to 4:30 pm. This should not be allowed as it will overlap with existing booking time.

So how do we solve this?

At first we need to understand, how many types of time period overlap can happen. There is actually 3 types of it.

We have 2 time periods in the below images namely BLUE and RED. The bars correspond to PM. So 3 is 3PM.

1. When BLUE time period starts at RED time period start or between RED start time and RED end time period

Example 1

RED bar is 3pm to 7pm and BLUE is 4pm to 5pm

Example 2

Condition Check for Overlap : if condition is true it means Overlap exists

RED.START_TIME <= BLUE.START_TIME < RED.END_TIME

2. When BLUE time period ends in between RED start and end time period

Example 1

Example 2

Condition Check for Overlap : if condition is true it means Overlap exists

RED.START_TIME < BLUE.END_TIME < RED.END_TIME

3. When BLUE time period ends after RED end time period

Example

Condition Check for Overlap : if condition is true it means Overlap exists

BLUE.END_TIME > RED.END_TIME and BLUE.START_TIME < RED.END_TIME

The Database design

We have 3 tables for this application

1. User
2. Spot
3. Booking

User Table

Spot Table

Booking Table

So from above booking table, its clear that A1 is not available between 4pm and 5pm on July 25th.

Finding available / empty spots for given time period

Lets see the steps involved in finding empty spots available between 4:30pm and 5:30 pm

First we run query to find all the spot ids which are occupied during the given time

select s.id from spot s join booking b on s.id=b.spot_id 
where 
(start_time<='2024-07-25 04:30:00' and '2024-07-25 04:30:00' < end_time)
or (start_time<'2024-07-25 05:30:00' and end_time<'2024-07-25 05:30:00')
or ('2024-07-25 05:30:00'>end_time and '2024-07-25 04:30:00'<end_time);

Now we need to avoid these ids get rest of spot ids using this as subquery

select * from spot where id not in (select s.id from spot s join booking b on s.id=b.spot_id 
where 
(start_time<='2024-07-25 04:30:00' and '2024-07-25 04:30:00' < end_time)
or (start_time<'2024-07-25 05:30:00' and end_time<'2024-07-25 05:30:00')
or ('2024-07-25 05:30:00'>end_time and '2024-07-25 04:30:00'<end_time));

You can see that it only returned the free spot information for the given time period

Thanks for reading.