在数据库中如何有效地建模继承？(How do you effectively model inheritance in a database?)

在数据库中如何有效地建模继承？(How do you effectively model inheritance in a database?)

在数据库中建模继承的最佳做法是什么？

什么是权衡（例如可信度）？

（我最感兴趣的是SQL Server和.NET，但我也想了解其他平台如何解决这个问题。）

What are the best practices for modeling inheritance in databases?

What are the trade-offs (e.g. queriability)?

(I'm most interested in SQL Server and .NET, but I also want to understand how other platforms address this issue.)

最满意答案

有几种方法可以在数据库中建模继承。 你选择的是取决于你的需要。 以下是几个选项：

表型（TPT）

每个班都有自己的桌子。 基类中包含所有基类元素，从其派生的每个类都有自己的表，主键也是基类表的外键; 派生表的类只包含不同的元素。

所以例如：

class Person { public int ID; public string FirstName; public string LastName; } class Employee : Person { public DateTime StartDate; }

会产生如下表格：

table Person ------------ int id (PK) string firstname string lastname table Employee -------------- int id (PK, FK) datetime startdate

表层次结构（TPH）

有一个表格表示所有的继承层次结构，这意味着几个列可能是稀疏的。 添加了一个歧视列，告诉系统这是什么类型的行。

给出上面的类，你最终得到这个表：

table Person ------------ int id (PK) int rowtype (0 = "Person", 1 = "Employee") string firstname string lastname datetime startdate

对于rowtype 0（Person）的任何行，startdate将始终为空。

表混凝土（TPC）

每个类都有自己完整的表，没有任何其他表的引用。

给出上面的类，你最终得到这些表：

table Person ------------ int id (PK) string firstname string lastname table Employee -------------- int id (PK) string firstname string lastname datetime startdate

There are several ways to model inheritance in a database. Which you choose depends on your needs. Here are a few options:

Table-Per-Type (TPT)

Each class has its own table. The base class has all the base class elements in it, and each class which derives from it has its own table, with a primary key which is also a foreign key to the base class table; the derived table's class contains only the different elements.

So for example:

class Person { public int ID; public string FirstName; public string LastName; } class Employee : Person { public DateTime StartDate; }

Would result in tables like:

table Person ------------ int id (PK) string firstname string lastname table Employee -------------- int id (PK, FK) datetime startdate

Table-Per-Hierarchy (TPH)

There is a single table which represents all the inheritance hierarchy, which means several of the columns will probably be sparse. A discriminator column is added which tells the system what type of row this is.

Given the classes above, you end up with this table:

table Person ------------ int id (PK) int rowtype (0 = "Person", 1 = "Employee") string firstname string lastname datetime startdate

For any rows which are rowtype 0 (Person), the startdate will always be null.

Table-Per-Concrete (TPC)

Each class has its own fully formed table with no references off to any other tables.

Given the classes above, you end up with these tables:

table Person ------------ int id (PK) string firstname string lastname table Employee -------------- int id (PK) string firstname string lastname datetime startdate