We are starting a new project where we need to store product and many product attributes in a database. The technology stack is MS SQL 2008 and Entity Framework 4.0 / LINQ for data access.

The products (and Products Table) are pretty straightforward (a SKU, manufacturer, price, etc..). However there are also many attributes to store with each product (think industrial widgets). These may range from color to certification(s) to pipe size. Every product may have different attributes, and some may have multiples of the same attribute (Ex: Certifications).

The current proposal is that we will basically have a name/value pair table with a FK back to the product ID in each row.

An example of the attributes Table may look like this:

ProdID     AttributeName     AttributeValue
123        Color             Blue
123        FittingSize       1.25
123        Certification     AS1111
123        Certification     EE2212
123        Certification     FM.3
456        Pipe              11
678        Color             Red
999        Certification     AE1111
...

Note: Attribute name would likely come from a lookup table or enum.

So the main question here is: Is this the best pattern for doing something like this? How will the performance be? Queries will be based on a JOIN of the product and attributes table, and generally need many WHEREs to filter on specific attributes - the most common search will be to find a product based on a set of known/desired attributes.

If anyone has any suggestions or a better pattern for this type of data, please let me know.

Thanks! -Ed