LehnerBuild

Programming

How can you represent inheritance in a database

25 September 2026 · 9 min read

How can you represent inheritance in a database

Representing inheritance within a database presents a unique challenge for developers. Inheritance, a fundamental concept in object-oriented programming, allows classes to inherit properties and behaviors from parent classes. But how do you translate this hierarchical relationship into the relational structure of a database? Choosing the right approach is crucial for data integrity, query efficiency, and overall application performance. This article explores various strategies for representing inheritance in a database, weighing their pros and cons to help you make the best decision for your specific needs.

Single Table Inheritance

The single table inheritance approach involves storing all attributes of all classes in the inheritance hierarchy within a single table. This table includes columns for every possible attribute, even if they are not applicable to all subclasses. NULL values are used to fill in gaps where a particular subclass doesn’t utilize a specific attribute. While simple to implement, this method can lead to a sparse table with many NULL values, impacting storage space and query performance.

For instance, imagine a “Vehicle” base class and subclasses “Car” and “Motorcycle.” A single table would contain columns for number of doors (relevant to cars) and engine displacement (relevant to both), leading to NULLs in the “number of doors” column for motorcycle entries. This approach is best suited for simple inheritance hierarchies with minimal differentiation between subclasses.

Class Table Inheritance

Class table inheritance involves creating a separate table for each class in the hierarchy. Each table contains only the attributes specific to that class. A foreign key relationship links each subclass table back to its parent table. This approach avoids the NULL value issue of the single table approach and offers better data normalization. However, it can complicate queries that involve retrieving data across the entire hierarchy, as they may require joins across multiple tables.

Continuing the vehicle example, separate tables would exist for “Vehicle,” “Car,” and “Motorcycle.” The “Car” table would have a foreign key referencing the “Vehicle” table, inheriting common attributes like “make” and “model.” This method is suitable for complex inheritance structures with significant attribute variations among subclasses.

Concrete Table Inheritance

Concrete table inheritance creates a separate table for each concrete class in the hierarchy, but not for abstract classes. Each table contains all the attributes inherited from its parent classes, as well as its own specific attributes. This approach offers a balance between data normalization and query simplicity. It avoids the need for joins in most queries, but it can lead to some data redundancy if many subclasses share common attributes from parent classes.

In our vehicle example, “Car” and “Motorcycle” tables would exist, each containing all vehicle attributes plus their specific ones. This eliminates joins for fetching complete car or motorcycle data, a trade-off for redundant “make” and “model” storage compared to class table inheritance. This approach balances query efficiency with data redundancy concerns.

Joined Table Inheritance

Joined table inheritance utilizes a separate table for each class in the hierarchy, similar to class table inheritance. However, it avoids NULL values by storing only the specific attributes of each class in its corresponding table. Shared attributes are still stored in the parent table. Retrieving all information about a specific object requires joining the relevant tables based on the foreign key relationships.

This approach addresses the sparsity issue of the single table method and the data redundancy of the concrete table method. However, it introduces the complexity of joins for certain queries. Consider this approach when you have a deep inheritance hierarchy and want to avoid data redundancy and null values, even at the cost of more complex queries.

Infographic Placeholder: Visual representation of the four inheritance methods.

  • Consider the complexity of your inheritance hierarchy.
  • Evaluate the trade-offs between query complexity, data redundancy, and NULL values.
  1. Analyze your application’s query patterns.
  2. Choose the inheritance representation that best suits your needs.
  3. Implement and test your chosen approach.

Expert Quote: “Choosing the right database inheritance strategy is a critical design decision that can significantly impact application performance and maintainability.” - Dr. Database, Renowned Database Architect.

See more on database design principles on this website.

For deeper insights, explore resources on database normalization and object-relational mapping (ORM) techniques.

Learn MoreFrequently Asked Questions

Q: What is the simplest approach for representing inheritance in a database?

A: Single table inheritance is the simplest, though it can lead to data sparsity.

Q: Which method offers the best data normalization?

A: Class table inheritance provides the best normalization, minimizing data redundancy.

Selecting the optimal approach for representing inheritance in your database involves a nuanced understanding of your specific requirements. The ideal choice hinges on factors like the complexity of your inheritance structure, anticipated query patterns, and tolerance for data redundancy or NULL values. By carefully evaluating these factors and considering the various trade-offs, you can create a robust and efficient database design that seamlessly integrates with your object-oriented application. Explore the resources provided and experiment with different approaches to find the best fit for your project. Effective database design starts with a solid understanding of inheritance representation. A well-structured database forms the backbone of any successful application, ensuring data integrity and efficient retrieval.

Question & Answer :
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 are possible solutions for this scenario?

@Bill Karwin describes three inheritance models in his SQL Antipatterns book, when proposing solutions to the SQL Entity-Attribute-Value antipattern. This is a brief overview:

Single Table Inheritance (aka Table Per Hierarchy Inheritance):

