Imagine a doctor working at some hospital, he has a weekly working schedule and can update the schedule at a given date. So I have to implement a functionality where users can search and find docker's today's schedule.
I've created a schedules model with
- date - Date
- day- Weekday name
- startTime - time
- endTime - time
- status - active|inactive
the table can have a date or day, not both.
So when I have to find a duty schedule for a date for example - 19 July 2021 (Monday) then I do following
- find all schedules querying
date - then grab the ids of all schedules returned from the above query
- find all schedules with day (Monday, taken from date) and skip the above ids But I feel there must be a better way of doing this. This works well but wherever I want to do something about a doctor's duty schedule I have to run the above queries which do not feel good in terms of performance.
What are some ways I can achieve the same?