Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Noob question, sorry if it's too basic for this exchange. We process a routine data feed from a client who just refactored their database from a form that seems familiar (one row per entity, one column per attribute) to one that seems unfamiliar to me (one row per entity per attribute):

Before: one column per attribute

ID   Ht_cm   wt_kg   Age_yr  ... 
1      190      82     43    ...
2      170      60     22    ...
3      205      90     51    ...

After: one column for all attributes

ID    Metric   Value
 1     Ht_cm     190
 1     Wt_kg     82
 1     Age_yr    43
 1      ...
 2     Ht_cm     170
 2     Wt_kg     60
 2     Age_yr    22
 2     ...
 3     Ht_cm     205
 3     Wt_kg     90
 3     Age_yr    51
 3     ...

Is there a name for this database structure? What are the relative advantages? The old way seems easier to place validity constraints on specific attributes (non-null, non-negative, etc.) and easier to calculate averages. But I can see how it might be easier to add new attributes without refactoring the database. Is this a standard/preferred way of structuring data?

share|improve this question
add comment

5 Answers 5

up vote 55 down vote accepted
+100

It's called Entity-Attribute-Value (also sometimes 'name-value pairs') and it's a classic case of "a round peg in a square hole" when people use the EAV pattern in a relational database.

Here's a list of why you shouldn't use EAV:

  • You can't use data types. It doesn't matter if the value is a date, a number or money (decimal). It's always going to be coerced to varchar. This can be anything from a minor performance problem to a massive gut-ache (ever had to chase down a one-cent variation in a monthly roll-up report?).
  • You can't (easily) enforce constraints. It requires a ridiculous amount of code to enforce "Everyone needs to have a height between 0 and 3 metres" or "Age must be not null and >= 0", as opposed to the 1-2 lines that each of those constraints would be in a properly-modelled system.
  • Related to above, you can't easily guarantee that you get the information you need for each client (age might be missing from one, then the next might be missing their height etc.). You can do it, but it's a hell of a lot more difficult than SELECT height, weight, age FROM Client where height is null or weight is null.
  • Related again, duplicate data is a lot harder to detect (what happens if they give you two ages for one client? De-EAVing the data, as below, will give you two rows of results if you have one attribute doubled. If one client has two separate entries for two attributes, you'll get four rows from the query below).
  • You can't even guarantee that the attribute names are consistent. "Age_yr" might become "AGE_IN_YEARS" or "age". (Admittedly this is less of a problem when you're receiving an extract versus when people are inserting data, but still.)
  • Any sort of nontrivial query is a complete disaster. To relationalise a three-attribute EAV system so you can query it in a rational fashion requires three joins of the EAV table.

Compare:

SELECT cID.ID AS [ID], cH.Value AS [Height], cW.Value AS [Weight], cA.Value AS [Age]
FROM (SELECT DISTINCT ID FROM Client) cID 
      LEFT OUTER JOIN 
    Client cW ON cID.ID = cW.ID AND cW.Metric = "Wt_kg" 
      LEFT OUTER JOIN 
    Client cH ON cID.ID = cH.ID AND cW.Metric = "Ht_cm" 
      LEFT OUTER JOIN 
    Client cA ON cID.ID = cA.ID AND cW.Metric = "Age_yr"

To:

SELECT c.ID, c.Ht_cm, c.Wt_kg, c.Age_yr
FROM Client c

Here's a (very short) list of when you should use EAV:

  • When there's absolutely no way around it and you have to support schema-less data in your database.
  • When you just need to store "stuff" and don't expect to have to need it in a more structured form. Beware, though, the monster called "changing requirements".

I know I just spent this entire post detailing why EAV is a terrible idea in most cases - but there are a few cases where it's needed/unavoidable. however, most of the time (including the example above), it's going to be far more hassle than it's worth. If you have a requirement for wide support of EAV-type data input, you should look at storing them in a key-value system, e.g. Hadoop/HBase, CouchDB, MongoDB, Cassandra, BerkeleyDB.

share|improve this answer
3  
+1 with a minor notice: you can use data types if you put the values of different types in different tables (well, not classic EAV, but sort of improvement). (But then there comes an additional question: how do you know the type of a new attribute?) –  dezso Jul 12 '12 at 6:37
3  
Agreed, but I would add that EAV is also a good approach to use when you are keeping a list of things which are semantically irrelevant to your system (not just schema-less). For example an online product catalog where the product features need to be stored and listed. You have a list of key/value pairs to regurgitate, but the system doesn't actually know or care about what those keys or values are about. In that situation, the perils of EAV are irrelevant. –  Joel Brown Jul 12 '12 at 12:03
8  
@JoelBrown You don't care NOW, but if down the road a VP asks to know how many shirts in the catalog have both brown buttons and button down collars, it'll be a bitch of a query to write. EAV itself normally indicates a lack of planning or foresight. –  JNK Jul 12 '12 at 12:36
2  
@JoelBrown I'm not disagreeing that it has a (very small very narrow) use. But if the info is likely to ever be queried in any structured fashion it probably should not be in EAV –  JNK Jul 12 '12 at 16:58
4  
@JoelBrown If your business requirements or the data you're storing changes, so should your data model. Your data model shouldn't be carved in stone. Also, for a relational database, 99% of the time people use EAV their reasoning boils down to "I don't want to spend time thinking about how to store my data" rather than "Considering all the database patterns and models I know, EAV works best for this data set". To repeat - there are cases where EAV is useful (and maybe even the 'right' answer), but they're few and far between. –  Simon Righarts Jul 13 '12 at 0:12
show 8 more comments

