Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Name value pair (EAV) model Expand / Collapse
Author
Message
Posted Monday, March 24, 2008 11:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 18, 2014 2:06 PM
Points: 37, Visits: 176
Yes, the C# code has to use the culture-aware parse method, and is aware of the requested culture.

Int32..::.Parse Method (String, NumberStyles, IFormatProvider)



Post #473607
Posted Monday, March 24, 2008 12:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 4, 2014 8:26 AM
Points: 109, Visits: 490
I would definitely push this way down in a wrapper, so the app was a shielded as possible from the raw EAV. Less room for developers to make mistakes in their reading and writing.
Post #473627
Posted Monday, March 24, 2008 12:54 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:00 PM
Points: 1,384, Visits: 402
There is a good deal of information on the web explaining why the EV model does not scale and does not perform well under load.

No amount of indexing will fix the basic flaw in the EV design.

Some people are fooled by the relative decent performance for single row and related row set retrieval but it is very easy to find fairly simple queries that do not run well with this design. Once you get 250 million rows of EV values like an application we purchased has you can pretty much schedule vacation while you wait for results.

-- Mark D Powell --

Post #473655
Posted Monday, March 24, 2008 3:46 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 18, 2014 2:06 PM
Points: 37, Visits: 176
Mark,

Could you give some links to discussion on the (lack of) scalability of the EAV model?

Also, what do you recommend in its place, master entity tables with 1-1 subtables for entity subclasses?

Thanks,

Jeff



Post #473761
Posted Monday, March 24, 2008 3:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 7:15 AM
Points: 25, Visits: 549
JRoughgarden (3/24/2008)
Mark,

Could you give some links to discussion on the (lack of) scalability of the EAV model?

Also, what do you recommend in its place, master entity tables with 1-1 subtables for entity subclasses?

Thanks,

Jeff


I will try to address the implementation specific of EAV in my part II article and hopefully it will answer all your concerns.

regards

Teshome
Post #473763
Posted Monday, March 24, 2008 4:40 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 14, 2014 10:33 PM
Points: 85, Visits: 260
I can see the flexibility demands that a rapidly changing data model requires. But I will argue strongly against abstracting the data model away from the database layer.

All these conceptual thought games around changing data models, software changes and end user configurablity just pass the decisions in a changing application to a person that is abstracted from a relational view of the performance impact of the data model change they are contemplating.

Unmanaged incremental performance risk with potentially cheaper development blind to the risks of the modelling decisions they are making. If only I could force the decision makers to do the ongoing support for these applications.
Post #473775
Posted Monday, March 24, 2008 7:18 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, February 2, 2013 8:21 AM
Points: 283, Visits: 268
I love how everytime I read about EAVs there's always some quote about performance blah, blah. EAVs have their place and they are good at what they do. Hmm, let's take an example, start using Extended Properties or querying the SysProperties or using the ::fn_listExtendedProperty functions. Oh my god! Microsoft used an EAV! Did they use it throughout the whole design of Sql Server, No! EAVs serve their purpose as long as you are not relying on this data for business logic, I think it's fine.

When you need to display different descriptions for Products based on culture, you're doing an EAV even though you've defined the columns very specifically to Products, and in most minds it's a 1-to-Many design, it's underlying concept is still EAV.