Using a single table as in your first option is probably the simplest design. As you mentioned, many attributes that are subtype-specific will have to be given a NULL value on rows where these attributes do not apply. With this model, you would have one policies table, which would look something like this:

+------+---------------------+----------+----------------+------------------+ | id | date_issued | type | vehicle_reg_no | property_address | +------+---------------------+----------+----------------+------------------+ | 1 | 2010-08-20 12:00:00 | MOTOR | 01-A-04004 | NULL | | 2 | 2010-08-20 13:00:00 | MOTOR | 02-B-01010 | NULL | | 3 | 2010-08-20 14:00:00 | PROPERTY | NULL | Oxford Street | | 4 | 2010-08-20 15:00:00 | MOTOR | 03-C-02020 | NULL | +------+---------------------+----------+----------------+------------------+ \------ COMMON FIELDS -------/ \----- SUBTYPE SPECIFIC FIELDS -----/

Keeping the design simple is a plus, but the main problems with this approach are the following:

  • When it comes to adding new subtypes, you would have to alter the table to accommodate the attributes that describe these new objects. This can quickly become problematic when you have many subtypes, or if you plan to add subtypes on a regular basis.
  • The database will not be able to enforce which attributes apply and which don’t, since there is no metadata to define which attributes belong to which subtypes.
  • You also cannot enforce NOT NULL on attributes of a subtype that should be mandatory. You would have to handle this in your application, which in general is not ideal.

Concrete Table Inheritance:

Another approach to tackle inheritance is to create a new table for each subtype, repeating all the common attributes in each table. For example:

--// Table: policies_motor +------+---------------------+----------------+ | id | date_issued | vehicle_reg_no | +------+---------------------+----------------+ | 1 | 2010-08-20 12:00:00 | 01-A-04004 | | 2 | 2010-08-20 13:00:00 | 02-B-01010 | | 3 | 2010-08-20 15:00:00 | 03-C-02020 | +------+---------------------+----------------+ --// Table: policies_property +------+---------------------+------------------+ | id | date_issued | property_address | +------+---------------------+------------------+ | 1 | 2010-08-20 14:00:00 | Oxford Street | +------+---------------------+------------------+

This design will basically solve the problems identified for the single table method:

  • Mandatory attributes can now be enforced with NOT NULL.
  • Adding a new subtype requires adding a new table instead of adding columns to an existing one.
  • There is also no risk that an inappropriate attribute is set for a particular subtype, such as the vehicle_reg_no field for a property policy.
  • There is no need for the type attribute as in the single table method. The type is now defined by the metadata: the table name.

However this model also comes with a few disadvantages:

  • The common attributes are mixed with the subtype specific attributes, and there is no easy way to identify them. The database will not know either.
  • When defining the tables, you would have to repeat the common attributes for each subtype table. That’s definitely not DRY.
  • Searching for all the policies regardless of the subtype becomes difficult, and would require a bunch of UNIONs.

This is how you would have to query all the policies regardless of the type:

SELECT date_issued, other_common_fields, 'MOTOR' AS type FROM policies_motor UNION ALL SELECT date_issued, other_common_fields, 'PROPERTY' AS type FROM policies_property;

Note how adding new subtypes would require the above query to be modified with an additional UNION ALL for each subtype. This can easily lead to bugs in your application if this operation is forgotten.

Class Table Inheritance (aka Table Per Type Inheritance):

This is the solution that @David mentions in the other answer. You create a single table for your base class, which includes all the common attributes. Then you would create specific tables for each subtype, whose primary key also serves as a foreign key to the base table. Example:

CREATE TABLE policies ( policy_id int, date_issued datetime, -- // other common attributes ... ); CREATE TABLE policy_motor ( policy_id int, vehicle_reg_no varchar(20), -- // other attributes specific to motor insurance ... FOREIGN KEY (policy_id) REFERENCES policies (policy_id) ); CREATE TABLE policy_property ( policy_id int, property_address varchar(20), -- // other attributes specific to property insurance ... FOREIGN KEY (policy_id) REFERENCES policies (policy_id) );

This solution solves the problems identified in the other two designs:

  • Mandatory attributes can be enforced with NOT NULL.
  • Adding a new subtype requires adding a new table instead of adding columns to an existing one.
  • No risk that an inappropriate attribute is set for a particular subtype.
  • No need for the type attribute.
  • Now the common attributes are not mixed with the subtype specific attributes anymore.
  • We can stay DRY, finally. There is no need to repeat the common attributes for each subtype table when creating the tables.
  • Managing an auto incrementing id for the policies becomes easier, because this can be handled by the base table, instead of each subtype table generating them independently.
  • Searching for all the policies regardless of the subtype now becomes very easy: No UNIONs needed - just a SELECT * FROM policies.

I consider the class table approach as the most suitable in most situations.

The names of these three models come from Martin Fowler’s book Patterns of Enterprise Application Architecture.