SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Antipathy for Entity Attribute Value data models


Antipathy for Entity Attribute Value data models

Author
Message
David.Poole
David.Poole
SSCrazy Eights
SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)

Group: General Forum Members
Points: 8023 Visits: 3291
Comments posted to this topic are about the item Antipathy for Entity Attribute Value data models

LinkedIn Profile

Newbie on www.simple-talk.com
bnordberg
bnordberg
SSC-Addicted
SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)

Group: General Forum Members
Points: 497 Visits: 574
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.



carlos.iglesias
carlos.iglesias
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 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
keith.fearnley
keith.fearnley
Old Hand
Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)

Group: General Forum Members
Points: 307 Visits: 224
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).
marvin.elder
marvin.elder
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 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.
roger.plowman
roger.plowman
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1293 Visits: 1253
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
tymberwyld
tymberwyld
SSChasing Mays
SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)

Group: General Forum Members
Points: 644 Visits: 275
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.



donald.parish
donald.parish
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 44
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90309 Visits: 41146
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 Row-By-Agonizing-Row.
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
BillyJack
BillyJack
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 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.
Go


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

































































































































































SQLServerCentral


Search