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?
EAV的利弊是什么?
Is there an alternative database method to allow user defined attributes/fields and values?
是否有另一种数据库方法允许用户定义属性/字段和值?
4 个解决方案
#1
27
This is not to be considered an exhaustive answer, but just a few points on the topic.
这并不是一个详尽的答案，而是关于这个话题的几点。
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.
由于这个问题也被加上了[sql]标签，我想说的是，一般来说，关系数据库并不特别适合使用EAV模型存储数据。您仍然可以在SQL中设计EAV模型，但是您必须牺牲关系数据库所提供的许多优势。不仅您不能强制引用完整性、为值使用SQL数据类型和强制属性，甚至连最基本的查询也很难编写。实际上，为了克服这个限制，一些EAV解决方案依赖于数据复制，而不是与相关的表连接，您可以想象，这些表有很多缺点。
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.
如果您确实需要一个无模式设计，“允许无限数量的属性”，那么您最好的选择可能是使用NoSQL解决方案。尽管EAV相对于关系数据库的缺点也适用于NoSQL替代品，但您将获得常规SQL数据库难以实现的附加特性。例如，通常情况下，NoSQL数据存储比关系数据库更容易伸缩，这仅仅是因为它们的设计目的是解决某种可伸缩性问题，而且它们故意放弃了使扩展变得困难的特性。
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.
许多云计算平台(如Amazon、谷歌和Microsoft提供的平台)都基于EAV模型提供数据存储，其中任意数量的属性可以与给定的实体相关联。如果你正在考虑将应用程序部署到云,你会认为这既是一个业务优势,以及技术方面,因为大厂商之间的激烈竞争是推动value-to-cost比率非常高的水平,通过不断推高的特性和降低金融和实现成本。
#2
2
Have a look at posgtres hstore http://www.postgresql.org/docs/9.0/static/hstore.html this will do exactly what you want without most of the disadvantages
查看一下posgtres hstore: http://www.postgresql.org/docs/9.0/static/hstore.html，这将完全实现您想要的，而不是大多数的缺点?
#3
0
The Streams Platform proposes the alternative way based on Streams (actually, it's the Domain Model), Fields and Assignments entities.
Streams平台提出了一种基于流(实际上是领域模型)、字段和分配实体的替代方法。
#4
-9
Is there an alternative database method to allow user defined attributes/fields and values?
是否有另一种数据库方法允许用户定义属性/字段和值?
One alternative is to change the database schema based on user input: for example when the user wants a new field, then add a corresponding column to the database.
另一种方法是根据用户输入更改数据库模式:例如，当用户想要一个新字段时，然后在数据库中添加相应的列。
#1
27
This is not to be considered an exhaustive answer, but just a few points on the topic.
这并不是一个详尽的答案，而是关于这个话题的几点。
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.
由于这个问题也被加上了[sql]标签，我想说的是，一般来说，关系数据库并不特别适合使用EAV模型存储数据。您仍然可以在SQL中设计EAV模型，但是您必须牺牲关系数据库所提供的许多优势。不仅您不能强制引用完整性、为值使用SQL数据类型和强制属性，甚至连最基本的查询也很难编写。实际上，为了克服这个限制，一些EAV解决方案依赖于数据复制，而不是与相关的表连接，您可以想象，这些表有很多缺点。
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.
如果您确实需要一个无模式设计，“允许无限数量的属性”，那么您最好的选择可能是使用NoSQL解决方案。尽管EAV相对于关系数据库的缺点也适用于NoSQL替代品，但您将获得常规SQL数据库难以实现的附加特性。例如，通常情况下，NoSQL数据存储比关系数据库更容易伸缩，这仅仅是因为它们的设计目的是解决某种可伸缩性问题，而且它们故意放弃了使扩展变得困难的特性。
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.
许多云计算平台(如Amazon、谷歌和Microsoft提供的平台)都基于EAV模型提供数据存储，其中任意数量的属性可以与给定的实体相关联。如果你正在考虑将应用程序部署到云,你会认为这既是一个业务优势,以及技术方面,因为大厂商之间的激烈竞争是推动value-to-cost比率非常高的水平,通过不断推高的特性和降低金融和实现成本。
#2
2
Have a look at posgtres hstore http://www.postgresql.org/docs/9.0/static/hstore.html this will do exactly what you want without most of the disadvantages
查看一下posgtres hstore: http://www.postgresql.org/docs/9.0/static/hstore.html，这将完全实现您想要的，而不是大多数的缺点?
#3
0
The Streams Platform proposes the alternative way based on Streams (actually, it's the Domain Model), Fields and Assignments entities.
Streams平台提出了一种基于流(实际上是领域模型)、字段和分配实体的替代方法。
#4
-9
Is there an alternative database method to allow user defined attributes/fields and values?
是否有另一种数据库方法允许用户定义属性/字段和值?
One alternative is to change the database schema based on user input: for example when the user wants a new field, then add a corresponding column to the database.
另一种方法是根据用户输入更改数据库模式:例如，当用户想要一个新字段时，然后在数据库中添加相应的列。