数据库设计中的继承 [英] Inheritance in Database Design

问题描述

我正在设计一个新的实验室数据库，其中包含许多类型的主要实体。

I am designing a new laboratory database with MANY types of my main entities.

每个实体的表将包含该实体的所有类型共有的字段（entity_id ，created_on，created_by等）。然后我将使用具体的继承（每个唯一属性集的单独表）来存储所有剩余的字段。

The table for each entity will hold fields common to ALL types of that entity (entity_id, created_on, created_by, etc). I will then use concrete inheritance (separate table for each unique set of attributes) to store all remaining fields.

我相信这是标准类型的最佳设计每天通过实验室的数据。但是，我们经常会有一些特殊的样本，这些样本通常都附有发起者想要存储的特定值。

I believe that this is the best design for the standard types of data which come through the laboratory daily. However, we often have a special samples which often are accompanied by specific values the originator wants stored.

问题：我应该如何建模特殊（非标准类型的实体？

Question: How should I model special (non-standard) types of entities?

选项1：对特殊字段使用实体值

一个表（ entity_id ， attribute_name ， numerical_value ）将保存所有数据对于任何特殊实体。

+更少的表。

- 不能强制要求特定属性。

- 必须将行转换（转）到列这是效率低下的。

Option 1: Use entity-value for special fields
One table (entity_id, attribute_name, numerical_value) would hold all data for any special entity.
+ Fewer tables.
- Cannot enforce requiring a particular attribute.
- Must convert (pivot) rows to columns which is inefficient.

选项2：严格的具体继承。

为每个创建单独的表单独的特殊情况。

+按照所有其他规则进行跟进

- 只有几行的许多表的开销。

Option 2: Strict concrete inheritance.
Create separate table for each separate special case.
+ Follows in accordance with all other rules
- Overhead of many tables with only a few rows.

选项3：具有不同用户下特殊表格的具体继承。

全部放入不同用户下的pecial表。

+保持所有特殊表和标准表分开。

+更容易在列表中搜索通用标准表而无需搜索所有特殊表。

- 只有几行的许多表的开销。

Option 3: Concrete inheritance with special tables under a different user.
Put all special tables under a different user.
+ Keeps all special and standard tables separate.
+ Easier to search for common standard table in a list without searching through all special tables.
- Overhead of many tables with only a few rows.

推荐答案

其实您描述的设计（公用表格加特定于子类型的表格）称为类表继承。

Actually the design you described (common table plus subtype-specific tables) is called Class Table Inheritance.

具体表继承将在子类型表中重复所有公共属性，并且您现在没有超类型表。

Concrete Table Inheritance would have all the common attributes duplicated in the subtype tables, and you'd have no supertype table as you do now.

我强烈反对EAV。我认为它是一个SQL反模式。它可能看起来像一个优雅的解决方案，因为它需要更少的表，但你后来为自己设置了很多头痛。你发现了一些缺点，但还有很多其他缺点。恕我直言，只有当您引入新的子类型时，绝不能创建新表，或者如果您有无限数量的子类型（例如，用户可以定义新的属性），则仅适当使用EAV。

I'm strongly against EAV. I consider it an SQL antipattern. It may seem like an elegant solution because it requires fewer tables, but you're setting yourself up for a lot of headache later. You identified a couple of the disadvantages, but there are many others. IMHO, EAV is used appropriately only if you absolutely must not create a new table when you introduce a new subtype, or if you have an unbounded number of subtypes (e.g. users can define new attributes ad hoc).

你有很多子类型，但仍然是有限数量的子类型，所以如果我在做这个项目，我会坚持类表继承 。每个子类型可能只有几行，但至少可以确保每个子类型中的所有行都具有相同的列，如果需要，可以使用 NOT NULL ，你可以使用SQL数据类型，你可以使用参照完整性约束等。从关系的角度来看，它比EAV更好的设计。

You have many subtypes, but still a finite number of them, so if I were doing this project I'd stick with Class Table Inheritance. You may have few rows of each subtype, but at least you have some assurance that all rows in each subtype have the same columns, you can use NOT NULL if you need to, you can use SQL data types, you can use referential integrity constraints, etc. From a relational perspective, it's a better design than EAV.

你做的另一个选择'提及称为序列化LOB 。也就是说，为自定义属性的半结构化集合添加BLOB列。在该列中存储XML，YAML，JSON或您自己的 DSL 。您将无法使用SQL轻松解析BLOB中的各个属性，您必须将整个BLOB提取回应用程序并在代码中提取单个属性。所以在某些方面它不太方便。但如果这满足了你对数据的使用，那就没有错。

One more option that you didn't mention is called Serialized LOB. That is, add a BLOB column for a semi-structured collection of custom attributes. Store XML, YAML, JSON, or your own DSL in that column. You won't be able to parse individual attributes out of that BLOB easily with SQL, you'll have to fetch the whole BLOB back into your application and extract individual attributes in code. So in some ways it's less convenient. But if that satisfies your usage of the data, then there's nothing wrong with that.

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