Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I'm thinking to use EAV for some of the stuff in one of the projects, but all questions about it in stackoverflow end up to answers calling EAV an anti pattern.

But I'm wondering, if is it that wrong in all cases?

Let's say shop product entity, it has common features, like name, description, image, price, etc., that take part in logic many places and has (semi)unique features, like watch and beach ball would be described by completely different aspects. So I think EAV would fit for storing those (semi)unique features?

All this is assuming, that for showing product list, it is enough info in product table (that means no EAV is involved) and just when showing one product/comparing up to 5 products/etc. data saved using EAV is used.

I've seen such approach in Magento commerce and it is quite popular, so may be there are cases, when EAV is reasonable?

share|improve this question
    
So much text and nobody bothered to spell out what EAV means? –  busy_wait Jun 7 at 20:22
    
@busy_wait "Entity-Attibute-Value" tables - see Entity–attribute–value model on Wikipedia. –  Ross Patterson Jun 8 at 13:13
add comment

4 Answers

up vote 15 down vote accepted

This is a good forum discussion on this very topic and good points are brought up on both sides of the argument.

http://www.dbforums.com/database-concepts-design/1619660-otlt-eav-design-why-do-people-hate.html

EAV gives a flexibility to the developer to define the schema as needed and this is good in some circumstances.

On the other hand it performs very poorly in the case of an ill-defined query and can support other bad practices.

In other words, EAV gives you enough rope to hang yourself and in this industry, things should be designed to the lowest level of complexity because the guy replacing you on the project will likely be an idiot.

share|improve this answer
add comment

Let's say shop product entity, it has common features, like name, description, image, price, etc., that take part in logic many places and has (semi)unique features, like watch and beach ball would be described by completely different aspects. So I think EAV would fit for storing those (semi)unique features?

Using an EAV structure for has several implications that are trade offs.

You are trading off a 'less space for the row because you don't have 100 columns that are null' against 'more complex queries and model'.

Having an EAV typically means the value is a string that one can stuff any data into. This then has implications on validity and constraint checking. Consider the situation where you've put the number of batteries used as something in the EAV table. You want to find a flashlight that uses C sized batteries, but less than 4 of them.

select P.sku
from
  products P
  attrib Ab on (P.sku = Ab.sku and Ab.key = "batteries")
  attrib Ac on (P.sku = Ac.sku and Ac.key = "count")
where
  cast(Ac.value as int) < 4
  and Ab.value = 'C'
  ...

The thing to realize here is that you can't use an index reasonably on the value. You also can't prevent someone from putting in something that isn't an integer there, or an invalid integer (uses '-1' batteries) because the value column is used again and again for different purposes.

This then has implications in trying to write a model for the product. You'll have the nice typed values... but you're also going to have a Map<String,String> just sitting there with all sorts of stuff in it. This then has further implications when serializing it to XML or Json and the complexities of trying to do validation or queries against those structures.

Some alternatives or modifications to the pattern to consider is instead of a free form key, to have another table with valid keys. It means instead of doing string comparisons in the database, you are checking against the equality of foreign key ids. Changing the key itself is done in one spot. You've got a known set of keys which means that they can be done as an enum.

You could also have related tables that contain attributes of a specific class of product. A grocery department could have another table that has several attributes associated with it that the building materials doesn't need (and vice versa).

+----------+    +--------+    +---------+
|Grocery   |    |Product |    |BuildMat |
|id (fk)   +--->|id (pk) |<---+id (fk)  |
|expiration|    |desc    |    |material |
|...       |    |img     |    |...      |
+----------+    |price   |    +---------+
                |...     |               
                +--------+               

There are times that especially call for a EAV table.

Consider the situation where you aren't just writing a inventory system for your company where you know every product and every attribute. You are now writing an inventory system to sell to other companies. You can't know every attribute of every product - they will need to define them.

One idea that comes out is "we'll let the customer modify the table" and this is just bad (you get into meta-programming for table structures because you no longer know what is where, they can royally mess up the structure or corrupt the application, they've got the access to do wrong things and the implications of that access become significant). There's more about this path at MVC4 : How to create model at run time?

Instead, you create the administrative interface to an EAV table and allow that to be used. If the customer wants to create an entry for 'polkadots' it goes into the EAV table and you already know how to deal with that.

An example of this can be seen in the database model for Redmine you can see the custom_fields table, and the custom_values table -- those are parts of the EAV that allows the system to be extended.


Note that if you find your entire table structure to look like EAV rather than relational, you might want to look at the KV flavor of NoSQL (cassandra, redis, Mongo,. ...). Realize that these often come with other tradeoffs in their design that may or may not be appropriate to what you are using it for. However, they are specifically designed with the intent of an EAV structure.

You may wish to read SQL vs NoSQL for an inventory management system

Following this approach with a document oriented NoSQL database (couch, mongo), you could consider each inventory item to be a document on a disk... pulling up everything in a single document is fast. Furthermore, the document is structured so that you can pull out any one single thing fast. On the other hand, searching all the documents for things that match a particular attribute can have less performance (compare using 'grep' against all the files)... its all a trade off.

Another approach would be LDAP where one would have a base with all of its associated items, but would then also have additional object classes applied to it for the other types of items. (see System Inventory Using LDAP)

Once you go down this path, you may find something that exactly matches what you are looking for... though everything comes with some tradeoffs.

share|improve this answer
add comment

In a nutshell, EAV is useful when your list of attributes is frequently growing, or when it's so large that most rows would be filled with mostly NULLs if you made every attribute a column. It becomes an anti-pattern when used outside of that context.

share|improve this answer
2  
I would replace "frequently" by "needs the possibilty to be changed at run-time". –  Doc Brown Jun 7 at 17:26
add comment

Typically people look the other way if you're using it for lookup tables, or other situations where the benefit is to keep from having to create tables for one or two stored values. The situation you're describing, where you're basically storing item properties, sounds perfectly normal (and normalized). Widening a table to store a variable number of item attributes is a bad idea.

For the general case of storing disparate data in a long thin table...You shouldn't be afraid to create new tables if you need to, and having just one or two long skinny tables isn't much better than having only one or two short fat tables.

That being said, I am notorious for using EAV tables for logging. They do have some good utility.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.