Entity Attribute Value (EAV)

It is considered to be an anti-pattern by many, including me.

Here are your alternatives:

  1. use database table inheritance

  2. use XML data and SQLXML functions

  3. use a nosql database, like HBase

share|improve this answer
3  
Definitely an anti-pattern for most use cases. If you have a really small data set and performance doesn't matter it may work for you. –  JNK Jul 12 '12 at 2:58
add comment

In PostgreSQL, one very good way to deal with EAV structures is the additional module hstore, available for version 8.4 or later. I quote the manual:

This module implements the hstore data type for storing sets of key/value pairs within a single PostgreSQL value. This can be useful in various scenarios, such as rows with many attributes that are rarely examined, or semi-structured data. Keys and values are simply text strings.

share|improve this answer
add comment

If you have a database that is using the EAV structure, it is possible to query the data a variety of ways.

@Simon's answer already shows how to perform a query using multiple joins.

Sample Data Used:

CREATE TABLE yourtable ([ID] int, [Metric] varchar(6), [Value] int);

INSERT INTO yourtable ([ID], [Metric], [Value])
VALUES (1, 'Ht_cm', 190),
    (1, 'Wt_kg', 82),
    (1, 'Age_yr', 43),
    (2, 'Ht_cm', 170),
    (2, 'Wt_kg', 60),
    (2, 'Age_yr', 22),
    (3, 'Ht_cm', 205),
    (3, 'Wt_kg', 90),
    (3, 'Age_yr', 51);

If you are using an RDBMS that has a PIVOT function (SQL Server 2005+/Oracle 11g+) then you can query the data the following way:

select id, Ht_cm, Wt_kg, Age_yr
from
(
  select id, metric, value
  from yourtable
) src
pivot
(
  max(value)
  for metric in (Ht_cm, Wt_kg, Age_yr)
) piv;

See SQL Fiddle with Demo

If you do not have access to a PIVOT function, then you can use an aggregate function with a CASE statement to return the data:

select id,
  max(case when metric ='Ht_cm' then value else null end) Ht_cm,
  max(case when metric ='Wt_kg' then value else null end) Wt_kg,
  max(case when metric ='Age_yr' then value else null end) Age_yr
from yourtable
group by id

See SQL Fiddle with Demo

Both of these queries will return data in the result:

| ID | HT_CM | WT_KG | AGE_YR |
-------------------------------
|  1 |   190 |    82 |     43 |
|  2 |   170 |    60 |     22 |
|  3 |   205 |    90 |     51 |
share|improve this answer
add comment

Funny to see how EAV db model is criticized and even considered as an "anti-pattern" by some.

As far as I'm concerned, the major downsides are :

  • Learning curve is steeper if you get on a project which already started using EAV a while ago. Indeed, the queries are tough as you greatly increase the number of joins (and tables) and so it will ask more time for you to understand . Just have a look at the Magento project and see how the dev external to the project have a tough time working on the DB, yet the documentation is well sustained.
  • Not suited for reporting, if you need to get the number of people who's name started with "M" etc...

However, you should definitely not discard this solution, and here is why :

  • Simon talked about the monster called "changing requirements". I like this expression :). And IMHO this is precisely why EAV may be a good candidate, because this is well suited for "change", as you can add as many attributes as you wish quite easily. Of course it depends on the requirements we're changing. If we're talking about a whole new business, of course you will have to review your dataModel, but EAV offers a lot of flexibility. Just because it asks for more rigour, doesn't mean this is less interesting.
  • It was also said that "You can't use data types." : This is wrong. You may very well have several value tables, one for each dataType. You then have to specify in your attribute table which sort of dataType is your attribute. In fact, a mix of classic relationnal/EAV with class relationship offers a lot of interesting potential in dataBase design.
share|improve this answer
2  
Learning curve is steeper for the first EAV design one encounters. After that, all look alike. –  ypercube Jan 10 '13 at 20:54
    
Temp comment: I don't understand why the "not suited for reporting" claim. EAV seems great for reporting. Select ObjectId from eav.values where propertyId = name and value like 'm%'. Changes to the virtual schema (e.g. adding properties) can be included in any dynamic reporting interfaces (like dropdowns) without having to recompile. –  crokusek May 21 at 6:25
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.