SQL Server – How to Enforce Data Consistency in One-to-One Relationship

database-designinheritancesql serversubtypes

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).

Best Answer

Add a student/teacher attribute to Person. Since this attribute is dependent of the key in Person (whatever that is), no Person can be both a Teacher and a Student. Now it is a matter of guaranteeing that a person whose type is student is not added to Teacher etc.

For DBMS:s that support queries in check constraints you can do something like:

ALTER TABLE Student Add constraint ...
         CHECK ( (select type 
                  from person p
                  where p.<key> = <key>) = 'Student' )

If you DBMS does not support this type of construction you can add a super key in Person consisting of the primary key + the type attribute. Add the type attribute to Teacher and Student, add a check constraint that guarantees the type in those "subtables", and a foreign key that includes the type attribute:

ALTER TABLE Person ADD COLUMN type_attribute varchar(..) not null;
ALTER TABLE Person ADD CONSTRAINT ... UNIQUE (<key>, type_attribute);

ALTER TABLE Student ADD COLUMN type_attribute varchar(..) not null;
ALTER TABLE Student ADD CONSTRAINT ... CHECK (type_attribute = 'Student')
ALTER TABLE Student ADD CONSTRAINT ... 
     FOREIGN KEY (<key>, type_attribute)
     REFERENCES Person (<key>, type_attribute);

Now it is not possible to add a student as a teacher and the other way around. That there really is a student/teacher for a person with that attribute has to be guaranteed through the transaction that adds the information.

Related Question