I've seen lots of ERDs of schools, teachers, and all the junction/associative tables. They always have three tables:
- 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;