Date and Time range Overlapping problem and Checking for availibility

Working with Data
, , ,
1

Hi all!!

I have a doubt regarding schema design and data modelling
I Want to design a schema for a project related to Reservation where you have to select a date range with time slots and it will return with the available slots in different Lots and you also have more than one spot in a single Lot for your reservation to understand this let’s take a real world scenario…

  • A parking lot has 300 or more parking spots
  • And each parking spots can be booked for different date/time range
  • But if the slot is booked for the future then it means until that time reaches that spot will be available right?
  • Now I was thinking about how I can design a schema or what approach I should follow to solve this problem. Also it takes very less time to read and give the result of available spots in different parking lots.
  • I am not choosing to store the booking records inside an array because if we do so when we will be searching for available Slots then it has to go to each and every Lot and in each Lot it will loop through each slot for checking whether the slot is available or not.

Actually I am looking for a Scalable and Efficient solution so the users don’t have to wait more to see the result when our database grows.

I am thinking of making a Reservation schema and do child parent referencing with Start and End date with time but when we search for availability how can I do so?

Can anyone help me with this problem?

2

Hi @Ayanabha_Paul ,

I have thought about this problem for a few times. I came to a conclusion is that it make sense to have a document for a specific range of time and the taken lots there.

I am not sure what kind of timeframes have you thought about in your case, but lets say that we book a place for at least 30 min.

If someone booked a spot in our lot starting 10:00 AM it will have a document of a taken slot in a 30 min document for this time:

{
 _id : .... ,
 parkingId : ... ,
 startTime : ISODate("2022-03-13T10:00"),
 endTime : ISODate("2022-03-13T10:30"),
 lots : [ 1 , 3, 5],
 openSlots : 297
}

If we index { lots : 1, startTime : 1 , endTime : 1} and {startTime : 1 , endTime : 1} we can check, for a specific time frame what are the available parking spots and what are the taken one. If we want to see when parking lot 1 is taken we can just query “{lots : 1}” and sort by “startTime” and we will see all the taken spots. Any other spot is available.

The only thing here is that if someone takes a slot for a full day you need to either have a fullDay document with those timeframes or insert/update 48 documents of 30 min docs…

Checking what is available can be done by querying the taken spots for a specific timeframes and anything that is not there is free basically.

Let me know if this design make sense to you.

Thanks
Pavel