I've seen lots of ERDs of schools, teachers, and all the junction/associative tables. They always have three tables:

  • Teachers
  • Students
  • Associative table betweeen them, maybe subjects of something like that.

it is assumed that a teacher cannot be a student and vice-versa. But what if a student can also be a teacher (a non-grade school, obviously)? If I keep the traditional view, then I end up with repeating data between teachers and students, such as the name. What am I missing?

Would it be bad design to have:

Person table

Teacher table
subject_unique_id #if I want to know what this person can teach.

Subject table

I would still have to have an associative table for all the many-to-many relationships like:


Then I could do something like:

SELECT * FROM Person p, Teacher T, Subject s
WHERE p.person_unique_id = t.person_unique_id
AND t.subject_unique_id = s.subject_unique_id
AND p._person_unique_id = 12;
  • What is " the traditional view"? What are you asking? Your database design looks as if it might work out for what you want to, doesn't it?
    – Magier
    Nov 22, 2016 at 16:53
  • @Magier To me it does.
    – johnny
    Nov 22, 2016 at 17:25
  • So there is no question you are asking, is there?
    – Magier
    Nov 22, 2016 at 17:44
  • @Magier Yes, Is what I am saying making sense? Is this a bad design? Am I missing something? The "traditional" view is the two are separate tables with two separate sets of information that only relate via a junction.
    – johnny
    Nov 22, 2016 at 18:07

1 Answer 1


What you are saying makes sense, yes.

One thing is that you actually can't select students without excluding Persons that are teachers... And even if you did, the students that are also teachers would be missing without further efforts. So maybe it makes sense to have a students table as well to assign Persons that are students the same way you did it with teachers.

Meaning: P1 is a teacher. P2 is a Student. ...

There are for sure more possible solutions. I think it all depends on the requirement you have and any solution must stand.

It's possible that a student also sometimes is a teacher and maybe even teaches the course where he or she is a Student... Or Maybe the school Administration used a database for different school let's say elementary same time as for parents night school. Maybe then additional flexibility is required.

Basically just go ahead and think about normalization to solve your requirement and get to a valuable Model. Using m:n tables to solve this just follows the normalization rules.

Normalization howto example:


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.