Database model for a school timetable

Question

I am trying to design a DB model for a school timetable, and have some issues figuring out a model that would work with my requirements.

Domain entities:

Subject - something that is taught over the course of a year. eg: English, Programming, etc.

Group - a group of students.

Lesson - a recurring event where students are taught some specific subject. Example:

• every Monday at 10:00 group A is taught Programming.

The original requirements were very basic. CRUD for Subjects. CRUD for Groups. Ability to create recurring events that span some period of time. (note: no editing was allowed after creation).

The current model I use is:

Subjects
id:int
name:string

Groups
id:int
name:string

Lessons
id:int
name:string
startDate:date (ex: event starts recurring from Jan 1 2021)
endDate:date (ex: event recurring ends on Dec 31 2021)
startTime:time (ex: 10:00)
endTime:time (ex: 11:00)
dayOfWeek: flag enum that takes values Monday-Sunday
(we have more fields that are responsible for recurrence, but they are ommited as they are not very relevant to my question).

Currently, entire series of events is stored as a single row in a database.

This was working fine, but now additional requirements were added and I am having issues adjusting my model to accommodate them.

New requirements are:

1. ability to grade students for each lesson
2. ability to edit lessons (for example shift start/end time 1 hour for event series; change the day on which event occurs etc)
3. ability to shift a single event in a series (say teacher woke up sick and needs to move lecture from Monday to Tuesday just for this Monday)

#1 in theory is simple - we create a new table Grades that has fields

lessonId:int
userId:int
date:date
grade:int?

but it won't work once we take into account new requirement #2 (ability to edit lessons). let's say we have an event that occurs each Monday. an event occurred on Monday, Jan 1st. we graded some students, so we have some grades tied to that date. then we go to edit our lesson to occur on Tuesdays instead of Mondays. it is no longer possible to map existing grades to this lesson, as the dates no longer match.

#2 editing lessons seems like a pretty straightforward operation..until you consider grades. basically, the only issue with this requirement is the one described in #1

#3 no idea how to implement this, considering it should be compatible with other requirements.

I need help figuring out the database model that would satisfy those requirements.

Answer 1

From your description I take it that the lessons table also has a subject_id and a groups_id, which you merely forgot to put in your table column list.

Let's look at the tasks:

1. ability to grade students for each lesson

This is not easy. So far you have groups attending lessons. So to start this, you should add a students table to the database. Then, depending on whether a student can belong to more than one group or not, you'd either have the group ID in the student table or create a bridge table group_student.

Now, what does each "lesson" mean in this requirement? So far a lesson is a recurring event (your example: "every Monday at 10:00 group A is taught Programming"). That would mean you'd want a student_lesson table to be able to store the grade. The only problem I see here is that you could store a student_lesson row for a student that doesn't attend the lesson, if you stick to single IDs. Using composite IDs would solve this. The student_lesson table would have student_id, group_id, lesson_id, and grade. And id would have foreign keys on (student_id, group_id) and on (group_id, lesson_id).

If, however, lesson means a single lesson in the recurring lessons, then you need a single_lesson table, too.

2. ability to edit lessons (for example shift start/end time 1 hour for event series; change the day on which event occurs etc)

Should be no problem. These are just attributes that can be changed, anyway. Maybe you want a history table to see that the Tuesday lesson took place on Mondays until a month ago, but so far there is no requirement for this.

3. ability to shift a single event in a series (say teacher woke up sick and needs to move lecture from Monday to Tuesday just for this Monday)

Maybe you already have a single_lesson table because of requirement #1. Then each lesson occurrence already gets its one row with a date and sometimes that Monday would become a Tuesday. You could even store both dates, original date/time, new date/time. Maybe even a text for a reason.

If task #1 doesn't require a single_lesson table, because grades are per recurring lesson, then you only need a single_lesson_exception table for the exceptions where original date/time and new date/time are obligatory this time.