Table-per-Hierarchy (TPH) Inheritance

In Object-Oriented C#, Inheritance is a fundamental concept (e.g., Dog inherits from Animal). However, SQL Databases inherently do not support inheritance. EF Core bridges this gap securely using three distinct patterns, the most performant and default of which is Table-per-Hierarchy (TPH).

1. Modeling the C# Inheritance

Imagine a billing system where you sell both Physical Products and Digital Subscriptions. They share common traits (Price, Name) but possess unique traits.

public abstract class Product 
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

public class Book : Product 
{
    public decimal ShippingWeightKb { get; set; }
}

public class SoftwareLicense : Product 
{
    public string DownloadUrl { get; set; }
}

2. How TPH Works (The Discriminator Column)

By default, if you include these classes in your DbContext, EF Core will generate exactly ONE physical SQL Table named Products containing ALL the columns from EVERY subclass.

EF Core uses the hidden Discriminator string column to figure out which C# subclass to instantiate when you run _context.Products.ToList()!

3. Customizing the Discriminator (Fluent API)

Storing the raw C# class name ("SoftwareLicense") as a string is brittle. If you rename the C# class later, your entire database breaks. We can configure TPH to use integers instead of class names.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Product>()
        // Override the default Discriminator to be an Integer
        .HasDiscriminator<int>("ProductType")
        .HasValue<Book>(1) // Book = 1
        .HasValue<SoftwareLicense>(2); // SoftwareLicense = 2
}

4. Interview Mastery

Q: "Because TPH places all properties for all derived classes into a single giant SQL table, doesn't it violate Database Normalization and resulting in massive amounts of NULL data spaces?"

Architect Answer: "Yes, it creates a sparsely populated table. In the table above, `DownloadUrl` is always `NULL` for Books. This seems like bad database design, but SQL Server is incredibly efficient at storing `NULL` values (consuming almost zero physical bytes). The immense advantage of TPH is read performance. If you want to query all Products, TPH executes a single `SELECT * FROM Products`. If you used the alternative 'Table-per-Type' (TPT) approach where each subclass gets its own physical table, EF Core would have to execute a massive, computationally expensive `OUTER JOIN` across all inherited tables just to load a simple catalog. TPH trades perfect normalization for extreme query speed."