数据库 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屋！