I don't know that I would ever use a global table to look up so many attributes for a wide array of objects, but I have used a similar system as an attribute table for other a set of objects.

I think the system works best and is most useful when you can't predict what attributes a given set of entities are going to have during the main design phase of a project. In one case, however, I wound up using the system and then the list of attributes didn't change... I quite frankly wish that I could go back and replace it with a less flexible, more straight-forward design now. I don't think that invalidates the design concept, but I think it does point towards the argument some have been making that it is best used when you're dealing with sparse attributes. It beats creating a bunch of different tables with a 1-to-1 relationship with your entity tables, that all wind up with 5 rows that you have to join every time you run a query. Also keeps you from having to add a bunch of extraneous fields to your tables that may not ever really get used.

One gotcha I have found with my implementation is that I can't really use views to single out the attributes by entity in large joins without taking a significant performance hit. I haven't tried something like indexed views to make it faster, but instead I take the code from the views and integrate it into the query I'm writing. I'll use the views until I find a performance issue then go do the work to rewrite as needed. Part of the problem, however, is probably that I don't have just a simple identifier in the attribute table to say "this is an item color", or "this is an item cost", but instead have other tables that have to join to the attribute table to determine what kind of attribute it is. I have this requirement partly because I expected the list of attributes that were valid for an entity to change over time, but for a time period, they would all be the same; this was a way to enforce which attributes were valid when and allow them to be categorized since the application presents them to the user based on what type of attribute it is... I now think it could be done better since this has caused a fair amount of coding effort to support. On the other hand, I directly attribute the ease of a few changes to the system on how flexible it is.

Either way, I thank the author for sharing the article. IMHO, anything that causes design debate in the DBA community is a Good Thing.

Matthew Galbraith