.net 您如何有效地对数据库中的继承进行建模？

How do you effectively model inheritance in a database?

提问by Even Mien

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.)

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

回答by Brad Wilson

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)

每类型表 (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)

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.

对于 rowtype 0 (Person) 的任何行，开始日期将始终为空。

Table-Per-Concrete (TPC)

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

回答by Jeffrey L Whitledge

Proper database design is nothing like proper object design.

正确的数据库设计与正确的对象设计完全不同。

If you are planning to use the database for anything other than simply serializing your objects (such as reports, querying, multi-application use, business intelligence, etc.) then I do not recommend any kind of a simple mapping from objects to tables.

如果您计划将数据库用于简单序列化对象以外的任何事情（例如报告、查询、多应用程序使用、商业智能等），那么我不建议从对象到表的任何类型的简单映射。

Many people think of a row in a database table as an entity (I spent many years thinking in those terms), but a row is not an entity. It is a proposition. A database relation (i.e., table) represents some statement of fact about the world. The presence of the row indicates the fact is true (and conversely, its absence indicates the fact is false).

许多人认为数据库表中的一行是一个实体（我用这些术语思考了很多年），但行不是一个实体。这是一个提议。数据库关系（即，表）表示关于世界的一些事实陈述。该行的存在表明该事实为真（相反，它的缺失表明该事实为假）。

With this understanding, you can see that a single type in an object-oriented program may be stored across a dozen different relations. And a variety of types (united by inheritance, association, aggregation, or completely unaffiliated) may be partially stored in a single relation.

通过这种理解，您可以看到面向对象程序中的单个类型可能存储在十几种不同的关系中。并且多种类型（通过继承、关联、聚合或完全无关联）可以部分存储在单个关系中。

It is best to ask yourself, what facts do you want to store, what questions are you going to want answers to, what reports do you want to generate.

最好问问自己，您想要存储哪些事实，您想要回答什么问题，您想要生成什么报告。

Once the proper DB design is created, then it is a simple matter to create queries/views that allow you to serialize your objects to those relations.

一旦创建了正确的数据库设计，那么创建查询/视图就很简单了，允许您将对象序列化为这些关系。

Example:

例子：

In a hotel booking system, you may need to store the fact that Jane Doe has a reservation for a room at the Seaview Inn for April 10-12. Is that an attribute of the customer entity? Is it an attribute of the hotel entity? Is it a reservation entity with properties that include customer and hotel? It could be any or all of those things in an object oriented system. In a database, it is none of those things. It is simply a bare fact.

在酒店预订系统中，您可能需要存储 Jane Doe 预订了 4 月 10 日至 12 日在 Seaview Inn 的房间这一事实。这是客户实体的属性吗？它是酒店实体的属性吗？它是一个拥有包括客户和酒店在内的属性的预订实体吗？它可以是面向对象系统中的任何或所有这些东西。在数据库中，这些都不是。这只是一个赤裸裸的事实。

To see the difference, consider the following two queries. (1) How many hotel reservations does Jane Doe have for next year? (2) How many rooms are booked for April 10 at the Seaview Inn?

要查看差异，请考虑以下两个查询。(1) Jane Doe 明年有多少酒店预订？(2) 4月10日海景客栈订了几间房？

In an object-oriented system, query (1) is an attribute of the customer entity, and query (2) is an attribute of the hotel entity. Those are the objects that would expose those properties in their APIs. (Though, obviously the internal mechanisms by which those values are obtained may involve references to other objects.)

在面向对象的系统中，query(1)是customer实体的一个属性，query(2)是hotel实体的一个属性。这些是将在其 API 中公开这些属性的对象。（不过，很明显，获取这些值的内部机制可能涉及对其他对象的引用。）

In a relational database system, both queries would examine the reservation relation to get their numbers, and conceptually there is no need to bother with any other "entity".

在关系数据库系统中，两个查询都将检查保留关系以获取它们的编号，并且从概念上讲，不需要打扰任何其他“实体”。

Thus, it is by attempting to store facts about the world—rather than attempting to store entities with attributes—that a proper relational database is constructed. And once it is properly designed, then useful queries that were undreamt of during the design phase can be easily constructed, since all the facts needed to fulfill those queries are in their proper places.

