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 12»»

EAV's and NVP's are bad. So... What do YOU use as an alternative? Expand / Collapse
Author
Message
Posted Sunday, May 01, 2011 7:40 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 6:02 PM
Points: 35,972, Visits: 30,265
I've just read a dozen articles/posts where people absolutely condemn the use of EAV's (Entity, Attribute, Value) and NVP's (Name, Value Pair) tables and lambasted anyone who stood up and even eluded to a possible good use.

So... let me ask with the promise that I'm not going to lambaste anyone for their opinion... when you have a front end app that allows users to build their own custom "record" format, how do YOU store that information in a database without using EAV's or NVP's? Or is THAT the exception?

Thanks ahead of time for your thoughts, folks.


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

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1101412
Posted Sunday, May 01, 2011 11:21 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 3:16 PM
Points: 5,986, Visits: 6,931
Jeff Moden (5/1/2011)
I've just read a dozen articles/posts where people absolutely condemn the use of EAV's (Entity, Attribute, Value) and NVP's (Name, Value Pair) tables and lambasted anyone who stood up and even eluded to a possible good use.

So... let me ask with the promise that I'm not going to lambaste anyone for their opinion... when you have a front end app that allows users to build their own custom "record" format, how do YOU store that information in a database without using EAV's or NVP's? Or is THAT the exception?

Thanks ahead of time for your thoughts, folks.


I'd have to say that IS the exception. You've basically left the realm of design at that point and left it in the hands of the end users. When your users are going to design their own database... well... you do what you can.

In general I would avoid them, but there's another exception to me, and that's the proper use of a EAV table that uses multiple columns dependent on the type of value put in, and then only during the first year of a constructed DB. The idea here is that you're not quite sure what the users are going to need at first, so you design incredibly generically. After a year or two, you revisit the design wholesale, once you've learned what the end users are really doing, rather then what they thought they wanted.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1101437
Posted Sunday, May 01, 2011 11:38 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 6:02 PM
Points: 35,972, Visits: 30,265
Thanks for the feedback, Craig. I'm going to hold up on telling folks whether or not I use them because I don't want to bias anyone. I might even have a surprise for folks that think they're not using them.

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

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1101443
Posted Tuesday, May 03, 2011 12:07 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 17, 2014 10:33 AM
Points: 357, Visits: 1,928
I've seen them used very successfully in applications where the end users have choices on configuration. An accounting system I used to support allowed users to create ledgers with 2 to 10 coding elements. Each element type had a letter as a prefix, and the master data was stored in a single table for all elements. Using a single table made coding somewhat easier, and gave the users maximum flexibility.

I've used EAV tables to store configuration data in cases where it made sense. I don't like creating tables to hold a single record, so "misc" table has been a part of most of my designs. I include a comment field to document the values inserted.

Like any other concept, there are appropriate places for EAV/NVP constructs, and it's up to us to make sure they are used correctly.
Post #1102594
Posted Tuesday, May 03, 2011 12:24 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
I've used them. Those and XML columns both work for "user-defined data structures". Which one I use depends on degree of flexibility needed and how it's going to be used.

EAV is often easier, but XML has the advantage of the data all being in one place. If you delete a row, but not a set of rows, out of an EAV table, you've potentially corrupted the data. Not so with an XML column. XML also works nicely with .NET front ends, since the .NET languages have all kinds of XML shredding tools. Other application development scenarios are probably just as good at XML, but I don't know that one way or the other.

On the other hand, EAV doesn't require jumping through any hoops to contain markup characters. And, if you include a column for each basic data type, and a column that indicates which type a particular row has in it, you can use that, plus a simple check constraint, to enforce pretty specific data integrity rules. Re-constituting the data is a performance killer every time I've seen these used, but it can be mostly overcome by some relatively simple tricks. Will never be fast, and doesn't index well, but it can be less-slow. It can also take a lot less disk space than XML, in some cases, and thus also less I/O work, less RAM, et al.

