BillyJack (1/31/2013)
Jeff Moden (1/30/2013)
It is a bit wicked, though. How are you handling similar but different attribute names for the same thing? For example, in address related data, you might have an attribute for "Street Number". Not including the potential for phat phingering, I can think of at least 15 different ways that might be entered as an attribute name. Short of a huge list of possible attribute names and a "simto" search, I've not found a way to overcome that problem.One of the requirements I've always had when an EAV model is to be implemented is that the business owner MUST have someone (a data czar for lack of a better name) responsible for managing new attribute setup (and their valid values) in the system. If not, you will inevitably wind up with multiple attribute names representing the same thing as you indicated, and you will be writing a "merge" proc to consolidate all of the attributes and their values under the proper attribute name. So, with built in business flexibility comes added business responsibility...
Concur and that's where I was going to on this one. In every case except for one internal app, we made it so that only those folks with such responsibility could actually add attributes. Is it idiot proof? Not by a long shot but the customers sure were/are happy about it.
We did go a bit further, though. We made it so that the unique attributes could be listed for any given entity (there weren't THAT many because the rest of the system was properly normalized) and then through a simple system of check boxes, made it so that one of the data czar's you spoke of could combine attributes for that given entity. As you can well imagine, the stored procedure for such an update was incredibly simple and didn't even require any dynamic SQL.
--Jeff Moden
Change is inevitable... Change for the better is not.