Expected Behavior
- Student enters a desired range of times eg. Monday 10am, Tuesday 11am in the UI
- 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.