实体属性值（EAV）的替代方法？ [英] Alternatives to Entity-Attribute-Value (EAV)?
问题描述
我们的数据库是基于EAV（Entity-Attribute-Value）模型设计的。
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?
干杯，
Mosh
Cheers, Mosh
推荐答案
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数据库，它没有人们发布的问题，它执行得很漂亮，客户很高兴（没有进一步的工作是一个完整的功能满意的迹象）。
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屋！
