TPH vs. TPT: Choosing the Right Inheritance Strategy for Your SQL Server and C# Project

There are two main approaches to model inheritance in SQL Server:

1. Table Per Hierarchy (TPH):

   • A single table holds data for all classes in the hierarchy.
   • Each row includes a "discriminator" column that identifies the specific class (e.g., "VehicleType" with values like "Car", "Truck").
   • Columns specific to each subtype are included in the table, but might contain null values for irrelevant classes.

2. Table Per Type (TPT):

   • Separate tables are created for each class in the hierarchy.
   • The supertype table holds common attributes, and each subtype table inherits the primary key from the supertype and adds its specific attributes.

Example:

Imagine you have a hierarchy of classes: Vehicle (supertype) with properties like Make and Model, and subtypes Car (with NumDoors) and Truck (with CargoCapacity).

TPH Example (C# and SQL):

public class Vehicle
{
    public string Make { get; set; }
    public string Model { get; set; }
}

public class Car : Vehicle
{
    public int NumDoors { get; set; }
}

public class Truck : Vehicle
{
    public int CargoCapacity { get; set; }
}

// Table creation in SQL Server
CREATE TABLE Vehicles (
    VehicleID INT PRIMARY KEY IDENTITY,
    Make NVARCHAR(50),
    Model NVARCHAR(50),
    VehicleType NVARCHAR(10) // Discriminator column
);

INSERT INTO Vehicles (Make, Model, VehicleType)
VALUES ('Honda', 'Civic', 'Car'),
       ('Ford', 'F-150', 'Truck');

public class Vehicle
{
    public int VehicleID { get; set; }
    public string Make { get; set; }
    public string Model { get; set; }
}

public class Car : Vehicle
{
    public int NumDoors { get; set; }
}

public class Truck : Vehicle
{
    public int CargoCapacity { get; set; }
}

// Table creation in SQL Server
CREATE TABLE Vehicles (
    VehicleID INT PRIMARY KEY IDENTITY,
    Make NVARCHAR(50),
    Model NVARCHAR(50)
);

CREATE TABLE Cars (
    CarID INT PRIMARY KEY,
    NumDoors INT,
    FOREIGN KEY (CarID) REFERENCES Vehicles(VehicleID)
);

CREATE TABLE Trucks (
    TruckID INT PRIMARY KEY,
    CargoCapacity INT,
    FOREIGN KEY (TruckID) REFERENCES Vehicles(VehicleID)
);

// Inserting data
INSERT INTO Vehicles (Make, Model)
VALUES ('Honda', 'Civic'),
       ('Ford', 'F-150');

INSERT INTO Cars (CarID, NumDoors)
SELECT VehicleID, 4 FROM Vehicles WHERE Make = 'Honda';

INSERT INTO Trucks (TruckID, CargoCapacity)
SELECT VehicleID, 1000 FROM Vehicles WHERE Make = 'Ford';

Choosing the Right Approach:

• TPH: Advantages: Simple queries, efficient for retrieving all data at once. Disadvantages: Redundant data for subtypes can lead to wasted storage and potential inconsistencies.
• TPT: Advantages: Eliminates data redundancy, avoids null values, potentially faster updates for specific subtypes. Disadvantages: More complex joins required for queries involving multiple subtypes.

Related Issues and Solutions:

• Performance:
  • TPH might be slower for specific queries due to joins.
  • TPT can have overhead for updates involving multiple tables.
• Data Integrity:
• Complexity: