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 |
| freshfruit | productmanufacturer | Chiquita |
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...
- I lose data types (everything is a varchar)
- Data constraints are impossible without complex trigger logic
- 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.
Dave Wentzel CONTENT
data architecture