I am currently designing a DB that employs this name value pair approach to store arbitrary sets of attributes to be associated to a variety of entities. I am concerned about performance. but generally deal with with performance problems after they arise.

The product and marketing folks love the concept because it lets them promise anything to customers. They can avoid writing specs and get flexibility at the expense of performance, which is not their problem anyway. :rolleyes:

One alternative seems to be entity tables with an ever-increasing list of nullable fields associated with the entities of interest, the union of all attributes. I'm not very keen on this approach either. Another approach is to have a master entity table with 1-1 subordinate sub-tables for the various entity types, but this also makes for a complicated schema.

I am setting the data type for the attribute values to be nvarchar, for localization. Three-character prefixes on the attribute names indicate the actual type of the value, e.g. strName, datEffective, smnPrice, etc. C# programmers are comfortable with the .Parse(string) functions and can retrieve the strongly typed actual value with these methods.

I hope this all works out, but any words of warning are much appreciated.

Jeff Roughgarden, MCDBA, MCSD