This article describes about the inheritance in the relational database and how we can handle that in the Entity Framework. First let me discuss about the database inheritance.

I searched the Internet and found there are databases which support inheritance natively. PostgreSQL has this feature, something similar to this.

INHERITS FROM (tablename[, othertable...])

 

Then I read few chapters from some books. I have given the same exact points that I read from the books. I made some statements bold as I have a personal strong vertical nodding with them. :p

From the Book SQL Server MVP Deep Dives

There’s both a technical and cultural impedance mismatch between the object world

and the database world. Relational databases don’t readily represent inheritance……

Too often the solution is to use an application layer object-relational mapper, such

as NHibernate or the Entity Framework. This provides a poor abstraction for the database……

 

 

From the Book Beginning Database design from Novice to Professional (Apress)

Inheritance in a data model is not as common as you might think at first. Humans are very good at categorizing things into hierarchies, and once people get hold of the idea of inheritance in data modeling, there can be a temptation to use it everywhere……..

“Is A a type of B?”

only meant that using inheritance might be a possible way of making sense of a problem.

 

I strongly believe on what is mentioned above. We do not need to use inheritance structure unless we have a very specific need for it. Because the inheritance in the OOP is very different from the inheritance (or what we call the inheritance relationship) in the database.

Let’s have a simple example of Person and  Student and Lecturer.

In the context of OOP, most of the cases we have our super classes declared as abstract. So we could have the code as like this.

abstract class Person { }

class Student : Person { }

class Lecturer : Person { }

 

But in the databases we do not have something called abstract table. Look at this structure which has the inheritance modeled in the database level.

 

As you can see, though this looks like as it’s having some kind of inheritance it is not clean and not pure.

There can be only person who is neither a student nor a lecturer. There can be people who are students and lectures (of course this makes sense but think a scenario where a person can be either of them, not both)

And just by giving an Id we cannot say what type of a person he is. (we need to perform an OUTER JOIN for this) Just to make things more accurate we have to enforce some constraints.

First create another table with two columns (for minimum) with PersonTypeID and PersonType.

 

Add the PersonTypeID as Foreign Key (FK) to the Person table with NOT NULL constraint (Here we enforce that one person can be either Student or Lecturer)

So here Jonny and Russel are Lecturers and Thuru and Ann are students.

Still there are some works we have to do. Let me explain, we added the PersonTypeID column with as FK and enforced that a person can belong to one type but we didn’t enforce that a person cannot be in both. I can still have Thuru in lecturer table.

That means I have enforced the rule only in the Person table not in the Student table and Lecturer table.

To overcome this problem, we have to refer the PersonTypeID column in the Person table with the Student table and Lecturer table.

So we add a column in the Student table and in the Lecturer table which refers to the PersonTypeID of the Person table. But we cannot create FK in the student table or in the lecturer table since the PersonTypeID of the Person table is not unique. (as we know to the FK referring column should be unique) So to make them unique we have to create a unique constraint in the Person table.

Like the following.

ALTER TABLE Person ADD CONSTRAINT UQ_PersonID_Type UNIQUE (PersonId,PersonTypeID)

Here the unique key can be constructed with the combination of PersonID and PersonTypeID. (or you can make the combination as Primary Key (PK) as well)

Then we can have the StudentID and PersonTypeID columns in the Student table and LecturerID and PersonTypeID columns in the Lecturer table as FKs of the PersonID and PersonTypeID of the Person table.

Here Student has the PersonTypeID = 1 and Lecturer has the PersonTypeID = 2. This is not going to change, so when creating the Student table (or Lecturer table) we can use the PERSISTED keyword in TSQL

 

CREATE TABLE Student (

StudentID INT PRIMARY KEY,

PersonTypeID AS 1 PERSISTED,

GPA FLOAT NOT NULL,

CONSTRAINT FK_Student FOREIGN KEY (StudentID,PersonTypeID) REFERENCES Person(PersonID,PersonTypeID)

 

Here I have used the computed columns for the PersonTypeID, you can use the CHECK constraint as well. As our intention is to not allowing any other values than the corresponding PersonTypeID in the table.

Here is the complete diagram of the tables.

The complete TSQL Code

CREATE TABLE PersonType (

  PersonTypeID INT IDENTITY (1,10) NOT NULL PRIMARY KEY,

  PersonTypeName VARCHAR(50) UNIQUE

  )

 

CREATE TABLE Person (

PersonID INT IDENTITY (1,1) NOT NULL,

FirstName VARCHAR(50) NOT NULL,

LastName VARCHAR(50),

Age INT NOT NULL,

PersonTypeID INT NOT NULL,

CONSTRAINT FK_PersonType FOREIGN KEY (PersonTypeID) REFERENCES PersonType(PersonTypeID)

)

ALTER TABLE Person

    ADD CONSTRAINT PK_PersonID PRIMARY KEY (PersonID)

   
ALTER TABLE Person

ADD CONSTRAINT UQ_ID_Type UNIQUE (PersonID,PersonTypeID)

 
 
CREATE TABLE Student (

  StudentID INT NOT NULL PRIMARY KEY,

  RegNo VARCHAR(50) NOT NULL,

  GPA FLOAT NOT NULL,

  PersonTypeID AS 1 PERSISTED,

  CONSTRAINT FK_Student FOREIGN KEY (StudentID,PersonTypeID) REFERENCES Person(PersonID,PersonTypeID)

  )

CREATE TABLE Lecturer (

  LecturerID INT NOT NULL PRIMARY KEY,

  EmpID VARCHAR(50) NOT NULL,

  SALARY MONEY NOT NULL,

  PersonTypeID AS 2 PERSISTED,

  CONSTRAINT FK_Lect FOREIGN KEY (LecturerID,PersonTypeID) REFERENCES Person(PersonID,PersonTypeID)

  )

So implementing the inheritance in the database is tricky and we have to be carful in the CRUD operations. It is recommended to write proper SPs with transactions to handle the situation.

Because though we have conceptually implemented the rules, there can be a situation where there is a Student record in the Person table without the corresponding row in the Student table. (that means we do not have the abstract behavior in the table)

Now let’s take a look where a person can be a student as well as a Lecturer. In this case we have the Person to PersonType as N : M relationship. Where a person can take many person types and one person type can have many persons (sorry here I have to put people, but that creates ambiguity in the context, so let us have plural of person as persons 😀 )

So we had to have a mapping table for the PersonID and the PersonTypeID.

That is the difference we are going to have. And the foreign key constraints of the Student table and the Lecturer table refers the mapping table.

So we can define more flexible inheritance scenarios in the relational databases, but this is full in the context of the database. And I still go with the first saying mentioned in the top of this post. Do not use unless you have a proper need for inheritance.

Some say they go for this kind of database design considering the future extendibility. Database extendibility is big area as far as I know, defining an inheritance structure is not enough for that. And I have very few ideas about that so I don’t discuss that here. Additional reading : http://www.saas-attack.com/DatabaseExtensibility/tabid/161/Default.aspx

This is a complex structure and when using the a ORM framework like EF it has plenty of limitations. I’m not aware of the EF much, so if anyone knows how to map this in the EF while keeping the constraints as they are, feel free to make a comment.