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;
