I have 2 tables, T_PersonHeader and T_PersonDetails
My scenario is, I have 2 entities, Student and Teacher.
For T_PersonHeader, I only store the following: PersonID, PersonType (Student or Teacher), Status (Active/Inactive)
Each entities have their own attributes and all the attributes are stored in T_PersonDetails
When I create a Teacher record and a Student record I will have 2 rows in the T_PersonDetails table.
I have a screen that loads the attributes for Person.
If Student, I should be able to find out the Teacher that the Student links to and vice-versa.
How do I link this at a database level?
I can only think of merge Teacher and Student record into new record (let screen loads from this record). But I want to keep Student record and Teacher record separately, as a Teacher can become Active again.
I want to know what is the current best database practice for handling this?