Search code examples
databasedatabase-normalization

Can a normalised database have 2 link tables linking 2 other tables to a single primary key in another?

Here are the tables that I currently have:

Student(StudentID, SurName, OtherNames, TargetGrade etc.)
Teacher(TeacherID, SurName, OtherNames etc.)
Class(ClassID, Description)

A student can be in multiple classes and each class has many students. A Class can have multiple teachers and each teacher teaches many classes. So to deal with these 2 many to many relationships, I used 2 lookup tables:

StudentClass(StudentID,ClassID)
TeacherClass(TeacherID,ClassID)

The reason why I am not sure whether this is normalised is because if you remove the class table (Which seems a little redundant but could help with the identification of classes), you would be left with a many to many relationship between StudentClass and TeacherClass. Is this Normalised?

Thanks

-----------        ----------       ----------         -------------         -----------
|         |       /|        |\      |        |        /|           |\        |         |
| Student |------<-| Student|->-----|  Class |-------<-| Class -   |->-------| Teacher |
|         |       \| -Class |/      |        |        \|   Teacher |/        |         |
-----------        ----------       ----------         -------------         -----------

Edit: For this system Class would not be required to store anything other really than a description. Test Scores are stored in a separate table linked to students.

Solution

  • What you have here is definitely in 3rd NF. Just a remark concerning what you are saying about scores: you should not miss the fact that each class is associated to 1 or many exams, so you have typically a one-to-many relation between class and exams (could be 0 to many for classes without exams?).

    Then, you can consider that a score is held by a relation\table between the 'studentClass' table and the 'Exams' table. Depending on your business rules, a 'studentClass' record can be linked to one or multiple scores for a specific exam, depending if you can pass the exam once or multiple times.