0

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
----
person_unique_id
lastname
firstname
...

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


Subject table
----
subject_unique_id

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

schedule
----
schedule_unique_id
person_unique_id
teacher_unique_id
subject_unique_id
time
date
room
....

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 '16 at 16:53
  • @Magier To me it does. – johnny Nov 22 '16 at 17:25
  • So there is no question you are asking, is there? – Magier Nov 22 '16 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 '16 at 18:07
2

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:

http://www.studytonight.com/dbms/database-normalization.php

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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