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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA