[ACCEPTED]-Alternatives to Entity-Attribute-Value (EAV)?-entity-attribute-value

Accepted answer
Score: 52

There is a difference between EAV done faithfully 50 or badly; 5NF done by skilled people or 49 by those who are clueless.

Sixth Normal Form 48 is the Irreducible Normal Form (no further 47 Normalisation is possible). It eliminates 46 many of the problems that are common, such 45 as The Null Problem, and provides the ultimate 44 method identifying missing values. It is 43 the academically and technically robust 42 NF. There are no products to support it, and 41 it is not commonly used. To be implemented 40 properly and consistently, it requires a 39 catalogue for metadata to be implemented. Of 38 course, the SQL required to navigate it 37 becomes even more cumbersome (SQL already 36 being cumbersome re joins), but this is 35 easily overcome by automating the production 34 of SQL from the metadata.

EAV is a partial 33 set or a subset of 6NF. The problem is, usually 32 it is done for a purpose (to allow columns 31 to be added without having to make DDL changes), and 30 by people who are not aware of the 6NF, and 29 who do not implement metadata. The point 28 is, 6NF and EAV as principles and concepts 27 offer substantial benefits, and performance 26 increases; but commonly it is not implemented 25 properly, and the benefits are not realised. Quite 24 a few EAV implementations are disasters, not 23 because EAV is bad, but because the implementation 22 is poor.

Eg. Some people think that the 21 SQL required to construct the 3NF rows from 20 the 6NF/EAV database is complex: no, it 19 is cumbersome but not complex. More important, an 18 ordinary SQL VIEW can be provided, so that 17 all users and report tools see only the 16 straight 3NF VIEW, and the 6NF/EAV issues 15 are transparent to them. Last, the SQL 14 required can be automated, so the labour 13 cost that many people endure is quite unnecessary.

So 12 the answer really is, Sixth Normal Form, being 11 the father of EAV, and a purer form, is 10 the replacement for it. The Caveat is, ensure 9 it is done properly. I have one large 6NF 8 db, and it suffers none of the problems 7 people post about, it performs beautifully, the 6 customer is very happy (no further work 5 is a sign of complete functional satisfaction).

I 4 have already posted a very detailed answer 3 to another question which applies to your 2 question as well, which you may be interested 1 in.

Other EAV Question

Score: 9

Regardless of the kind of relational model 34 you use, tracking field name changes requires 33 a lot of meta data which you must keep track 32 of in either transaction logs or audit tables. Unfortunately, querying 31 either of those for state at a particular 30 date is very complicated. If your client 29 only requires state at a particular time 28 date however, meaning the entire state, not 27 just with respect to name changes, you can 26 duplicate the database and roll back the 25 transaction log to the particular time required 24 and run your queries on the new instance. If 23 entities added after the specified date 22 need to show up in the query with the old 21 field names however, you have a very large 20 engineering problem ahead of you. In that 19 case, with the information you provided 18 in your question, I would suggest either 17 negotiating alternatives with the client 16 or getting more information about the use 15 of the reports to find alternative solutions.

You 14 could move to a document based datastore, but 13 that still wouldn't solve the problem in 12 the second case. Sorry this isn't really 11 an answer, but having worked through similar 10 situations, the client likely needs a more 9 realistic reporting solution or a number 8 of other investors willing to front the 7 capital for the engineering.

When this problem 6 came up for us, we kept the db schema constant 5 and implemented an entity mapping factory 4 based on a timestamp. In the end, the client 3 continually changed requirements (on a weekly 2 to monthly basis) as to how aggregate fields 1 were calculated and were never fully satisfied.

Score: 0

To add to the answers from @NickLarsen and 34 @PerformanceDBA

If you need to track historical 33 changes to things like field name, you may 32 want to look into something like Slowly Changing Dimensions. It appears 31 to me like you are using the EAV to model 30 dynamic dimensional models (probably lookup 29 lists).

The simplest (and probably least 28 efficient) way of achieving this would be 27 to include an "as of" date field 26 on EAV tables, and whenever a change occurs, insert 25 a new record (instead of updating an existing 24 record) with the current date. This means 23 that you need to alter your queries to always 22 include or look for an "as of" date, or 21 deafult to "now" if none provided. Your 20 base entity that joins to the EAV objects 19 would then have to query "top 1" from 18 the EAV table where "as of" date 17 is less than or equal to the 'last updated' date 16 of the row, ordered by "as of" descending. Worst 15 case scenario, if you need to track the 14 most recent change to a given row where 13 both the name (stored in the 'attribute' table) and 12 the value have changed, you would chain 11 this logic to the value table using 'last 10 modified' of the row to find the appropriate 9 value for that particular date.

This obviously 8 has the potential to generate LARGE amounts 7 of data if there are a lot of changes. That's 6 why this approach is referred to as "slowly" changing. It's 5 intended for dimensional values that may 4 change, but not very often. To help with 3 query performance, indexes on the "as 2 of" and "last modified" fields 1 should help.

More Related questions