Antipathy for Entity Attribute Value data models

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by David Poole » Antipathy for Entity Attribute Value data...

63 posts, Page 1 of 7

1 2 3 4 5 »»»

Antipathy for Entity Attribute Value data models

Rate Topic

Display Mode

Topic Options

Author

Message

David.Poole

Posted Sunday, January 20, 2013 3:07 PM

SSCrazy

Group: General Forum Members
Last Login: Monday, March 2, 2015 10:17 AM
Points: 2,944, Visits: 1,923

Comments posted to this topic are about the item Antipathy for Entity Attribute Value data models

LinkedIn Profile
Newbie on www.simple-talk.com

Post #1409313

bnordberg

Posted Sunday, January 20, 2013 9:25 PM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:56 PM
Points: 271, Visits: 514

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.

Post #1409344

carlos.iglesias

Posted Monday, January 21, 2013 2:11 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, September 26, 2013 6:55 AM
Points: 43, 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

Post #1409428

keith.fearnley

Posted Monday, January 21, 2013 2:27 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, February 27, 2015 9:46 AM
Points: 259, Visits: 161

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).

Post #1409433

marvin.elder

Posted Monday, January 21, 2013 3:05 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, January 30, 2015 6:48 PM
Points: 7, 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.

Post #1409451

roger.plowman

Posted Monday, January 21, 2013 5:48 AM

Old Hand

Group: General Forum Members
Last Login: Monday, December 2, 2013 6:30 AM
Points: 346, Visits: 691

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. BigGrin

Post #1409514

tymberwyld

Posted Monday, January 21, 2013 7:05 AM

SSC Veteran

Group: General Forum Members
Last Login: Saturday, February 2, 2013 8:21 AM
Points: 283, Visits: 268

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.

Post #1409537

donald.parish

Posted Monday, January 21, 2013 7:49 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, March 3, 2015 7:16 AM
Points: 5, Visits: 28

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.

Post #1409561

Jeff Moden

Posted Monday, January 21, 2013 7:57 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:03 PM
Points: 36,646, Visits: 33,403

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. Tongue

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." Wink

--22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems

Post #1409569

BillyJack

Posted Monday, January 21, 2013 9:39 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 7:40 AM
Points: 37, 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.

Post #1409616

« Prev Topic | Next Topic »

63 posts, Page 1 of 7

1 2 3 4 5 »»»

Permissions