Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Authors
About us
Contact us
Newsletters
Write for us
Daily SQL Articles by email:
Sign up
Back
SQLServerCentral
Register
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss Content Posted by David Poole
»
Antipathy for Entity Attribute Value data...
Antipathy for Entity Attribute Value data models
Post reply
Like
678
Add to Briefcase
1
2
3
4
5
7
Next
Antipathy for Entity Attribute Value data models
View
Options
Author
Message
Dave Poole
Dave Poole
Posted 5 years ago
#1409313
SSC-Forever
Group: General Forum Members
Points: 42052
Visits: 3851
Comments posted to this topic are about the item
Antipathy for Entity Attribute Value data models
LinkedIn Profile
www.simple-talk.com
52
Quote
bnordberg
bnordberg
Posted 5 years ago
#1409344
Hall of Fame
Group: General Forum Members
Points: 3227
Visits: 576
I could not agree more.
However isn't this the same concept being used for "Big Data" .
Every presentation I see about big data (aside from hadoop), seems to look just like EAV models that we so hate. I fear we are returning to those days.
49
Quote
carlos.iglesias
carlos.iglesias
Posted 5 years ago
#1409428
SSC-Enthusiastic
Group: General Forum Members
Points: 115
Visits: 91
Hi
A good example of EAV model are e-commerce apps like Magento. They are using an EAV model and duplicate writes to maintain some denormalized tables, they are using EAV for writes and relational model for reads. You can also find EAV models in clinic models due the huge range of attributes to analyze. In my opinion, as you said in your article, business requirement standardization is the main reason for using it.
Regards
Carlos
34
Quote
keith.fearnley
keith.fearnley
Posted 5 years ago
#1409433
SSC-Addicted
Group: General Forum Members
Points: 467
Visits: 225
Not EAV, but I'd be interested in people's views on Anchor Modelling (www.anchormodeling.com) which basically normalises out every column except the primary key, but provides decent generated code for accessing as it still in 3NF.
For an application we have that needs every change to any column to be recorded, it looks attractive from a storage-space perspective and maintainability for schema changes (also a requirement in this case), but I'm a bit concerned about performance for a live transactional system (rather than a data warehouse).
63
Quote
marvin.elder
marvin.elder
Posted 5 years ago
#1409451
SSC-Enthusiastic
Group: General Forum Members
Points: 145
Visits: 36
I think you miss the most critical points of EAV data models:
a. "models" in general, not just data models, are abstractions that help humans understand a process or a system. A conceptual or logical data model describes the underlying business as a relational database, derived by a data architect working with the Subject Matter Expert to rapidly build the correct database structures that "model" the business or process.
b. The physical databases implemented from a logical data model will automatically enforce database integrity: both entity integrity and relational (foreign key) integrity.
c. The ANSI - SPARC three-schema architecture, supported by high quality data modeling tools like Embarcadero's ER Strudio and ERWin, does the coupling of conceptual, logical and physical models and keeps them in sync. Tremendous value.
d. The physical database is, or may be, just one of several possible implementations of a logical data model.
47
Quote
roger.plowman
roger.plowman
Posted 5 years ago
#1409514
SSCertifiable
Group: General Forum Members
Points: 5609
Visits: 1716
In many ways SQL Server itself is an EAV system. Tables, columns, constraints, they're all records in tables.
However, having said that, why reinvent the wheel? SQL Server actually abstracts EAV so we don't have to worry about all the (myriad) implementation details for a complex EAV system. I can live with that!
The one use case I have no trouble allowing in a model is that of "minor" tables, such as a 3-4 item list that would otherwise be hardcoded into the application and merely stored with a check constraint in a byte field.
One example would be a status field that basically said: Active, Inactive, Parted Out, or Destroyed. If you have a dozen such small status fields an EAV approach of key, value, and (perhaps) user explanation fields isn't burdensome.
Beyond that--well, too much work. :-D
48
Quote
tymberwyld
tymberwyld
Posted 5 years ago
#1409537
SSCarpal Tunnel
Group: General Forum Members
Points: 4606
Visits: 276
Wow, I never knew someone would use EAV so extensively! Usually the only time we employ such a scenario is when we have a need for "Customer-Defined Fields" (CDFs as opposed to UDFs). These are not fields we would know about ahead of time, nor could we predict what a Customer wants. Obviously we attempt to guide them to use any existing fields "correctly" because (as we all know) every Customer uses different terminology for the same things. Lastly, once we notice multiple Customers using the same "Custom-Defined Fields", we "promote" those to real fields and standardize everyone at the same time.
The idea (I believe) of EAVs is to allow flexibility in the short-term, while at the same time learning about what the audience of the system needs, and then promoting those needs to real models as trends are noticed.
45
Quote
donald.parish
donald.parish
Posted 5 years ago
#1409561
Old Hand
Group: General Forum Members
Points: 320
Visits: 57
Datomic (http://www.datomic.com/) by Rich Hickey addresses many of your criticisims. It add Time to EAV (EAVT), so the state of the database is available as a value at any point in time. It uses Datalog query language to handle queries. In addition it does have a schema that includes data types, whether one entity is a component of another. Certainly worth taking a look at.
54
Quote
Jeff Moden
Jeff Moden
Posted 5 years ago
#1409569
SSC Guru
Group: General Forum Members
Points: 619983
Visits: 45201
I agree with tymberwyld above. I normally limit EAVs to those things that cannot be known at design time.
I also think that the ability to datatype such a thing has been grossly underestimated. SQL_Variant does a dandy job there although I'll admit it provides no datatype enforcement by itself. While I also agree that contraints and FK's are all but useless, that's the whole point behind EAVs... they allow the end user to create "columns" on the fly. A trigger that "learns" could be applied to the table for a form of DRI and datatype enforcement. Yep... users can make a mess of such a table but such messes are sometimes best left to the customer.
Shifting gears, there is actually quite a bit of data that is better served in the form of an EAV. A simple example would be monthly plans vs actuals where you store the month or day or other temporal data in a column, the entity, the attribute (plan, actual, etc), and, of course, the value. Some would even call this particular type of EAV a "normalized fact table". Once you learn how to quickly aggregate and pivot the data, they're extremely useful and easy to manipulate. Of course, the cool part about this form of an EAV is that you can actually have DRI by entity and attribute and date/time.
Another very common use for EAVs is for auditing very wide tables. Yep... it can be a pain to figure out what a given row looked like at a given point of time the first time but, once you've done it, you won't think it so bad. Well, unless you're an ISO/ANSI purist. :-P
Also, in real life, I try to avoid storing XML anywhere in the database. It just seems as stupid as storing CSVs and certainly has more bloat both in parsing and storing. I'd much rather see the XML be shredded and propagated to real, normalized tables.
As far as polyglots go, I try to NOT go there. One app for the front end and one app for the backend is usually just right. Anything more is like a very complicated trip to work where you drive a car to a train station, walk from the train station to the curb where you pick up a bus that's not going directly to where you want it to go, get dropped off near work, cross several streets, take an elevator, follow a road map to your cube, and spend the next half hour reading emails that shouldn't have been sent to you to begin with before you can do any of your own work.
--Jeff Moden
RBAR
is pronounced ree-bar and is a Modenism for
R
ow-
B
y-
A
gonizing-
R
ow.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
69
Quote
BillyJack
BillyJack
Posted 5 years ago
#1409616
SSC Veteran
Group: General Forum Members
Points: 293
Visits: 65
Having used EAV designs for well over a decade, I still haven't seen a good alternative for data driven systems that provides the flexibility of an EAV in dealing with adding new attributes and incorporating them into an organization's business rules. Yes, data typing of the attribute value is an issue, but so is the ongoing maintenance of adding columns to tables and updating SQL to incorporate those new columns and managing application versioning/rollout. I would love to have an alternative for EAV designs where new attributes are regularly added to an entity's definition.
51
Quote
Go
Post reply
Like
678
Add to Briefcase
1
2
3
4
5
7
Next
Post quoted reply
Permissions
You
can't
post new topics.
You
can't
post topic replies.
You
can't
post new polls.
You
can't
post replies to polls.
You
can't
edit your own topics.
You
can't
delete your own topics.
You
can't
edit other topics.
You
can't
delete other topics.
You
can't
edit your own posts.
You
can't
edit other posts.
You
can't
delete your own posts.
You
can't
delete other posts.
You
can't
post events.
You
can't
edit your own events.
You
can't
edit other events.
You
can't
delete your own events.
You
can't
delete other events.
You
can't
send private messages.
You
can't
send emails.
You
can
read topics.
You
can't
vote in polls.
You
can't
upload attachments.
You
can
download attachments.
You
can't
post HTML code.
You
can't
edit HTML code.
You
can't
post IFCode.
You
can't
post JavaScript.
You
can
post emoticons.
You
can't
post or upload images.
Select a forum
SQL Server 2017
SQL Server 2017 - Administration
SQL Server 2017 - Development
SQL Server 2016
SQL Server 2016 - Administration
SQL Server 2016 - Development and T-SQL
SQL Server 2014
Administration - SQL Server 2014
Development - SQL Server 2014
SQL Server 2012
SQL 2012 - General
SQL Server 2012 - T-SQL
SQL Server vNext
SQL Server 15 - Administration
SQL Server 15 - Development
SQL Server 2008
SQL Server 2008 - General
T-SQL (SS2K8)
June 2007 CTP
Working with Oracle
July CTP
SQL Server Newbies
Security (SS2K8)
SQL Server 2008 High Availability
SQL Server 2008 Administration
Data Corruption (SS2K8 / SS2K8 R2)
SQL Server 2008 Performance Tuning
Cloud Computing
SQL Azure - Development
SQL Azure - Administration
Amazon AWS and other cloud vendors
General Cloud Computing Questions
CosmosDB
Azure Data Lake
Azure Machine Learning
Reporting Services
Reporting Services
Reporting Services 2005 Administration
Reporting Services 2005 Development
Reporting Services 2008/R2 Administration
Reporting Services 2008 Development
SSRS 2012
SSRS 2014
SSRS 2016
Programming
Connecting
General
SMO/RMO/DMO
XML
Service Broker
Powershell
Testing
TFS/Data Dude/DBPro
SSDT
Continuous Integration, Deployment, and Delivery
R Services and R Language
Data Warehousing
Integration Services
Strategies and Ideas
Analysis Services
Data Transformation Services (DTS)
Performance Point
Data Mining
PowerPivot
R language
Machine Learning
Database Design
Disaster Recovery
Design Ideas and Questions
Relational Theory
Hardware
Virtualization
Security and Auditing
SQLServerCentral.com
Anything that is NOT about SQL!
Contests!
Editorials
SQLServerCentral.com Announcements
SQLServerCentral.com Website Issues
Suggestions
Tag Issues with Content
Podcast Feedback
SQLServerCentral.com Test Forum
Articles Requested
SQL Server 2005
Administering
Backups
Business Intelligence
CLR Integration and Programming.
Data Corruption
Development
Working with Oracle
SQL Server 2005 Compact Edition
SQL Server 2005 General Discussion
SQL Server 2005 Security
SQL Server 2005 Strategies
SS2K5 Replication
SQL Server Express
SQL Server 2005 Performance Tuning
SQL Server 2005 Integration Services
T-SQL (SS2K5)
SQL Server Newbies
SQL Server 7,2000
Administration
Backups
Data Corruption
General
Globalization
In The Enterprise
Working with Oracle
Security
Strategies
SQL Server Newbies
Service Packs
SQL Server CE
Performance Tuning
Replication
Sarbanes-Oxley
T-SQL
SQL Server Agent
SQL Server and other platforms
MySQL
Oracle
PostgreSQL
DB2
SQL Server and Sharepoint
Older Versions of SQL (v6.5, v6.0, v4.2)
Older Versions of SQL (v6.5, v6.0, v4.2)
Career
Certification
Employers and Employees
Events
Job Postings
Resumes and Job Hunters
Presentations and Speaking
Retired Members
Testing Center
Question of the Day (QOD)
SQL Server Security Skills
Microsoft Access
Microsoft Access
Products and Books
Third Party Products
CA
SQLCentric
Extreme Technologies.
Innovartis
Embarcadero
SQL Sentry
Golden Gate Software
Idera
Lumigent
Red Gate Software
Quest Software
ApexSQL
Sonasoft
Discussions about Books
Discuss Programming Books
Discuss XML Books
Discuss Data Warehousing Books
Discuss T-SQL Books
Discuss DTS Books
Discuss SQL Server 2000 Books
Discuss SQL Server 7.0 Books
Notification Services
Administration
Article Discussions
Future Versions
SQL 12
Narrow your search by forum
Explore
Home
Latest
Popular
Calendar
Members
Who's on
Moderators
Forums
SQL Server 2017
SQL Server 2017 - Administration
SQL Server 2017 - Development
SQL Server 2016
SQL Server 2016 - Administration
SQL Server 2016 - Development and T-SQL
SQL Server 2014
Administration - SQL Server 2014
Development - SQL Server 2014
SQL Server 2012
SQL 2012 - General
SQL Server 2012 - T-SQL
SQL Server vNext
SQL Server 15 - Administration
SQL Server 15 - Development
SQL Server 2008
SQL Server 2008 - General
T-SQL (SS2K8)
June 2007 CTP
Working with Oracle
July CTP
SQL Server Newbies
Security (SS2K8)
SQL Server 2008 High Availability
SQL Server 2008 Administration
Data Corruption (SS2K8 / SS2K8 R2)
SQL Server 2008 Performance Tuning
Cloud Computing
SQL Azure - Development
SQL Azure - Administration
Amazon AWS and other cloud vendors
General Cloud Computing Questions
CosmosDB
Azure Data Lake
Azure Machine Learning
Reporting Services
Reporting Services
Reporting Services 2005 Administration
Reporting Services 2005 Development
Reporting Services 2008/R2 Administration
Reporting Services 2008 Development
SSRS 2012
SSRS 2014
SSRS 2016
Programming
Connecting
General
SMO/RMO/DMO
XML
Service Broker
Powershell
Testing
TFS/Data Dude/DBPro
SSDT
Continuous Integration, Deployment, and Delivery
R Services and R Language
Data Warehousing
Integration Services
Strategies and Ideas
Analysis Services
Data Transformation Services (DTS)
Performance Point
Data Mining
PowerPivot
R language
Machine Learning
Database Design
Disaster Recovery
Design Ideas and Questions
Relational Theory
Hardware
Virtualization
Security and Auditing
SQLServerCentral.com
Anything that is NOT about SQL!
Contests!
Editorials
SQLServerCentral.com Announcements
SQLServerCentral.com Website Issues
Suggestions
Tag Issues with Content
Podcast Feedback
SQLServerCentral.com Test Forum
Articles Requested
SQL Server 2005
Administering
Backups
Business Intelligence
CLR Integration and Programming.
Data Corruption
Development
Working with Oracle
SQL Server 2005 Compact Edition
SQL Server 2005 General Discussion
SQL Server 2005 Security
SQL Server 2005 Strategies
SS2K5 Replication
SQL Server Express
SQL Server 2005 Performance Tuning
SQL Server 2005 Integration Services
T-SQL (SS2K5)
SQL Server Newbies
SQL Server 7,2000
Administration
Backups
Data Corruption
General
Globalization
In The Enterprise
Working with Oracle
Security
Strategies
SQL Server Newbies
Service Packs
SQL Server CE
Performance Tuning
Replication
Sarbanes-Oxley
T-SQL
SQL Server Agent
SQL Server and other platforms
MySQL
Oracle
PostgreSQL
DB2
SQL Server and Sharepoint
Older Versions of SQL (v6.5, v6.0, v4.2)
Older Versions of SQL (v6.5, v6.0, v4.2)
Career
Certification
Employers and Employees
Events
Job Postings
Resumes and Job Hunters
Presentations and Speaking
Retired Members
Testing Center
Question of the Day (QOD)
SQL Server Security Skills
Microsoft Access
Microsoft Access
Products and Books
Third Party Products
CA
SQLCentric
Extreme Technologies.
Innovartis
Embarcadero
SQL Sentry
Golden Gate Software
Idera
Lumigent
Red Gate Software
Quest Software
ApexSQL
Sonasoft
Discussions about Books
Discuss Programming Books
Discuss XML Books
Discuss Data Warehousing Books
Discuss T-SQL Books
Discuss DTS Books
Discuss SQL Server 2000 Books
Discuss SQL Server 7.0 Books
Notification Services
Administration
Article Discussions
Future Versions
SQL 12
SQLServerCentral
Register
Search
Narrow your search by forum
Unthreaded, ascending
Unthreaded, descending
Subscribe to topic
Print topic
RSS feed
Go to topics forum
Jump to page
Jump to page
Copyright © 2002-2018 Redgate. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.