Hey guys, I'm making a website for a private tuition classes. Here, the concept is that a teacher goes to teach to the student's house. There are many teachers operating this way teaching many students at their home and all this is scheduled by the classes. They want a system to manage this whole thing.

The real life scenario is like:-the classes decides the weekdays on which lectures will be conducted throughout the semester e.g. in this semester all the lecture that student S will have which are taught by teacher T will be conducted on say Monday and Wednesday of every week. Only the weekdays are decided and no other details are predetermined. Student, Teacher and Subject have their own IDs (stu_id, tch_id, sub_id) and they together are identified by the primary key assignment_id. For each pair of student, teacher and subject there is an assignment_id and these details about each assignment i.e. which teacher teaches which subject to which student, are stored in another table “assignments”.

Also we have to give a notification to student and teacher about the lectures which are to be conducted and let them decide if the lecture will be conducted on the decided day of the week or some other day after that day. And then insert the data into “lecture_schedule” table about the conducted lecture i.e. assignment_id, duration, chapters covered, etc. In case the lecture gets rescheduled, the reason is stored in a separate table with the reason of rescheduling and required details about the adjustment. There is one more possibility that the day of the week on which the lecture is to be conducted may change i.e. if lecture is conducted on Monday, I want it to be conducted on Friday for the whole remaining semester.

I want to create a database schema using which I can handle all these tasks in an efficient way. I want the details of the lecture which are conducted to be stored in a way that I can manage it well (number of lectures for a particular assignment are not fixed). And I want a mechanism using which I can change the weekday as well for a particular assignment.

Solution I thought of is like this:-

I have a table “assignment_days” whose schema is like assignment_days(assignment_id, mon, tue, wed, thu, fri, sat , sun). If I want the lectures to be conducted on Monday and Friday, then I make all other values null and put a value in the table for mon and fri such that on Monday and Friday, the integer value in that field will change to 7 and if the value is (value%7 == 0) then I send the notification to respective student and teacher. If I want the lecture day to be Tuesday instead of Monday then I take the number of remaining days for the next lecture and then subtract it from 7 e.g. if today is Wednesday and I’m replacing Monday with Tuesday then next lecture of Tuesday is after 6 days. So I put 7-6=1 into the Tuesday field for that assignment_id so that on the next Tuesday the number in that field will be 7 and notification will be shown.

For storing the lecture details, I’ve made a table “lecture_schedule” in which I’m storing assignment_id, lecture number, duration of lecture, and many other details of the each lecture conducted. But there are many lectures conducted by many teachers in a single semester so I’m worried that the whole table will be very bulky and the table will be difficult to manage. If you can think of a criteria based on which I can divide the lecture details into several tables then please tell me. Also if you have a better solution for managing this system then please let me know.

Tasks I want to do:-

1. Store the lecture details in an efficient way.
2. Manage and store scheduling and sending notifications.
3. Store the data about rescheduling and make changes in database accordingly.

Can anyone give a solution for this please?

Thanks in advance!