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. Students
and 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 Students
, and 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).