Entity-Relationship diagram

Question

I have been asked to come up with the logical data model for an application. I've identified all entities, and documented them using an ERD. Now a coworker is suggesting some changes that I don't agree with, and I need reasons for and against my and his approach to be able to come up with the best solution to the problem.

So basically the discussion is on this: I have an entity like this one:

The_Entity

• Attribute 1: string
• Attribute 2: string
• Attribute 3: boolean

And my coworker is suggesting doing

The_Entity

• the_entity_id: PK

Attribute

• the_entity_id: FK
• name: string
• value: string

So basically modelling attributes as entities too.

I think my approach makes more sense semantically, but it is also more static (in implementation, adding an attribute would require to add a column to a table). What is your take on this?

Answer 1

Agreed with the answers already given.

The most obvious case against EAV is when there are business rules involving the attributes that you mention. Say, in your example, "Attribute 1 and Attribute 2 cannot both be longer than ... characters.".

If you implement your example using a single three-column table, then implementing that business rule is as simple and straightforward as defining a single CHECK constraint on that table. The rule will simple and straightforward, also for decoding by others who read your design later on, and at run-time, checking the constraint will go like a swoosh.

If you implement your example using the EAV approach, implementing the same business rule cannot be done declaratively, thus it will require (quite a bit of) extra code, that will be more difficult to decode for anyone reading the design later on, that has more opportunities for getting it wrong, especially in the arena of transaction serialization, and at run-time it can end up being the performance nightmare that makes your system run like a dog with no legs.

You wight also ask your friend how he intends to include attributes in his design, if the value of those attributes is an integer, or a date, or a float, or a boolean, or whatever other type the DBMS has to offer. (He cannot stick to a single table without creating the performance problem of having to un-string any number at read time, and to-string any number at write time. And that's a CPU hog nightmare.)

What your friend has "discovered", is actually the design used by the DBMS itself to document the structure of the user's databases. DBMS catalogs have known that structure for decades (except then for the 'V' part, of course, catalogs are EA, so to speak, EAV without the V), so it's not exactly a "new" idea in any sense.

So yes, when he claims that with his design, it is way easier to adapt the structure of the database, he is right, in a sense. But the price there is to pay for that increased flexibility is in most cases prohibitively high, especially if you also consider the actual added value (typically less than what the EAV proponents suggest) of that "increased flexibility".

A typical case where an EAV approach might be acceptable, is for dealing with questionnaires. If the questionnaire is not really 99.99% stable (can extend a lot over time), and there are not many "rules" that apply to "how it should be filled in" (If the answer to question 7 was "yes", then question 8 must be answered), and the answers to the questionnaire are typically inspected more in "isolation" than in "combination", then most of the disadvantages of EAV do not apply, and can validly be considered.

Answer 2

What your co-worker is advocating is called "Entity-Attribute-Value" or EAV and is broadly considered an anti-pattern. (Google around for "EAV evil" and you will find many examples.) If you want ammunition for why you shouldn't do it the way your co-worker is suggesting, you'll have no trouble finding it.

Having said this, you'll find if you look at my answers on SO and DBA.SE that I'm a rare proponent of EAV in certain specific circumstances, where the bad things about EAV are not applicable. Still, since we don't know whether your specific model meets these exceptional conditions, I'd say it's pretty safe to avoid EAV.