In a school database, do I need to have repeating data when a student can also be a teacher

database-designrelations

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;

Best Answer

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:

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

Related Question