[sql-server] How can you represent inheritance in a database?

I'm thinking about how to represent a complex structure in a SQL Server database.

Consider an application that needs to store details of a family of objects, which share some attributes, but have many others not common. For example, a commercial insurance package may include liability, motor, property and indemnity cover within the same policy record.

It is trivial to implement this in C#, etc, as you can create a Policy with a collection of Sections, where Section is inherited as required for the various types of cover. However, relational databases don't seem to allow this easily.

I can see that there are two main choices:

1. Create a Policy table, then a Sections table, with all the fields required, for all possible variations, most of which would be null.

2. Create a Policy table and numerous Section tables, one for each kind of cover.

Both of these alternatives seem unsatisfactory, especially as it is necessary to write queries across all Sections, which would involve numerous joins, or numerous null-checks.

What is the best practice for this scenario?

The another way to do it, is using the INHERITS component. For example:

CREATE TABLE person (
    id int ,
    name varchar(20),
    CONSTRAINT pessoa_pkey PRIMARY KEY (id)
);

CREATE TABLE natural_person (
    social_security_number varchar(11),
    CONSTRAINT pessoaf_pkey PRIMARY KEY (id)
) INHERITS (person);


CREATE TABLE juridical_person (
    tin_number varchar(14),
    CONSTRAINT pessoaj_pkey PRIMARY KEY (id)
) INHERITS (person);

Thus it's possible to define a inheritance between tables.

In addition at the Daniel Vassallo solution, if you use SQL Server 2016+, there is another solution that I used in some cases without considerable lost of performances.

You can create just a table with only the common field and add a single column with the JSON string that contains all the subtype specific fields.

I have tested this design for manage inheritance and I am very happy for the flexibility that I can use in the relative application.

I lean towards method #1 (a unified Section table), for the sake of efficiently retrieving entire policies with all their sections (which I assume your system will be doing a lot).

Further, I don't know what version of SQL Server you're using, but in 2008+ Sparse Columns help optimize performance in situations where many of the values in a column will be NULL.

Ultimately, you'll have to decide just how "similar" the policy sections are. Unless they differ substantially, I think a more-normalized solution might be more trouble than it's worth... but only you can make that call. :)

Check out the answer I gave here

Fluent NHibernate one-to-one mapping with synthetic keys

The 3rd option is to create a "Policy" table, then a "SectionsMain" table that stores all of the fields that are in common across the types of sections. Then create other tables for each type of section that only contain the fields that are not in common.

Deciding which is best depends mostly on how many fields you have and how you want to write your SQL. They would all work. If you have just a few fields then I would probably go with #1. With "lots" of fields I would lean towards #2 or #3.

Alternatively, consider using a document databases (such as MongoDB) which natively support rich data structures and nesting.

With the information provided, I'd model the database to have the following:

POLICIES

• POLICY_ID (primary key)

LIABILITIES

• LIABILITY_ID (primary key)
• POLICY_ID (foreign key)

PROPERTIES

• PROPERTY_ID (primary key)
• POLICY_ID (foreign key)

...and so on, because I'd expect there to be different attributes associated with each section of the policy. Otherwise, there could be a single SECTIONS table and in addition to the policy_id, there'd be a section_type_code...

Either way, this would allow you to support optional sections per policy...

I don't understand what you find unsatisfactory about this approach - this is how you store data while maintaining referential integrity and not duplicating data. The term is "normalized"...

Because SQL is SET based, it's rather alien to procedural/OO programming concepts & requires code to transition from one realm to the other. ORMs are often considered, but they don't work well in high volume, complex systems.