Entity-Attribute-Value Table Design

by

I’m going to offer a contrary opinion to most of the comments on this question. While EAV is EVIL for all of the reasons that you can find thoroughly explained many times here on SO and DBA.SE and elsewhere, there is one really common application for which most of the things that are wrong with EAV are largely irrelevant and the (few) advantages of EAV are very much germane. That application is online product catalogs.

The main problem with EAV is that it doesn’t let the database do what it is really good at doing, which is helping to give proper context to different attributes of information about different entities by arranging them in a schema. Having a schema brings many, many advantages around accessing, interpreting and enforcing integrity of your data.

The fact about product catalogs is that the attributes of a product are almost entirely irrelevant to the catalog system itself. Product catalog systems do (at most) three things with product attributes.

  1. Display the product attributes in a list to end users in the form: {attribute name}: {attribute value}.

  2. Display the attributes of multiple products in a comparison grid where attributes of different products line up against each other (products are usually columns, attributes are usually rows)

  3. Drive rules for something (e.g. pricing) based on particular attribute/value combinations.

If all your system does is regurgitate information that is semantically irrelevant (to the system) then the schema for this information is basically unhelpful. In fact the schema gets in the way in an online product catalog, especially if your catalog has many diverse types of products, because you’re always having to go back into the schema to tinker with it to allow for new product categories or attribute types.

Because of how it’s used, even the data type of an attribute value in a product catalog is not necessarily (vitally) important. For some attributes you may want to impose contraints, like “must be a number” or “must come from this list {…}”. That depends on how important attribute consistency is to your catalog and how elaborate you want your implementation to be. Looking at the product catalogs of several online retailers I’d say most are prepared to trade off simplicity for consistency.

Yes, EAV is evil, except when it isn’t.

More Related Contents:

  1. How to pivot a MySQL entity-attribute-value schema
  2. Is it possible to query a tree structure table in MySQL in a single query, to any depth?
  3. MySQL – Conditional Foreign Key Constraints
  4. Remove Primary Key in MySQL
  5. Modeling Product Variants
  6. Difference between two table structure
  7. Strings as Primary Keys in MYSQL Database [closed]
  8. When to use a left outer join?
  9. Delete the entire content of all table in a Database [closed]
  10. Delete all Duplicate Rows except for One in MySQL? [duplicate]
  11. Calculate a running total in MySQL
  12. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  13. MySQL – Operand should contain 1 column(s)
  14. How to get the difference in years from two different dates?
  15. Restore table structure from frm and ibd files
  16. UNION query with codeigniter’s active record pattern
  17. INSERT … ON DUPLICATE KEY (do nothing)
  18. Group mysql query by 15 min intervals
  19. Error Code: 1406. Data too long for column – MySQL
  20. Using Count to find the number of occurrences
  21. Is there any reason to worry about the column order in a table?
  22. Checking a table for time overlap?
  23. Does mysql have the equivalent of Oracle’s “analytic functions”?
  24. MySQL INSERT INTO … VALUES and SELECT
  25. MYSQL select DISTINCT values in two columns
  26. What are practical differences between `REPLACE` and `INSERT … ON DUPLICATE KEY UPDATE` in MySQL?
  27. How do you force mysql LIKE to be case sensitive? [duplicate]
  28. #1060 – Duplicate column name ‘id’
  29. In what order are MySQL JOINs evaluated?
  30. Inventory management with stock options

Leave a Comment