Tell me more ×
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.
up vote 3 down vote favorite
1

I'm not asking if EAV tables are good or bad. I'm wondering if they are considered "normalized", and if not, why? If they aren't normalized, which normal form are they violating and why?

share|improve this question

3 Answers

up vote 5 down vote

An EAV (aka Key-Question-Answer) table is technically in 3NF:

1NF

  • Rows are order-independent - True, the rows can be stored in any order.
  • Columns are order-independent - True, the column ordering has no effect on the state of the contained data, and columns can be referenced independently of any other.
  • No duplicate rows - True, there exists a unique candidate key Entity+Attribute which cannot be duplicated.
  • Every field (every column of every row) contains one and only one value - True, provided that "value" does not contain a list of values or a compound "structure" in serialized format.
  • Nothing is hidden - true, the database contains all information necessary to follow the above four rules and does not hide any of it (it may hide implementation details not necessary to follow the above four rules).

2NF

  • In 1NF - True
  • All columns of the table that aren't the candidate key must have a relationship (direct or indirect) with the entire candidate key - True, the only column that isn't part of the key is related to the full key.

3NF

  • In 2NF - True
  • All columns of the table that aren't the candidate key are directly related to the entire candidate key - True, the only column that isn't part of the key is directly related to both parts of the key.

Thus, EAV adheres to third normal form. It does not meet 4NF, because the structure has multivalued dependencies (in fact it is designed to); adding a new Entity ID to the table requires creating a row for each Attribute, while adding a newly-supported Attribute requires adding a row for each Entity having that new Attribute. A more traditional "flattened" table structure may meet 4NF; adding a new Entity only requires adding one row, and that is the only reason to add a row to the table.

If there is an argument to say that it's not 3NF, it would be that it violates 1NF's "no hidden information" rule in that the structure of the data is not known to the DB. The "Value" field of an EAV table is required to be able to hold anything that may be used as a value; as such it is usually a string field of some type, holding string representations of data such as numbers. That data must be parsed into the proper types, and the knowledge to do so is not contained in an accessible place in the DB (unless incorporated into Value itself, violating 1NF's "one and only one value" rule, or included as an extra column, violating 3NF's "all fields directly related to candidate key" rule), therefore it is "hidden".

share|improve this answer
If all of those attributes are "nullable" then you don't need a new row for each new attribute, just the ones that have values. Also imagine that the entity column and the attribute column are both FK'ed to proper entity and attribute base tables. I dunno, there's something I'm not getting about how this isn't 4NF. // It's kinda just like a parent/child mapping table that has a first class attribute (i.e. the value of the attribute) – Mark Canlas Aug 14 '12 at 4:48
the requirement for 4NF is that there are no "multivalued dependencies". Simply put, there nmust be one reason to add a new row to the table and that reason should result in only one row being added. Because an "entity" is a collection of rows, adding an entity requires adding multiple rows. – KeithS Aug 14 '12 at 5:01
You could argue that the base entity of that table is the attribute. The table is modeling a table so the terminology is a bit meta. Anyway, adding one row is merely filling in the definition of one nullable attribute (in the overall model). – Mark Canlas Aug 14 '12 at 5:25
I disagree. The EAV model represents a set of the cross product of a set of entities and a set of attributes, and thus to add one new entity or attribute you must add the cross product of the entity and all attributes, or the attribute and all entities, which is a violation of 4NF. – KeithS Aug 14 '12 at 21:44
I agree that EAV is in 3NF. However, I disagree with your statement of 3NF in the above. The 3NF rule, in addition to 2NF, is that not key columns must not be dependent on other non key columns. Since there is only one on key column, this rule couldn't be violated. – Walter Mitty Oct 8 '12 at 12:41
show 1 more comment
up vote 1 down vote

It's not Normalized

One of the Rules of Normalization is that : A field should have the same meaning in each row of the table.

Also the Table dosen't represent any specific Entity..

SQL Database Normalization Rules

share|improve this answer
The "entity" would be an "entity-attribute" pair. Similar to how the rows in a link/mapping table are first class. – Mark Canlas Aug 13 '12 at 20:35
2  
That's not a rule of the formal "normal form" definitions. – KeithS Aug 14 '12 at 1:30
I agree with Keith. However, Morons' point is well taken, even if it should not be called "normalization". If column composition is broken, asking whether table composition is normalized is beside the point. The column whose composition is broken is Value. The domain of Value is the entire range of all the values the DBMS can store. In order to store an EAV in SQL, we have to give it a more limited domain than that. So maybe it's "character strings, up to 255 characters long". That's a broken domain. Explanation too long for a comment. – Walter Mitty Oct 8 '12 at 12:48
up vote -1 down vote

Asking whether it's normalized or not is largely beside the point.

Instead, you should ask "is it based on a relational model of the subject matter"? The answer is a resounding NO. This is by intent. When new features of the subject matter are discovered, like new entities or attributes, the EAV can be updated with no changes to data definitions.

That means that the EAV is independent of the information requirements on the subject matter. That in turn means we can build an EAV without doing any data analysis of the subject matter. That's both the great attraction and the terrible pitfall of EAV.

share|improve this answer

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.