您如何有效地对数据库中的继承进行建模? [英] How do you effectively model inheritance in a database?
问题描述
在数据库中建模继承的最佳实践是什么?
What are the best practices for modeling inheritance in databases?
有哪些权衡(例如可查询性)?
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:
表格类型 (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;
}
会产生如下表格:
table Person
------------
int id (PK)
string firstname
string lastname
table Employee
--------------
int id (PK, FK)
datetime startdate
每个层次结构表 (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
对于 rowtype 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屋!