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.