I am using PHP, MySQL to develop a website to be used in an educational institution.
One function of this is to allocate lectures when creating a batch. To allocate lectures to the starting batch, system will prompt available lectures based on their availability and qualifications. Then the course coordinator will take the decision.
My problem is how to check the availability of the lecture on a particular week day given time slot.(time slot is varying not fixed durations)
I am planning to keep lecture schedules in a table where it shows lecture_Id, Batch_Id, day, start_time, end_time, start_day , end_day.
Then when availability checking I need to write a complex query to check the available lecture_Id's. I couldn't figure out it yet.
Is there any other smart ways to do this?
Thanks