实体-属性-值 (EAV) 的替代品? [英] Alternatives to Entity-Attribute-Value (EAV)?
问题描述
我们的数据库是基于 EAV(实体-属性-值)模型设计的.那些使用过 EAV 模型的人都知道为了灵活性而带来的所有废话.
Our database is designed based on EAV (Entity-Attribute-Value) model. Those who have worked with EAV models know all the crap that comes with for the purpose of flexibility.
我向我的客户询问了使用 EAV 模型(灵活性)的原因，他们的回答是:他们的实体会随着时间而变化.所以，今天他们可能有一个包含一些属性的表，但在一个月的时间里，可能会添加一些新的属性，或者可能会重命名现有的属性.他们需要生成报告以及时返回到任何阶段，并根据该阶段的实体形状查询数据.
I asked my client about the reasons why using EAV model (flexibility), and their response was: Their entities change over time. So, today they may have a table with a few attributes, but in a month time, a few new attributes may be added, or an existing attribute may be renamed. They need to produce reports to get back to any stage in time and query the data based on the shape of entities at that stage.
我知道这对于传统的关系模型是不可行的，但我个人认为 EAV 是反模式.是否有其他替代模型使我们能够捕获实体和实例更改的时间维度?
I understand this is not feasible with a conventional relational model, but I personally see EAV as anti-pattern. Are there any other alternative models that enables us to capture the time dimension in changes to the entities and instances?
干杯，莫什
推荐答案
EAV 做的好还是坏是有区别的；5NF 由熟练的人或无知的人完成.
There is a difference between EAV done faithfully or badly; 5NF done by skilled people or by those who are clueless.
第六范式是不可约范式(无法进一步归一化).它消除了许多常见问题，例如空问题，并提供了识别缺失值的最终方法.它是学术和技术上强大的 NF.没有产品支持，也不常用.为了正确和一致地实施，它需要一个要实施的元数据目录.当然，导航它所需的 SQL 变得更加麻烦(SQL 已经很麻烦重新连接)，但是通过从元数据自动生成 SQL 可以轻松克服这一问题.
Sixth Normal Form is the Irreducible Normal Form (no further Normalisation is possible). It eliminates many of the problems that are common, such as The Null Problem, and provides the ultimate method identifying missing values. It is the academically and technically robust NF. There are no products to support it, and it is not commonly used. To be implemented properly and consistently, it requires a catalogue for metadata to be implemented. Of course, the SQL required to navigate it becomes even more cumbersome (SQL already being cumbersome re joins), but this is easily overcome by automating the production of SQL from the metadata.
EAV 是 6NF 的部分集或子集.问题是，它通常是出于某种目的(允许添加列而无需进行 DDL 更改)，并且是由不了解 6NF 且未实现元数据的人完成的.关键是，6NF 和 EAV 作为原则和概念提供了实质性的好处，并提高了性能；但通常没有得到妥善实施，并没有实现收益.不少EAV实现是灾难，不是因为EAV不好，而是因为实现不好.
EAV is a partial set or a subset of 6NF. The problem is, usually it is done for a purpose (to allow columns to be added without having to make DDL changes), and by people who are not aware of the 6NF, and who do not implement metadata. The point is, 6NF and EAV as principles and concepts offer substantial benefits, and performance increases; but commonly it is not implemented properly, and the benefits are not realised. Quite a few EAV implementations are disasters, not because EAV is bad, but because the implementation is poor.
例如.有些人认为从 6NF/EAV 数据库构建 3NF 行所需的 SQL 很复杂:不，它很麻烦但并不复杂.更重要的是，可以提供一个普通的SQL VIEW，让所有的用户和报表工具看到的只是直接的3NF VIEW，6NF/EAV问题对他们来说是透明的.最后，所需的 SQL 可以自动化，因此很多人所承受的人工成本是非常不必要的.
Eg. Some people think that the SQL required to construct the 3NF rows from the 6NF/EAV database is complex: no, it is cumbersome but not complex. More important, an ordinary SQL VIEW can be provided, so that all users and report tools see only the straight 3NF VIEW, and the 6NF/EAV issues are transparent to them. Last, the SQL required can be automated, so the labour cost that many people endure is quite unnecessary.
所以答案确实是，第六范式，作为EAV之父，而更纯粹的形式，是它的替代品.警告是，确保正确完成.我有一个大型 6NF db，它没有遇到人们发布的任何问题，它表现出色，客户非常满意(没有进一步的工作是完全满意的标志).
So the answer really is, Sixth Normal Form, being the father of EAV, and a purer form, is the replacement for it. The Caveat is, ensure it is done properly. I have one large 6NF db, and it suffers none of the problems people post about, it performs beautifully, the customer is very happy (no further work is a sign of complete functional satisfaction).
我已经发布了另一个问题的非常详细的答案，该答案也适用于您的问题，您可能会感兴趣.
I have already posted a very detailed answer to another question which applies to your question as well, which you may be interested in.
这篇关于实体-属性-值 (EAV) 的替代品?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
