How can you represent inheritance in a database?

user_393851 0
Viewed 138131

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?

class-table-inheritance database-design inheritance sql-server
7 Answers

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.

user_19130890

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

user_2695380
Related