数据库继承的技术? [英] 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?

假设我有一个经典的例子:

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?

附言我搜索并发现了几个关于数据库继承的问题，但大多数是关于更改为原生支持它的数据库引擎.但是，假设我坚持使用 SQL Server 2005...我有哪些选择?

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. 为层次结构中的每个类创建一个表，其中包含为每个类定义的属性和返回顶级超类表的外键.因此，您可能有一个 vehicle 表和其他表，例如 car 和 airplane，它们具有 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 的列，它对应于 Airplane 类型.如果您将此列设为 NOT NULL，则插入表中的 Car 的任何实例都将需要 wingspan 的值，即使 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屋！