你如何有效地在数据库模型继承？ [英] How do you effectively model inheritance in a database?

问题描述

什么是数据库中建模继承的最佳实践？

What are the best practices for modeling inheritance in databases?

什么是权衡（如queriability）？

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

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

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

推荐答案

有几种方法可以继承数据库模型。你的选择取决于你的需求。这里有几个选项：

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

的table-per-类型（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.

因此​​，例如：

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

表，每层次（TPH）

有一个单一的表，再presents所有的继承层次结构，这意味着数列可能会很少。鉴别器列添加它告诉系统什么类型的列上了。

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

有关这是行类型0（人）的任何行的起始日期将始终为空。

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

表，每混凝土（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

这篇关于你如何有效地在数据库模型继承？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！