36

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.

What are the pros and cons of EAV?

Is there an alternative database method to allow user defined attributes/fields and values?

Daniel Vassallo
  • 337,827
  • 72
  • 505
  • 443
Nic Hubbard
  • 41,587
  • 63
  • 251
  • 412

4 Answers4

37

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.

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.

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.

Daniel Vassallo
  • 337,827
  • 72
  • 505
  • 443
  • 14
    i dont see how EAV is a technical advantage : its more complicated to build, to query and to insert, and readability is very low. What would be a simple and fast "select * from" becomes a slow monster sql query. And i m not even talking about trying to do some reporting based on a EAV model. – guigui42 Aug 27 '10 at 08:40
  • @guigui42: Thanks for your comment. You have a point, but my answer was not intended to be exhaustive. Nevertheless, since it was marked as accepted by the OP, I have updated the answer with some further considerations. – Daniel Vassallo Aug 27 '10 at 09:59
  • 1
    I am a bit confused on the 'enforce referential integrity' part. I am using EAV and have many foreign keys. Multiple SQL data types can be used thanks to UNION ALL and some meta-data which maps attributes to the table (data type) they are stored in. You can greatly improve query speed by adding additional indexed columns to the entity table (like `type` for example). – mako-taco Jan 30 '14 at 13:59
  • 1
    @mako-taco It is hard to apply database integrity checks! For example how would you check if an attribute is mandatory and other one is not. And if you map the relationships in EAV table, then you can not create database relationships. – Amir Pashazadeh Dec 10 '17 at 11:32
3

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

rqmedes
  • 540
  • 2
  • 14
  • Since 2014 (9.4), Postgres has added JSONB data type support, together with GIN indexes can provide an alternative to using HStore, or the typical EAV table model. The `hstore_to_jsonb` function is available, in case you want to switch. More information here: https://www.postgresql.org/docs/9.5/datatype-json.html – NostraDavid Aug 15 '22 at 21:05
0

The Streams Platform proposes the alternative way based on Streams (actually, it's the Domain Model), Fields and Assignments entities.

Piterden
  • 771
  • 5
  • 17
-11

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.

Bittercoder
  • 11,753
  • 10
  • 58
  • 76
ChrisW
  • 54,973
  • 13
  • 116
  • 224
  • 2
    The downside to this is that you will have tons of empty fields – Nic Hubbard Feb 08 '10 at 20:13
  • 2
    @ChrisW: The scenarios where that is feasible are very limited in my opinion. – Daniel Vassallo Feb 08 '10 at 20:24
  • Some databases have support for 'sparse' columns (e.g. SQL Server) which make this approach a bit more workable. – codeulike Nov 19 '10 at 12:58
  • 1
    having been involved on improving such a model - i strongly recommend against adding new columns for every new attribute. – IEnumerator Apr 08 '11 at 13:17
  • This method doesn't seem feasible, each user can have his own set of fields and the possible number of these specific sets is high. – Oleg Mar 11 '12 at 04:32
  • But what if we add some sane maximum count (say, 100) of NVARCHAR (or whatever engine specific type) columns and name them col1 ... col100 in the database and then have a table of metadata to describe if the column is used at all and what is its purpose? This way we can get rid of the nasty joins for each EAV column. When reading data from the table, we first read the metadata and then build a straight forward select query with necessary columns. The only drawback - if we exceed the maximum, we have to add more columns, but it is simple to do, just col101 .. col201 etc. – JustAMartin Aug 25 '17 at 06:47