So far I have used the following structure for a database dealing with internships of students:
School--< Class --< Teacher >--Person
School--< Class --< Internship
One school can have many classes (Year 1, Year 2,...)
Those classes can have many teachers who in turn can teach in more than one class.
One class can have many internships (one student could go there in autumn and another in spring).
We have deleted all the data after each year, but from now on we want to keep track of the students’ internships and therefore I have to keep all the information regarding the schools, classes and teachers and put the academic year somewhere.
Would you suggest to put it as a field into „class“ or create a join table like
School--< Class --< Class_AcademicYear (Join Table) --< Teacher >--Person
School--< Class --< Class_AcademicYear (Join Table) --< Internship
I somehow feel unwell by adding constantly „new“ classes to a school:
- School 1
- Year 1 (2016-2017)
- Year 1 (2017-2018)
- Year 2 (2016-2017)
The entity „class“ is – in this case - rather a group of teachers to which the internship is connected than a group of pupils. Therefore it is not really important that Year 1 (2016-2017) and Year 1 (2017-2018) are still somehow the same „Year 1“.
Each part of the information in class could change from year to year:
Year1 could become Year1+2 if the number of pupils decreases and/or the teachers may be different. Of course both could remain the same for several academic years.
Which approach would you suggest? Is there even a big difference? Or do you have a different idea?