Jeff Moden (10/1/2012)
Perhaps it's just the way I've been using them but I've had no such problems with a properly constructed EAV. Certainly, I've not had to do a 20 or 30 table self join.
Unfortunately the OP has already eluded to at least 10 self joins. One of the big challenges in this type of system is RI. Foreign keys and constraints can be a serious PITA to deal with. I have also seen them have real challenges with datatypes because the value columns will just be a varchar(max) and then you have to cast everything into the correct datatype. I have see other implementations where there was a datatype column to indicate what the datatype of the value column is.
I agree that properly constructed they can be useful and even preferred in some cases. The system described by the OP does not sound like that to me. It sounds like the beginning of a nightmare. That is just my $.02
_______________________________________________________________
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/