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


EAV's and NVP's are bad. So... What do YOU use as an alternative?


EAV's and NVP's are bad. So... What do YOU use as an alternative?

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86754 Visits: 41103
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.
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
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8625 Visits: 7660
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86754 Visits: 41103
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.
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
Ross McMicken
Ross McMicken
Mr or Mrs. 500
Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)

Group: General Forum Members
Points: 560 Visits: 2237
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.
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23663 Visits: 9730
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
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32555 Visits: 18556
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

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

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

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

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