Consider this design for a typical school database:
Person: ----------------- FirstName LastName SocialSecurityNumber Phone Email Student: ----------------- Grade Teacher: ----------------- Specialty
As you see, there are three tables in this design. One table holds the general information about the abstraction, and two other tables hold specific data about concrete entities.
Teachers tables have one-to-one relationship with
Persons table. So far there is no problem.
However, based on this design, there is this possibility that we have a record with Id 30 in
Persons table, and because of any reason (like manual script execution against database) we insert two records with the same Id in
Teachers tables. This way, a person with Id 30 is both a teacher, and a student at the same time.
Well, that makes sense in the context of a school database. But there are some contexts that derived tables are mutually exclusive, thus an entity from one concrete type can not logically be an entity from the opposite type too.
How can I prevent overlap Id insertion across derived tables in hierarchical database designs? I know I can achieve that with triggers, but I think code smell in using triggers.
Notes: I'm using SQL Server, and Entity Framework calls this design Table per Type (TPT).