因此，正是通过尝试存储关于世界的事实——而不是尝试存储具有属性的实体——构建了一个合适的关系数据库。一旦设计得当，就可以轻松构建在设计阶段做梦都想不到的有用查询，因为完成这些查询所需的所有事实都在适当的位置。

回答by Marcin

Short answer: you don't.

简短的回答：你没有。

If you need to serialize your objects, use an ORM, or even better something like activerecord or prevaylence.

如果您需要序列化您的对象，请使用 ORM，或者更好的类似 activerecord 或 prevaylence。

If you need to store data, store it in a relational manner (being careful about what you are storing, and paying attention to what Jeffrey L Whitledge just said), not one affected by your object design.

如果您需要存储数据，请以关系方式存储它（小心存储的内容，并注意 Jeffrey L Whitledge 刚才所说的内容），而不是受对象设计影响的方式。

回答by imang

TPT, TPH and TPC patterns are the ways you go, as mentioned by Brad Wilson. But couple of notes:

正如 Brad Wilson 所提到的，TPT、TPH 和 TPC 模式是您要走的路。但有几点需要注意：

• child classes inheriting from a base class can be seen as weak-entities to the base class definition in the database, meaning they are dependent to their base-class and cannot exist without it. I've seen number of times, that unique IDs are stored for each and every child table while also keeping the FK to the parent table. One FK is just enough and its even better to have on-delete cascade enable for the FK-relation between the child and base tables.

• In TPT, by only seeing the base table records, you're not able to find which child class the record is representing. This is sometimes needed, when you want to load a list of all records (without doing select on each and every child table). One way to handle this, is to have one column representing the type of the child class (similar to the rowType field in the TPH), so mixing the TPT and TPH somehow.

• 从基类继承的子类可以被视为数据库中基类定义的弱实体，这意味着它们依赖于它们的基类，没有它就不能存在。我已经多次看到，为每个子表存储唯一 ID，同时还将 FK 保留到父表。一个 FK 就足够了，甚至更好的是为子表和基表之间的 FK 关系启用 on-delete 级联。

• 在 TPT 中，仅通过查看基表记录，您无法找到该记录代表哪个子类。当您想要加载所有记录的列表（而不是select 在每个子表上执行）时，有时需要这样做。处理这个问题的一种方法是让一列代表子类的类型（类似于 TPH 中的 rowType 字段），因此以某种方式混合 TPT 和 TPH。

Say we want to design a database that holds the following shape class diagram:

假设我们要设计一个包含以下形状类图的数据库：

public class Shape {
int id;
Color color;
Thickness thickness;
//other fields
}

public class Rectangle : Shape {
Point topLeft;
Point bottomRight;
}

public class Circle : Shape {
Point center;
int radius;
}

The database design for the above classes can be like this:

上述类的数据库设计可以是这样的：

table Shape
-----------
int id; (PK)
int color;
int thichkness;
int rowType; (0 = Rectangle, 1 = Circle, 2 = ...)

table Rectangle
----------
int ShapeID; (FK on delete cascade)
int topLeftX;
int topLeftY;
int bottomRightX;
int bottomRightY;

table Circle
----------
int ShapeID; (FK on delete cascade)  
int centerX;
int center;
int radius;

回答by mattlant

There are two main types of inheritance you can setup in a DB, table per entity and table per Hierarchy.

您可以在数据库中设置两种主要类型的继承，每个实体的表和每个层次结构的表。

Table per entity is where you have a base entity table that has shared properties of all child classes. You then have per child class another table each with only properties applicable to that class. They are linked 1:1 by their PK's

每个实体的表是您拥有一个基本实体表的地方，该表具有所有子类的共享属性。然后，每个子类都有另一个表，每个表都只有适用于该类的属性。他们通过他们的PK 1:1链接

Table per hierarchy is where all classes shared a table, and optional properties are nullable. Their is also a discriminator field which is a number that denotes the type that the record currently holds

每个层次结构的表是所有类共享一个表的地方，并且可选属性可以为空。它们也是一个鉴别器字段，它是一个数字，表示记录当前保存的类型

SessionTypeID is discriminator

