database 数据库继承的技术？

Techniques for database inheritance?

提问by chakrit

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?

有哪些可用的技术来设计数据库？各有利弊？

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?

ps 我搜索并发现了几个关于数据库继承的问题，但大多数是关于更改为原生支持它的数据库引擎。但是，假设我被 SQL Server 2005 困住了……我有哪些选择？

回答by cliff.meyers

Three common strategies:

三种常用策略：

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 vehicletable with other tables like carand airplanethat have a vehicle_idcolumn. The disadvantage here is that you may need to perform a lot of joins just to get one class type out.

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

3. 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 vehicletable might contain a column called wingspanthat corresponds to the Airplanetype. If you make this column NOT NULL then any instance of a Carinserted into the table will require a value for wingspaneven though a value of NULLmight 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)

1. 为层次结构中的每个类创建一个表，其中包含为每个类定义的属性和返回顶级超类表的外键。因此，您可能有一个vehicle带有其他表的表car，airplane并且有一个vehicle_id列。这里的缺点是您可能需要执行大量连接才能获得一种类类型。

2. 为包含所有属性的层次结构中的每个类创建一个表。这可能会变得棘手，因为除非您使用诸如序列之类的东西，否则在所有表中维护一个公共 ID 并不容易。对超类类型的查询需要针对所有相关表进行联合。

3. 为整个类层次结构创建一张表。这消除了连接和联合，但要求所有类属性的所有列都在一个表中。您可能需要将大多数列保留为可空，因为某些列不适用于不同类型的记录。例如，该vehicle表可能包含一个名为的列wingspan，该列对应于该Airplane类型。如果将此列Car设为NOT NULL，则插入表中的任何实例都将需要一个值 forwingspan即使值NULL可能更有意义。如果您将列保留为可空，您可能可以使用检查约束来解决此问题，但它可能会变得丑陋。（单表继承）

回答by jjm340

Be careful with database inheritance in certain situations - we implemented it in our application for our auditing strategy and we ended up with a performance bottleneck/nightmare.

在某些情况下要小心数据库继承 - 我们在我们的审计策略的应用程序中实现了它，我们最终遇到了性能瓶颈/噩梦。

The problem was that the base table we used was insert only and rapidly changing so what we ended up with were deadlocks allover the place. We are currently planning to break these apart into their own tables because the headache of having the same columns in 15 different tables versus a performance nightmare is well worth it. This was also compounded by the fact that the entity framework doesn't necessarily handle inheritance efficiently (this is a known issue by Microsoft).

问题是，我们所用的基表是只插入和迅速变化的所以我们结束了被死锁所有的地方。我们目前正计划将这些拆分成他们自己的表，因为在 15 个不同的表中拥有相同的列而不是性能噩梦是值得的。实体框架不一定有效地处理继承（这是 Microsoft 的一个已知问题）这一事实也使情况更加复杂。

Anyway, just thought I'd share some knowledge since we've been through the wringer on this issue.

无论如何，只是想我会分享一些知识，因为我们已经解决了这个问题。

回答by Ken Yao

Chapter 8. Inheritance Mapping in following link also discussed this. http://nhibernate.info/doc/nh/en/index.html#inheritance

以下链接中的第 8 章继承映射也讨论了这一点。http://nhibernate.info/doc/nh/en/index.html#inheritance

It is the NHibernate document.

它是 NHibernate 文档。