数据库继承技术？ [英] Techniques for database inheritance?

问题描述

当你需要持久化类继承到不支持继承的关系数据库时，提示/技巧是什么？

What are the tips/techniques when you need to persist classes with inheritance to relational database that doesn't support inheritance?

说我有这个经典的例子： / p>

Say I have this classic example:

Person -> Employee -> Manager
                   -> Team lead
                   -> Developer
       -> Customer -> PrivilegedCustomer
                   -> EnterpriseCustomer

设计数据库的可用技术有哪些？每个的优点和缺点？

What are the available techniques to design the database? Pros and cons of each?

p.s。我搜索并发现了几个关于数据库继承的问题，但大多数是关于更改为支持本机的数据库引擎。

p.s. I have searched and found several question regarding database inheritance but most were about changing to a database engine that supports it natively. But let's say I'm stuck with SQL Server 2005... what are my options?

推荐答案

三种常见的策略：

1. 为层次结构中的每个类创建一个表，其中包含为每个类定义的属性以及返回顶级的外键超类表。因此，您可能有一个车辆与其他表如 car 和飞机有一个 vehicle_id 列。

1. Create a table for each class in the hierarchy that contain the properties defined for each class and a foreign key back to the top-level superclass table. So you might have a vehicle table with other tables like car and airplane that have a vehicle_id column. The disadvantage here is that you may need to perform a lot of joins just to get one class type out.

为层次结构中的每个类创建一个表，包含所有属性。这个可以变得棘手，因为不容易在所有表维护一个通用的ID，除非你使用像序列。对于超类类型的查询将需要针对所有表的联合。

Create a table for each class in the hierarchy that contains all properties. This one can get tricky since it's not easy to maintain a common ID across all the tables unless you're using something like a sequence. A query for a superclass type would require unions against all the tables in question.

为整个类层次结构创建一个表。这消除了联接和联合，但要求所有类属性的所有列都在一个表中。您可能需要将大多数列留空，因为某些列不会应用于不同类型的记录。例如， vehicle 表可能包含一个名为 wingspan 的列，其对应于 / code>类型。如果将此列设置为NOT NULL，那么插入到表中的 Car 的任何实例都将需要 wingspan 的值虽然 NULL 的值可能更有意义。如果你让列可以为null，你可能能够解决这个与检查约束，但它可能会变得丑陋。 （单一表格继承）

Create one table for the entire class hierarchy. This eliminates joins and unions but requires that all of the columns for all class properties be in one table. You'll probably need to leave most columns nullable since some columns won't apply to records of a different type. For example, the vehicle table might contain a column called wingspan that corresponds to the Airplane type. If you make this column NOT NULL then any instance of a Car inserted into the table will require a value for wingspan even though a value of NULL might make more sense. If you leave the column nullable you might be able to work around this with check constraints but it could get ugly. (Single Table Inheritance)

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