数据库EAV优点/缺点和替代方案 [英] Database EAV Pros/Cons and Alternatives

问题描述

我一直在寻找一个数据库解决方案来允许用户定义的字段和值（允许无限数量）。乍看之下，EAV似乎是正确的，但经过一番阅读，我不太清楚。

I have been looking for a database solution to allow user defined fields and values (allowing an unlimited number). At first glance, EAV seemed like the right fit, but after some reading I am not sure anymore.

EAV的利弊是什么？

What are the pros and cons of EAV?

是否有替代数据库方法来允许用户定义的属性/字段和值？

推荐答案

这不是一个详尽的答案，只是几个问题。

This is not to be considered an exhaustive answer, but just a few points on the topic.

由于问题也被标记为 [sql] 标签，让我说一般来说，关系数据库不是特别适合使用 EAV 模型。您仍然可以在SQL中设计一个EAV模型，但是您将不得不牺牲关系数据库提供的许​​多优点。不仅您无法强制引用完整性，使用SQL数据类型获取值并强制执行强制属性，但即使非常基本的查询也可能变得难以编写。事实上，为了克服这个限制，几个EAV解决方案依赖于数据重复，而不是加入相关的表，你可以想像，有很多缺点。

Since the question is also tagged with the [sql] tag, let me say that, in general, relational databases aren't particularly suitable for storing data using the EAV model. You can still design an EAV model in SQL, but you will have to sacrifice many advantages that a relational database would give. Not only you won't be able to enforce referential integrity, use SQL data types for values and enforce mandatory attributes, but even the very basic queries can become difficult to write. In fact, to overcome this limitation, several EAV solutions rely on data duplication, instead of joining with related tables, which as you can imagine, has plenty of drawbacks.

如果您真的需要一个无模式设计，允许无限数量的属性，最好的方法是使用 NoSQL 解决方案。即使EAV相对于关系数据库的弱点也适用于NoSQL替代品，您将获得使用常规SQL数据库难以实现的其他功能。例如，通常，NoSQL数据存储可以比关系数据库简单易用，因为它们是为了解决某种可扩展性问题而设计的，并且它们故意丢弃使扩展困难的功能。

If you really require a schemaless design, "allowing an unlimited number of attributes", your best bet is probably to use a NoSQL solution. Even though the weaknesses of EAV relative to relational databases also apply to NoSQL alternatives, you will be offered additional features that are difficult to achieve with conventional SQL databases. For example, usually NoSQL datastores can be scaled much easier than relational databases, simply because they were designed to solve some sort of scalability problem, and they intentionally dropped features that make scaling difficult.

许多云计算平台（例如由 Amazon提供的平台， Google 和 Microsoft ）具有基于EAV模型的数据存储，其中任意数量的属性可以与给定实体相关联。如果您正在考虑将应用程序部署到云端，那么您可以将其视为业务优势以及技术优势，因为大厂商之间的强劲竞争将价值成本比率推高到非常高的水平，不断推出功能，降低财务和实施成本。

Many cloud computing platforms (such as those offered by Amazon, Google and Microsoft) are featuring datastores based on the EAV model, where an arbitrary number of attributes can be associated with a given entity. If you are considering deploying your application to the cloud, you may consider this both as a business advantage, as well as a technical one, because the strong competition between the big vendors is pushing the value-to-cost ratios to very high levels, by continually pushing up on the features and pushing down the financial and implementation costs.

这篇关于数据库EAV优点/缺点和替代方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！