正确设计EAV数据库以获取历史数据

Question

介绍我一直在阅读有关EAV数据库的信息，而大多数缺点似乎都与EAV设计确实非常糟糕或很难从数据中生成报告有关。通常，当您看到人们抱怨EAV时，他们使用少于三个的表来尝试复制RDBMS中单独的表+列的功能。有时，这意味着将所有内容（从小数到字符串）都存储在单个TEXT值列中。EAV还会破坏数据完整性的安全保护措施，如果您不小心的话，这可能会很糟糕。但是，EAV确实提供了一种轻松的方式来跟踪历史数据，并允许我们在SQL和键值存储系统之间来回移动系统的某些部分。如果我们根据类型区分不同的实体属性该怎么办。除了与特定属性和实体相关的正确索引值之外，这还使我们仍然可以处理belongsTo，Has，HasMany和HasManyThrough关系。考虑以下两个基本实体products (price -> decimal, title -> string, desc -> text, etc...)    attributes        options            [...]        int        datetime        string        text        decimal        relation            [id,foreign_key]users (gender -> options, age -> int, username -> string, etc...)    attributes        options            [...]        int        datetime        string        text        decimal        relation            [id,foreign_key]RDBMS架构设计众所周知，用户资料和产品是世界上最多样化的产品。每个公司处理它们的方式都不一样，并且针对他们的需求具有不同的“列”或“属性”。以下是如何处理多个（嵌套和/或关系）实体的视图。想法是，对于每个实体都有此主属性表，然后该主表指定如何查找和解释这些值。这使我们能够处理特殊情况，例如指向其他实体的外键以及诸如“选项”或十进制数字之类的东西。查询样例首先，实体是什么“类型”？（用户，帖子，评论等。）SELECT * FROM entity_type et LEFT JOIN entity e ON e.entity_type_id = et.id WHERE e.id = ?接下来，该实体的属性是什么？（表属性）SELECT * FROM attr WHERE entity_id = ?接下来，此实体的属性中存在哪些值？（attr _ ###表）SELECT * FROM attr_option, attr_int, attr_relation, attr_text, ... WHERE entity_id = ?vsSELECT * FROM attr_option WHERE entity_id = ? if( ! multiple) ORDER BY created_at DESC LIMIT 1SELECT * FROM attr_int WHERE entity_id = ? ORDER BY created_at DESC LIMIT 1SELECT * FROM attr_relation WHERE entity_id = ? ORDER BY created_at DESC LIMIT 1SELECT * FROM attr_text WHERE entity_id = ? ORDER BY created_at DESC LIMIT 1...该实体存在什么关系？假设我们有一个ID为34的“发布”实体，并且我们想要它的“注释”（entity_type = 2），这可以允许我们在产品实体上获取评论实体ID：SELECT * FROM entity AS eLEFT JOIN attr_relation AS ar ON ar.entity_id = e.idWHERE ar.entity_id = 34 AND e.entity_type = 2;除了多个查询（无论如何，键值存储都需要这些查询）之外，这种方法还会存在哪些问题？

Answer 1

一个EAV“数据库” [原文如此]是字面上 数学 直截了当一个未记录的描述在数据库和其元数据的三元组，没有功能tablulate关系，或查询的关系，或查询的元数据，或类型检查或维护完整性，或优化，或原子交易，或控制并发。

软件工程原理规定，合理的EAV数据库的使用完全由定义适当的抽象（类型，运算符，过程，解释器，模块）组成，以重建DBMS的功能。

从一个人的EAV三元组及其含义到（碎片化的）数据库描述的映射的机械性质使此操作易于显示。

用Greenspun来解释，任何足够复杂的EAV项目都包含一个临时的，非正式指定的，漏洞百出的缓慢实现的DBMS一半。

我再说一遍：EAV是数据库及其元数据的三元组的无文档描述，没有DBMS。仅对已证明DDL解决方案不能满足性能要求并且EAV解决方案可以而且值得的数据库部分使用EAV。

Answer 2

这是此设计的一些问题。

• 您将如何查询给定实体的所有整数属性的当前值？

• 您将如何建模应该是的属性NOT NULL？也就是说，请确保给定属性是其实体的必需属性，并且没有该属性的值就无法创建该实体。

• 您将如何为UNIQUE列建模？假设您可以更改属性的值，然后将其更改回原始值。

• 您如何支持使用整数主键以外的其他内容引用实体的外键？

• 如何将给定属性限制为查找表中的值集？

解决其中大多数问题的唯一方法是使用应用程序代码。这就是EAV的问题：您最终重新发明了许多我们认为理所当然的SQL约束。这是“ 内部平台效应”反模式的一个示例：

平台内部的影响是软件架构师倾向于创建可定制的系统，以使其成为他们正在使用的软件开发平台的副本，并且通常是较差的副本。

第六范式不是EAV。在第六范式中，每个属性而不是每种数据类型都需要一个单独的表。您可以使用具有适当名称和数据类型的常规列。将此属性存储在不同的表中是使您能够存储历史修订的能力。

这意味着你仍然不能模拟NOT NULL在6NF，但至少你可以模拟UNIQUE和FOREIGN KEY在一个漂亮的传统方式。

Answer 3

“我一直在阅读有关EAV数据库的信息，而大多数的短期缺点似乎与确实，非常糟糕的EAV设计或从数据生成报告的难度有关。”

生成报告的困难固有地且不可避免地源于EAV DB所代表的事实：“人XYZ的属性BIRTHDATE的值为...”“人XYZ的属性DECEASEDATE的值为...”等。

这不是最终用户想到的用于承载有关人XYZ（或任何其他人）信息的数据结构的典型形式，在最终用户和数据库之间的某个地方是人为地，是附加的转换（非常类似于旋转，尽管不完全是100） ％） 是必要的。每个其他转换都是潜在的错误和性能损失的来源。

“通常，当您看到人们抱怨EAV时，他们使用少于三个表来尝试在RDBMS中复制单独的表+列的功能。有时这意味着将十进制到字符串存储在单个TEXT值列中。”

这只是EAV的缺点之一。属性级类型约束变得更难定义或无法定义。但是除此之外。

“ EAV还会破坏数据完整性的安全保护措施，如果您不小心的话，这将是非常糟糕的。”

这与报告生成的难度完全相关，这与表达有意义的查询的难度完全相同，而与表达构成某些特定规则的场景的难度则完全相同。

“但是，EAV确实提供了一种轻松的方式来跟踪历史数据，并允许我们在SQL和键值存储系统之间来回移动系统的一部分。”

BS和baloney。严格应用的EAV将使时间信息与其他任何“常规”属性一样远离其所应用的对象。如果您不这样做，那么您将不再（严格）应用EAV。参见Bill Karwin的回答：EAV！= 6NF !!!!!!!!! 6NF仍然具有任何其他“常规”数据库所具有的所有“结构”，EAV的全部作用（请参阅philip的回答和Bill的“内部平台”一词）有效地从数据库中删除了该结构。