Foreign key to access multiple tables

[chandimak]

I have a person table which specifies common attributes of sub tables teacher, student and staff. A person can be either teacher, student or both. I use the same primary key in sub tables which access the person table as foreign key (teacher_id, student_id is the same as of person_id). [ Refer the attachment ]

Then there is a separate_exam table where an exam is mentioned which can be done by a person who is a teacher or a student but not a staff. This accesses to the same exam. So, I require to connect separate_exam table to teacher and student. I thought to do this using person_id which is common to both teacher and student, using it as a primary key as well as foreign keys to both tables teacher and student.

But this cannot be done using relationships as it seems that it needs to have 2 foreign keys to teacher and student as I found (I thought to do this using person_id as foreign key as both teacher_id and student_id is the same as the value of person_id - separate_exam may have zero or one teacher or student).

Design as I thought,
separate_exam(exam_id, person_id, result) person_id PK,FK
(Not, as appeared on attachment as both teacher_id, student_id as PK,FK1 and PK, FK2)

I need to know whether there is any other way to achieve this. Your suggestions are highly appreciated.

[MartinPlatt]

I don't think that when taking an exam a teacher is actually a teach, they're a student taking the exam, and that person happens also to be a teacher (if that makes sense?) In other words the person takes the exam, who can be either a teacher, a student or staff. I think you're also confused there. Is a teacher staff? What data will go in those tables, or are they really all attributes of the person entity?

If we ignore what I said, I think you need a foreign key on the teacher table back to person, and the same for student, and staff. Then the exam would have three FKs for teacherid, studentid, and staffid. Those Ids would imply a person Id, the way you have it modelled. From a database design persective, that isn't very clean or clear. Think about changing it to a person takes and exam, then think about how much data is required that is not person data, but relates only to teacher, student, or staff - if there is data that isn't person data, then it is appropriate to have separate tables.

I saw another question like this earlier, so I'm guess that there is an assignment on for this ;)