Inheritance in SQL. How to guarantee it?

Question

I am trying to create inheritance as in a C# object using SQL Server and I have:

create table dbo.Evaluations
(
  Id int not null constraint primary key clustered (Id),  
  Created datetime not null
);

create table dbo.Exams
(
  Id int not null,  
  Value int not null
  // Some other fields
);

create table dbo.Tests
(
  Id int not null,  
  Order int not null
  // Some other fields
);

alter table dbo.Exams
add constraint FK_Exams_Id foreign key (Id) references dbo.Evaluations(Id);

alter table dbo.Tests
add constraint FK_Tests_Id foreign key (Id) references dbo.Evaluations(Id);

Which would translate to:

public class Evaluation {}

public class Exam : Evaluation {}

public class Test : Evaluation {}

I think this is the way to go but I have a problem:

How to force that an Evaluation has only one Test or one Exam but not both?
To find which type of evaluation I have I can check exam or test for null. But should I have an EvaluationType in Evaluations table instead?

NOTE: In reality I have 4 subtypes each one with around 40 to 60 different columns. And in Evaluations table I have around 20 common columns which are also the ones which i use more often to query so I get lists.

http://stackoverflow.com/questions/386652/techniques-for-database-inheritance — Donal, Sep 01 '14 at 14:29
Yes, I have read that ... But again I am not sure how to answer my 2 questions ... Should I use 2 tables, Exams and Tests, and place the common columns in them? In fact I have two more objects and around 15 common columns so I though this was the way to go. — Miguel Moura, Sep 01 '14 at 14:33

Gordon Linoff · Answer 1 · 2014-09-01T15:07:27.947

First, don't use reserved words such as order for column names.

You have a couple of choices on what to do. For this simple example, I would suggest just having the two foreign key references in the evaluation table, along with some constraints and computed columns. Something like this:

create table dbo.Evaluations
(
  EvaluationId int not null constraint primary key clustered (Id),
  ExamId int references exams(ExamId),
  TestId int references tests(TestId),
  Created datetime not null,
  EvaluationType as (case when ExamId is not null then 'Exam' when TestId is not null then  'Test' end),
  check (not (ExamId is not null and TestId is not null))
);

This approach gets less practical if you have lots of subtypes. For your case, though, it provides the following:

Foreign key references to the subtables.
A column specifying the type.
A validation that at most one type is set for each evaluation.

It does have a slight overhead of storing the extra, unused id, but that is a small overhead.

EDIT:

With four subtypes, you can go in the other direction of having a single reference and type in the parent table and then using conditional columns and indexes to enforce the constraints:

create table dbo.Evaluations
(
  EvaluationId int not null constraint primary key clustered (Id),
  EvaluationType varchar(255) not null,
  ChildId int not null,
  CreatedAt datetime not null,
  EvaluationType as (case when ExamId is not null then 'Exam' when TestId is not null then  'Test' end),
  ExamId as (case when EvaluationType = 'Exam' then ChildId end),
  TestId as (case when EvaluationType = 'Test' then ChildId end),
  Other1Id as (case when EvaluationType = 'Other1' then ChildId end),
  Other2Id as (case when EvaluationType = 'Other2' then ChildId end),
  Foreign Key (ExamId) int references exams(ExamId),
  Foreign Key (TestId) int references tests(TestId),
  Foreign Key (Other1Id) int references other1(Other1Id),
  Foreign Key (Other2Id) int references other2(Other2Id)
);

In some ways, this is the better solution to the problem. It minimizes storage and is extensible for additional types. Note that it is using computed columns for the foreign key references, so it is still maintaining relational integrity.

I updated my answer and added a note ... I have 4 subtypes and many columns ... It feels strange to me to have the FK's in Evaluations ... What do you think? And if I have 4 or more subtypes I will get many null values ... That is why I ended up with the other option. — Miguel Moura, Sep 01 '14 at 14:38
Yes, order was just an example ... I forgot it was a reserved word. the example I typed is not the real one because the real one has a lot of columns and much more tables. — Miguel Moura, Sep 01 '14 at 14:39

score 0 · Answer 2 · answered Sep 01 '14 at 15:05

My best experience is include all columns in one table. Relation model is not much friendly with object oriented design. If you treat every class as one table, you can get performance problems with high number of rows in "base-table" (base class) or you can suffer from a lot of joins if you have level of inheritance.

If you want minimalize amount of work to get correct structure, create your own tool, which can genrate create/alter scripts of tables for chosen classes. It's in fact pretty easy. Then you can generate also your data access layer. In result you will get automatic worker and you can focus on complex tasks and delegate work for "trained monkeys" to computer not humans.

Inheritance in SQL. How to guarantee it?

2 Answers2