SessionTypeID 是鉴别器

Target per hierarchy is faster to query for as you do not need joins(only the discriminator value), whereas target per entity you need to do complex joins in order to detect what type something is as well as retreiuve all its data..

每个层次结构的目标查询速度更快，因为您不需要连接（仅鉴别器值），而每个实体的目标您需要进行复杂的连接以检测某物的类型并检索其所有数据。

Edit: The images I show here are screen shots of a project I am working on. The Asset image is not complete, hence the emptyness of it, but it was mainly to show how its setup, not what to put inside your tables. That is up to you ;). The session table holds Virtual collaboration session information, and can be of several types of sessions depending on what type of collaboration is involved.

编辑：我在这里展示的图像是我正在处理的一个项目的屏幕截图。资产图像不完整，因此它是空的，但它主要是为了展示它的设置方式，而不是在你的表格中放什么。那取决于你 ;）。会话表保存虚拟协作会话信息，并且可以是多种类型的会话，具体取决于所涉及的协作类型。

回答by Per Hornsh?j-Schierbeck

You would normalize of your database and that would actually mirror your inheritance. It might have performance degradance, but that's how it is with normalizing. You probably will have to use good common sense to find the balance.

您将规范化您的数据库，这实际上会反映您的继承。它可能会降低性能，但这就是规范化的方式。您可能需要运用良好的常识来找到平衡点。

回答by Steven A. Lowe

repeat of similar thread answer

重复类似的线程答案

in O-R mapping, inheritance maps to a parent table where the parent and child tables use the same identifier

在 OR 映射中，继承映射到父表，其中父表和子表使用相同的标识符

for example

例如

create table Object (
    Id int NOT NULL --primary key, auto-increment
    Name varchar(32)
)
create table SubObject (
    Id int NOT NULL  --primary key and also foreign key to Object
    Description varchar(32)
)

SubObject has a foreign-key relationship to Object. when you create a SubObject row, you must first create an Object row and use the Id in both rows

SubObject 与 Object 有外键关系。创建子对象行时，必须先创建对象行并在两行中​​使用 Id

EDIT: if you're looking to model behavior also, you would need a Type table that listed the inheritance relationships between tables, and specified the assembly and class name that implemented each table's behavior

编辑：如果您还想对行为建模，则需要一个 Type 表，其中列出了表之间的继承关系，并指定了实现每个表行为的程序集和类名

seems like overkill, but that all depends on what you want to use it for!

看起来有点矫枉过正，但这一切都取决于你想用它做什么！

回答by Matthew Schinckel

Using SQL ALchemy (Python ORM), you can do two types of inheritance.

使用 SQL ALchemy (Python ORM)，您可以进行两种类型的继承。

The one I've had experience is using a singe-table, and having a discriminant column. For instances, a Sheep database (no joke!) stored all Sheep in the one table, and Rams and Ewes were handled using a gender column in that table.

我的经验是使用单表，并有一个判别列。例如，Sheep 数据库（不是开玩笑！）将所有 Sheep 存储在一个表中，而 Rams 和 Ewes 使用该表中的性别列进行处理。

Thus, you can query for all Sheep, and get all Sheep. Or you can query by Ram only, and it will only get Rams. You can also do things like have a relation that can only be a Ram (ie, the Sire of a Sheep), and so on.

因此，您可以查询所有 Sheep，并获取所有 Sheep。或者你可以只通过 Ram 查询，它只会得到 Rams。你也可以做一些事情，比如建立一个只能是公羊（即绵羊的父亲）的关系，等等。

回答by Pierre

Note that some database engines already provides inheritance mechanisms natively like Postgres. Look at the documentation.

请注意，一些数据库引擎已经像Postgres一样提供了原生的继承机制。查看文档。

For an example, you would query the Person/Employee system described in a response above like this:

例如，您将查询上述响应中描述的 Person/Employee 系统，如下所示：

  /* This shows the first name of all persons or employees */
  SELECT firstname FROM Person ; 

  /* This shows the start date of all employees only */
  SELECT startdate FROM Employee ;

In that is your database's choice, you don't need to be particularly smart !

这是您的数据库的选择，您不需要特别聪明！