您如何有效地对数据库中的继承进行建模? [英] How do you effectively model inheritance in a database?

查看:25
本文介绍了您如何有效地对数据库中的继承进行建模?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