4

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

2 Answers 2

2

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.

4
  • thank you for the answer. A concern that I have with this approach is a question I've already asked on SO. You can see it here: stackoverflow.com/questions/20018266/…. I hope to find another solution without requiring addition of a type column to the base table. As I said, triggers are one way, but they sound to be smelly. Mar 21, 2015 at 19:21
  • I assume you are the referring to the two reasons. Objection 1, Extensibility. Not sure where you are going here, is it the EmployeeType attribute in the Employee class in the oo-model you are conserned about? Why do you need it BTW? I think this is an example of OO/RM impedance mismatch. Objection 2 does not satisfy the constraints defined above. Mar 21, 2015 at 19:41
  • You mean that while a column is added to the common table, it's not necessary to add an equivalent property to the corresponding model, right? If that's the case, then I have no objection. But I still think there might be another solution. Mar 22, 2015 at 6:05
  • Yes, that is my point. OO and RM differs and we use different means to achieve what we want. Mar 22, 2015 at 6:16
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)
    );
2
  • So, in your design, it's the composite foreign key that prevents an entity with dual nature to be inserted into database? Am I right? Otherwise, if we only have a simple foreign key from PersonID to Persons table, it's still possible for a student record, to exist in teachers table too. Mar 22, 2015 at 6:09
  • 1
    The composite foreign key ensures the person is of the correct type (same value in the Person table). The check constraint guarantees that only students exist in the Student table and only teachers in Teacher. Without the check constraint, the same person could still exist on both Student and Teacher tables.
    – Dan Guzman
    Mar 22, 2015 at 12:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.