Database schema design for school system

database-designschema

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(department_id, department_name)

  • teachers(teacher_id, teacher_name);

  • departments_and_teachers(department_and_teachers_id, department_id (FK), teacher_id (FK))

  • student(student_id, student_name)

  • 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))

Best Answer

To your specific question of (easily satisfied) plausibility, the answer is yes. Since you seem to be looking for a broader critique. You seem to be mostly on the right track, but a few things are making your description overly complex.

It looks like your Classes table is more like what I would consider a course to be and your departments_teachers_and_classes is more of what I would expect a Classes table to be. A course would be Math, but a class would be the Math course taught by a particular teacher during a particular school term. A class is like an instance of a course.

To carry this change through, your departments_teachers_classes_and_students could simply be ClassStudents.

It seems as though you are using the department concept on several different levels. You should decide where the department belongs and stop referencing it everywhere else. You've said that a teacher can teach for multiple departments, so we can't put the department in the teachers table. This leaves Classes and Courses. Whichever you decide, it need only have a foreign key to the Departments table (unless you decide that one can be in multiple Departments). This eliminates the Departments_and_teachers table.

Teachers_and_Students could be replaced with a teacher foreign key in the Classes table and students/classes foreign keys in a ClassStudents table.

In short, for the portion of the design you have described, you probably need the following tables:

Departments
Teachers
Students
Courses
Classes
ClassStudents
Terms
Related Question