How to efficiently store the free timing slots (of reservation) for restaurants in the reservation system?

Here’s my ERD for reference:

And the following is my algorithm to calculate the free/occupied timing slots for restaurants:

  1. A restaurant owner will add its restaurant info into my system + its opening hours

here’s the open hours’ structure for reference:

timings = [ { day: 1, name: 'Monday', hours: [ { title: 'Lunch', from: '14:00', totalSlots: 3 * 4, // 3 hours * 4 slots per hour }, { title: 'Dinner', from: '18:00', totalSlots: 12, }, ], }, ]; 
  1. I’ll start iterating from ‘current date & 14:00’ to 3 hours at the interval of 15mins. and for each iteration, I’ll create the “free reservation slot for this specific restaurant”. Then repeat the same process for the ‘Dinner’ timing. Repeat this process till 14 days (the exact number will depend on how much a restaurant is willing to take the reservation in advance)

The structure of reservation slots will look like this:

reservationSlots = [ {restaurantId: 1, date: 'date-14:00', freeTableIDs: [1, 2], occupiedTableIDs: [], status: 'free|blocked|occupied'}, {restaurantId: 1, date: 'date-14:15', freeTableIDs: [1, 2], occupiedTableIDs: [], status: 'free|blocked|occupied'}, {restaurantId: 1, date: 'date-14:30', freeTableIDs: [1, 2], occupiedTableIDs: [], status: 'free|blocked|occupied'}, {restaurantId: 1, date: 'date-14:45', freeTableIDs: [1, 2], occupiedTableIDs: [], status: 'free|blocked|occupied'}, {restaurantId: 1, date: 'date-15:00', freeTableIDs: [1, 2], occupiedTableIDs: [], status: 'free|blocked|occupied'} ... ... {restaurantId: 1, date: 'date-20:45', freeTableIDs: [1, 2], occupiedTableIDs: [], status: 'free|blocked|occupied'} ] 

So, for context, if a restaurant has a total of 336 (6 * 14 * 4) reservable slots per 14 days, then I’d have to iterate this exact number of times and add a total of 336 rows/document into the table/collection.

  1. At the end of every day I’ll (Cron job) delete the reservation slots (of every restaurant) for the previous day, and calculate + add the reservation slots of the new 14th (this number will be different for each restaurant) day of each restaurant.

Is there any way that I can reduce the number of iterations/rows?

How can I eliminate the need for the Cron job at the end of every day?

Or how can I structure/store my data properly?

Do you have any better solution in mind?

EDIT: I’m using MongoDB btw.

Existing Solutions:

https://resy.com/

https://www.exploretock.com/

https://opentable.com/

submitted by /u/imshahzayb
[link] [comments]


Go to Source of this post
Author Of this post: /u/imshahzayb
Title Of post: How to efficiently store the free timing slots (of reservation) for restaurants in the reservation system?
Author Link: {authorlink}