I generally prefer XML columns for this kind of thing these days, but EAV has its place and its uses. It'd be more useful if T-SQL's Pivot and Unpivot operators weren't essentially junk.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1102611
Posted Tuesday, May 03, 2011 12:34 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:46 PM
Points: 20,466, Visits: 14,100
Until recently I was biased against them having had extremely bad experiences with them. I have seen an implementation where it seems to work rather well. Thus, I would say it really depends on implementation and knowledge level of people implementing it.


If ever you are curious, I have it on good account that match.com uses an EAV.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1102623
Posted Saturday, May 07, 2011 11:58 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 6:02 PM
Points: 35,972, Visits: 30,265
Thanks for the replies and my apologies... I lost track of this thread for a while because of all the activity on an article discussion.

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

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1105031
Posted Saturday, May 07, 2011 12:02 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 6:02 PM
Points: 35,972, Visits: 30,265
Ross McMicken (5/3/2011)
I've seen them used very successfully in applications where the end users have choices on configuration.
...
I've used EAV tables to store configuration data in cases where it made sense.
...
Like any other concept, there are appropriate places for EAV/NVP constructs, and it's up to us to make sure they are used correctly.


My thoughts, exactly. It all boils down to "It Depends". It's a bit like saying that all Cursors and While loops are bad. While they should be generally avoided, there are times where it's not only acceptable but they actually are the correct solution.


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

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1105032
Posted Saturday, May 07, 2011 12:06 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 6:02 PM
Points: 35,972, Visits: 30,265
GSquared (5/3/2011)
I've used them. Those and XML columns both work for "user-defined data structures". Which one I use depends on degree of flexibility needed and how it's going to be used.

EAV is often easier, but XML has the advantage of the data all being in one place. If you delete a row, but not a set of rows, out of an EAV table, you've potentially corrupted the data. Not so with an XML column. XML also works nicely with .NET front ends, since the .NET languages have all kinds of XML shredding tools. Other application development scenarios are probably just as good at XML, but I don't know that one way or the other.

On the other hand, EAV doesn't require jumping through any hoops to contain markup characters. And, if you include a column for each basic data type, and a column that indicates which type a particular row has in it, you can use that, plus a simple check constraint, to enforce pretty specific data integrity rules. Re-constituting the data is a performance killer every time I've seen these used, but it can be mostly overcome by some relatively simple tricks. Will never be fast, and doesn't index well, but it can be less-slow. It can also take a lot less disk space than XML, in some cases, and thus also less I/O work, less RAM, et al.

I generally prefer XML columns for this kind of thing these days, but EAV has its place and its uses. It'd be more useful if T-SQL's Pivot and Unpivot operators weren't essentially junk.


Well said. Thanks for the feedback, Gus.

I've not tried the XML route, yet. I should at least look into its perforance and handling requirements. I guess the biggest deterent from me trying it, so far, is because I've had such good luck with using Cross Tabs instead of using Pivot. And, I agree... what were they thinking when they introduced PIVOT/UNPIVOT to T-SQL. The Pivot function in Access absolutely blows the T-SQL Pivot out of the water. You'd have thought they would use that as a model.


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

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1105034
Posted Saturday, May 07, 2011 12:12 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 6:02 PM
Points: 35,972, Visits: 30,265
SQLRNNR (5/3/2011)
Until recently I was biased against them having had extremely bad experiences with them. I have seen an implementation where it seems to work rather well. Thus, I would say it really depends on implementation and knowledge level of people implementing it.


Exactly. "It Depends". I am curious though... what were the "extremely bad experiences" you had with them?

The bad experiences that I had with them was someone decided that every table in our system (for a previous company) need to have an NVP table as an audit table. Although it made the data collection and audit triggers stupid-simple to write, they were a nightmare when it came to determining what a row looked like at a given point in time. It wasn't such a bad idea for very wide tables because it did save on storage, but those saving were pretty much negated by the extra storage required for the narrower tables which made up most of the system.

If ever you are curious, I have it on good account that match.com uses an EAV.


Ok, now you DID get my curiosity up. What are they using it for? Their main "people" table or ????


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

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1105039
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse