All we need is an easy explanation of the problem, so here it is.
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
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;
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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.
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:
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