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

3 Answers 3


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:

  • I'd be tempted to call ClassStudents something like Attendees or similar.
    – Philᵀᴹ
    Commented Aug 6, 2012 at 21:07
  • Thanks for the great answer. I'm wondering if I referenced department in, say, the classes table, wouldn't it make the following type of query more difficult: 'select all teachers who work in a particular department' or is it a trade off between database design and the length/difficulty of some of the queries Commented Aug 6, 2012 at 21:13
  • Or ClassEnrollment @Phil?
    – jcolebrand
    Commented Aug 6, 2012 at 23:55
  • @rogersmith that is part of defining the requirements of what things are going to be needed in the initial requests.
    – jcolebrand
    Commented Aug 6, 2012 at 23:59
  • @jcolebrand Yup :)
    – Philᵀᴹ
    Commented Aug 7, 2012 at 0:22

some critique:

  1. I think one should try to describe the problem with proper words. if you tell us that something "has a list of" something then you do not describe the problem well. if you write that departments have a list of teachers I think that you want to say that teachers work as lecturer for some departments and they hold classes on some subjects for theses departements. only saying that "a teacher has a list of classes" raises to problems:

    • one does not know what this list has to do with the rest of the problem. of course i think this list should contain the names of the students that take classes of the teacher. but if we are working on a more difficult modeling problem my imagination may be wrong.

    • perhaps you guide me in the wrong direction because a list will mean a table containing the departements and the teachers.

    • it is hard to ask you more about these lists if these lists do not really exist but only exist in your imagination

  2. I think based on a good description one tries to create an entity relationship model

  3. then on applies some rules to transform the entity relationship model into tables

Because your description is not sufficient it is hard to criticize your design and propose furhter tables. But the departments_and_teachers-table looks very suspicious to me. Should this implement the lists of the departements that contains the teachers? Why is there an additional id? is it possible to have a departement-teacher pair more than once in this list with different department_and_teachers_id?


Why go through all of this when there is an excellent FREE hosted start of the art on-line LMS available?


Check it out!

  • 1
    Free but not open source. Commented Jan 14, 2015 at 23:54
  • 1
    John, do you have any interest in that product by any chance? Commented Jan 15, 2015 at 0:33
  • 2
    John, I am not down-voting as I, too, sometimes mention a product that I sell, but the rules are pretty clear that you need to disclose your affiliation to the product in your answer as noted here: dba.stackexchange.com/help/promotion (in the top section). That is probably part of why people are voting this down. The other reason is that the O.P. is more likely a student learning about DB design and someone actually building an LMS. Commented Jan 15, 2015 at 4:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.