This repository contains a .NET API 6 application that demonstrates two different mapping strategies for handling inheritance relationships: TPH (Table Per Hierarchy) and TPT (Table Per Type) with IdentityFramework for User Management . The aim of this project is to compare these two strategies in terms of storage efficiency, performance, and maintainability.
- Introduction
- Installation
- Mapping Strategies
- Comparison
- Performance Analysis
- ERDs
- UML for Users
- References
In object-oriented programming, inheritance allows a class (subclass) to inherit properties and behaviors from another class (base class). When working with a database, mapping these inheritance relationships efficiently becomes important. This project explores two common mapping strategies: TPH (Table Per Hierarchy) and TPT (Table Per Type) in a .NET API 6 application.
TPH strategy involves storing all subclasses of a hierarchy in a single table, utilizing a discriminator column to differentiate between different types. On the other hand, TPT strategy uses a separate table for each subclass, linked to the base class table through foreign keys.
By comparing these strategies, we aim to understand the trade-offs between storage efficiency, performance, and ease of maintenance, helping developers make informed decisions when designing their data models.
To run the project locally, follow these steps:
- Clone the repository to your local machine.
- Ensure you have .NET SDK 6 installed.
- Restore the NuGet packages by running
dotnet restorein the project's root directory. - Build the application using
dotnet build. - Run the application with
dotnet run.
In TPH strategy, all subclasses of a hierarchy are stored in a single table. A discriminator column is used to differentiate between different entity types in the table. This approach simplifies the database schema but can lead to redundancy and less efficient storage, as all properties for all subclasses are stored in one table.
In TPT strategy, each subclass has its own separate table, which is linked to the base class table through foreign keys. This approach allows for a more normalized database schema but can result in complex queries when fetching data involving multiple subclasses.
Let's compare the TPH and TPT strategies based on three key criteria:
- TPH : This strategy can lead to redundant storage, as all properties for all subclasses are stored in a single table. However, it may require fewer joins for certain queries.
- TPT : This strategy results in a more normalized schema, potentially reducing redundancy. Each subclass has its own table, leading to more efficient storage of properties unique to each subclass. However, queries may require more joins due to the separate tables.
- TPH : With fewer joins required, certain queries may have better performance compared to TPT. However, queries involving complex inheritance hierarchies may perform slower due to the need for filtering based on the discriminator column.
- TPT : The normalized schema may lead to faster queries for certain scenarios. However, complex queries involving multiple subclasses may involve more joins and could impact performance.
- TPH : The single-table approach makes schema updates straightforward. However, as the number of subclasses increases, the table can become cluttered, making maintenance challenging.
- TPT : The separated-table approach makes schema updates easier for individual subclasses. However, maintaining a large number of tables can be cumbersome.