I tend to use EAV data where it's applicable. For example, logging Event data. I don't use that data for business logic, maybe some reports, but it's rarely used. It does, however, need to be flexible or else you end up creating 100's of tables to log different types of events. Just looks at SQL Notification Services (hmm, I wonder why they're no longer going to support it?). It's too difficult and a maintenance nightmare.

Please keep reading! There will come a point after storing certain attributes that they will either become their own tables or additional columns of a root table once you realize the value or having that as an additional column or you find a way to name the column so that it's meaning is universal to anyone using it (for example, changing SSN to NationalID so that it works in any country or State to Region and County to District). EAVs make great sandboxes for data that can eventually become part of the standard model.

I don't know, my EAV tables have always had 2 indexes and have always been fine. Not sure how others are implementing this. Obviously partitioning will help.



Post #473815
Posted Monday, March 24, 2008 7:26 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 2013 12:15 PM
Points: 1,443, Visits: 711
Cade,
True - but not in this case. They actually used a TEXT field for the 'value' part......

Mark
Post #473817
Posted Monday, March 24, 2008 8:17 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 35,366, Visits: 31,905
tymberwyld (3/24/2008)
I love how everytime I read about EAVs there's always some quote about performance blah, blah. EAVs have their place and they are good at what they do. Hmm, let's take an example, start using Extended Properties or querying the SysProperties or using the ::fn_listExtendedProperty functions. Oh my god! Microsoft used an EAV! Did they use it throughout the whole design of Sql Server, No! EAVs serve their purpose as long as you are not relying on this data for business logic, I think it's fine.

When you need to display different descriptions for Products based on culture, you're doing an EAV even though you've defined the columns very specifically to Products, and in most minds it's a 1-to-Many design, it's underlying concept is still EAV.

I tend to use EAV data where it's applicable. For example, logging Event data. I don't use that data for business logic, maybe some reports, but it's rarely used. It does, however, need to be flexible or else you end up creating 100's of tables to log different types of events. Just looks at SQL Notification Services (hmm, I wonder why they're no longer going to support it?). It's too difficult and a maintenance nightmare.

Please keep reading! There will come a point after storing certain attributes that they will either become their own tables or additional columns of a root table once you realize the value or having that as an additional column or you find a way to name the column so that it's meaning is universal to anyone using it (for example, changing SSN to NationalID so that it works in any country or State to Region and County to District). EAVs make great sandboxes for data that can eventually become part of the standard model.

I don't know, my EAV tables have always had 2 indexes and have always been fine. Not sure how others are implementing this. Obviously partitioning will help.


I agree... event logging and audit logs are a great example of EAV's. I also use them with great success in cross-tab reporting... first step is frequently to put the data into some form of EAV as a derived table. If I really want things to fly, the EAV will be a temp table with the correct index. I've made several monthly reports that report 9 different aggregated attributes by hour of day by day of month for all days of the month. Millions of rows are the source of the data (IVR application, in this case) and they are quickly aggregated into an EAV and then cross-tabbed with ease. And, unlike the Pivot function, can return more than one measure (if you want to call it that.).

We've also very successfully used them in "Customer Added 'Fields'"... not something you should go nuts with, but effective when the customer uses them properly.

As has been said, they have their place and that's not "everywhere".


--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." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #473826
Posted Tuesday, March 25, 2008 8:01 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:00 PM
Points: 1,384, Visits: 402
Naturally I cannot remember where I read the articles I would like to reference but I did find the following:

Academic sytle paper
Data Extraction and Ad Hoc Query of an Entity— Attribute— Value Database
http://www.pubmedcentral.nih.gov/articlerender.fcgi?artid=61332

See section 11 Disccussion of drawacks
http://en.wikipedia.org/wiki/Entity-Attribute-Value_model

See Downsides
http://www.answers.com/topic/entity-attribute-value-model?cat=technology

Article in favor of generic design with many mostly negative comments posted
http://www.sswug.org/see/26210

Posted 4 table model of everything with a few mostly negative and not overly specific disagreements
http://discuss.joelonsoftware.com/default.asp?design.4.331499.15


The basic EV design works great when you have only one set of attributes to values, it works pretty good when you have a few sets of attributes to value pairs by a limited number of groups. By add another layer or two of grouping and increase the volume then the model starts to break down as soon as your query complexity increases.

If you are just considering using the model for a simple parameter table then you will probably not have to deal with most of the issues but if you needs are more complex you can quickly get into trouble.

-- Mark D Powell --

Post #474079
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse