Representing Inheritance in Databases: Models and Best Practices

Introduction

Inheritance is a core concept in object-oriented programming, but mapping it to relational databases like SQL Server requires careful design. Based on common scenarios such as commercial insurance policy storage, this article introduces three inheritance models to help developers make informed choices.

Single Table Inheritance

Single Table Inheritance, also known as Table Per Hierarchy, uses a single table to store all subtypes. Common attributes are shared, while subtype-specific attributes are added as nullable columns, differentiated by a type column. Example SQL code:

CREATE TABLE policies (
    policy_id INT PRIMARY KEY,
    date_issued DATETIME NOT NULL,
    cover_type VARCHAR(50) NOT NULL,
    vehicle_registration VARCHAR(20),
    property_address VARCHAR(255)
    -- Other fields for different cover types
);

Pros: Simple schema. Cons: Many NULL values, inability to enforce NOT NULL constraints, and schema modifications needed for new subtypes.

Concrete Table Inheritance

Each subtype has its own table with repeated common attributes. This avoids NULLs but leads to data redundancy. Example SQL code:

CREATE TABLE motor_policies (
    policy_id INT PRIMARY KEY,
    date_issued DATETIME NOT NULL,
    vehicle_registration VARCHAR(20) NOT NULL
);

CREATE TABLE property_policies (
    policy_id INT PRIMARY KEY,
    date_issued DATETIME NOT NULL,
    property_address VARCHAR(255) NOT NULL
);

Pros: Enforces NOT NULL constraints. Cons: Violates DRY principle, requires UNION operations for cross-subtype queries.

Class Table Inheritance

A base table holds common attributes, with subtype tables linked via foreign keys. Often considered the best practice. Example SQL code:

CREATE TABLE policies (
    policy_id INT PRIMARY KEY IDENTITY(1,1),
    date_issued DATETIME NOT NULL
    -- Other common attributes
);

CREATE TABLE motor_covers (
    policy_id INT PRIMARY KEY,
    vehicle_registration VARCHAR(20) NOT NULL,
    FOREIGN KEY (policy_id) REFERENCES policies(policy_id)
);

CREATE TABLE property_covers (
    policy_id INT PRIMARY KEY,
    property_address VARCHAR(255) NOT NULL,
    FOREIGN KEY (policy_id) REFERENCES policies(policy_id)
);

Pros: DRY, enforces constraints, simple querying. Cons: Requires JOIN operations to retrieve subtype data.

Comparison and Best Practices

Class Table Inheritance balances flexibility and integrity, recommended for complex scenarios. Single Table Inheritance suits simple applications, while Concrete Table Inheritance may be used for performance optimization. The choice depends on query frequency, data volatility, and maintenance costs.

Conclusion

For representing inheritance in SQL Server, Class Table Inheritance offers the most robust solution, ensuring data consistency and scalability. Developers should evaluate models based on specific needs, but Class Table Inheritance is generally the preferred approach.