How to enforce data consistency across one-to-one relationship in an inheritance hierarchy

Question

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

Answer 1

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.

Answer 2

One method is to introduce a PersonType attribute. Using this as a composite key along with a check constraint and foreign key in the Student and Teacher tables will ensure a row for a given person exists in only one of those entities and in the proper table. I can't speak to how well this implementation will get along with EF.

There are similar approaches using alternate keys (unique constraints); a foreign key can reference unique constraint/index columns, not just those of a primary key.

CREATE TABLE dbo.PersonType(
      PersonTypeCode char(1) NOT NULL
        CONSTRAINT PK_PersonType PRIMARY KEY
    , TypeName varchar(30) NOT NULL
    );

INSERT INTO dbo.PersonType (PersonTypeCode, TypeName)
    VALUES('S', 'Student'), ('T', 'Teacher');

CREATE TABLE dbo.Person(
      PersonID int NOT NULL
    , PersonTypeCode char(1) NOT NULL
        CONSTRAINT FK_Person_PersonType
        FOREIGN KEY REFERENCES dbo.PersonType(PersonTypeCode)
    , FirstName varchar(50) NOT NULL
    , LastName varchar(50) NOT NULL
    , SocialSecurityNumber char(11) NOT NULL
        CONSTRAINT UN_Person_SocialSecurityNumber UNIQUE
    , Phone varchar(20) NOT NULL
    , Email nvarchar(255) NOT NULL
    , CONSTRAINT PK_Person
        PRIMARY KEY(PersonID, PersonTypeCode)
    );

CREATE TABLE dbo.Student(
      PersonID int NOT NULL
    , PersonTypeCode char(1) NOT NULL DEFAULT 'S'
        CONSTRAINT CK_Student_PersonType CHECK (PersonTypeCode = 'S')
    , Grade char(2) NOT NULL
    , CONSTRAINT PK_Student 
        PRIMARY KEY(PersonID, PersonTypeCode)
    , CONSTRAINT FK_Student_Person
        FOREIGN KEY(PersonID, PersonTypeCode)
        REFERENCES dbo.Person(PersonID, PersonTypeCode)
    );

CREATE TABLE dbo.Teacher(
      PersonID int NOT NULL
    , PersonTypeCode char(1) NOT NULL DEFAULT 'T'
        CONSTRAINT CK_Teacher_PersonType CHECK (PersonTypeCode = 'T')
    , Specialty varchar(20) NOT NULL
    , CONSTRAINT PK_Teacher 
        PRIMARY KEY(PersonID, PersonTypeCode)
    , CONSTRAINT FK_Teacher_Person
        FOREIGN KEY(PersonID, PersonTypeCode)
        REFERENCES dbo.Person(PersonID, PersonTypeCode)
    );