Physical Data Modeling AntiPatterns - dynamic data structures

This is part two of my series on [[Physical Data Modeling AntiPatterns Series|Physical Data Modeling AntiPatterns]]. 

I've written extensively on [[EAV Models]] previously.  As a refresher, in an EAV model the table has 3 cols, one for the "entity", one for the "attribute", and one for the "value".  Something like this in a supermarket model:

 

entity
attribute
value
canned good
productname
green beans
canned good
productdescription
Canned vegetable, not at all tasty
canned good
productmanufacturer
DelMonte
freshfruit
productname
bananas
freshfruit
productdescription
A Tasty Fruit
freshfruitproductmanufacturerChiquita

EAV models are touted as being adaptive to evolving design.  No kidding?  When you only have 3 cols you can pretty much model everything.  The problems are many...

  1. I lose data types (everything is a varchar)
  2. Data constraints are impossible without complex trigger logic
  3. the simplest queries are many, MANY lines of SQL

Not all EAVs are evil.  There may be cases where a system needs to evaluate a property at run-time vs compile-time, meaning I may not have all of the data I need to compile correctly.  That's certainly fine, but we are speaking in terms of a limited amount of known data that would be modeled in an EAV, not transactional data.  

Don't fall for the argument of "unlimited flexibility" with an EAV solution.  It just won't work.  Read my older [[EAV Models|blog post]] for complete details.