August 22, 2012 at 2:52 pm
Michael Valentine Jones (8/22/2012)
This is one of my all time favorite threads about an EVA:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61024
I especially like the query posted by RobWafle at 02/01/2006 12:05:48 that had over 40 left joins.
I still stand by what I posted on that thread:
"..I think that the query you posted is a perfect illustration of the biggest disadvantage of the Entity/Attribute model, that it saves a little work up front in data modeling by allowing “open ended” insertion of new attributes at the cost of having to program the true data structure into each query. Of course, there are other annoying little problems, like enforcing not null, DRI, domain integrity, default values, check constraints, creating useful indexes, transactional integrity, etc. Basically, it takes all the most useful features of a relational data model, and throws them away..."
Yeah that big ole nasty query that has to join to the same table 40 times is EXACTLY what I have seen on a couple of EAV systems in the past. It is nothing but pain to appease somebody thinking they are clever.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 23, 2012 at 1:37 pm
Here is another thread that discusses the topic with additional worthwhile responses to those already made on this thread:
Pros and Cons - Table with loads of uniqe columns VS Row based keys with row data type
Edit: In case you do not make it too far into the other thread, please have a look at this for a great summary of the topic Keeping It Simple > EAV Fail
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 30, 2012 at 2:47 pm
I have to agree with most of what's been said here. As a general rule, EAV is much more costly in development time (especially for enhancements), storage, and performance than a proper relational model - there are exceptions to this general rule (for example if almost everything is extremely sparse you may get a storage saving from EAV, provided you avoid having to encode the datatype with each value) but these are very rare indeed.
Tom
August 30, 2012 at 7:51 pm
mishka-723908 (8/22/2012)
The value data-type in this case will always be the same. I did not think using EAV, but several developers here thought that we should. Thank you everyone for your comments.
To be honest, we don't actually have enough information to recommend or condemn because you haven't told us what the table is designed to hold, what the column names and purposes are, nor why you might want to eventually add a column or two. Some information in that area would certainly help us help you.
I'll join the others in saying that an EAV or NVP will most likely cause you some huge problems but, again, there's just not enough information to tell. It may very well be that an EAV or NVP is exactly what the doctor ordered (I stess again... probably not).
For example, it would be a huge benefit to you to use an NVP to store monthly data rather than storing monthly data in separate columns.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 31, 2012 at 9:09 am
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply