I am trying to create a database to store information about a school.
I need to store each department in the school. Each department then has a list of teachers. Each teacher has a list of classes and each class has a list of students.
The department name will be unique. However, teachers may span across departments. Another thing to note is that if teacher
A teaches a class called 'Maths' and teacher
B also teaches a class called 'Maths' then these should be different entities in the database (I think). For example, I may want to get a list of students that take the class 'Math' with 'teacher A' only.
So far I have this:
departments_and_teachers(department_and_teachers_id, department_id (FK), teacher_id (FK))
- teachers_and_students(department_and_teachers_id, student_id);
which I think is good database design? However, it doesn't include any information about the 'classes' and I'm not sure what the best way of altering the schema would be. For each department and teacher combo I need to add a list of classes that are taught. Is it plausible to do something like this:
- classes(class_id, class_name)
- departments_teachers_and_classes(dtc_id, department_and_teachers_id (FK), class_id(FK));
- department_teachers_classes_and_students(student_id, dtc_id)
EDIT - new suggestion based on answer received
- departments(department_id, department_name)
- teachers(teacher_id, teacher_name)
- classes(class_id, class_name, teacher_id (PK), department_id (PK))
- student(student_id, student_name);
- classStudents(student_id (PK), class_id (PK))