Best approach to create a reservation system

Expected Behavior

  1. Student enters a desired range of times eg. Monday 10am, Tuesday 11am in the UI
  2. Business logic checks a) which tutors are active during at least one of those times and b) which of the active tutors are free, meaning they do not have other sessions booked.

I have created a not_available table, where I enter the times tutors are not active or offline. I could do the opposite as well, if it helps the logic.

eg. Here are the times when tutor Michelle is not available:

During her available times however, tutor Michelle has some slots already booked. These are in a separate weekly_slots table

My approach is to receive the list of desired slots from the student, and then

a) First determine which tutors are active during those times by checking the not_active table for each timestamp

b) Then take the list of active tutors and eliminate those that already have a session at the requested time by looking for the combination of tutor and time in the weekly_slots table.

My questions are:
a) Since both the not_available and the weekly_slots tables have a relation to a common tutor table, is there a query syntax that is more efficient and that allows me to say eg. for a given timestamp, give me the tutors that are active during that time AND do not have another session.

b) It it possible to make this more efficient by using the SQL IN predicate to check all timestamps or all tutors in one search, instead of looping through them one by one?

c) This is a classic “reservation system” problem, like Airbnb listings search. Is there a different approach to the DB schema that you recommend? I understand if not, as this is quite a large question, but maybe you have a resource, a previous implementation or an idea in mind.

The approach above brings the right results, but because it is looping through every timestamp and every tutor, it takes about 40 seconds. The intention is to show this on UI Builder as a list of available times, so it will be a bit slow and bad UX to wait 30 seconds every time the user changes their search criteria.

Any pointer towards a faster, more efficient search experience would be appreciated.

Hello @Andreas_Marinopoulos

Yes, doing it in a loop is not the best solution, I can recommend you to try to build a single query using AND/OR/IN combinations to load all the relevant records in a single request

Regards, Vlad

1 Like