Entity attribute value (EAV) and XML column for new product attributes

Question

I have an existing, mature schema to which we need to add some new product attributes. For example, we have Products.Flavor, and now you need to add new attributes, such as Weight, Fragrance, etc. Instead of continuing to expand the Products table, I am considering a couple of other options. Firstly, this is a new attribute table that will effectively be a package of properties for attribute attributes and a ProductAttributes table to store mappings (and values) for specific product attributes. This is the Entity-Attribute-Value (EAV) pattern, as I understood it. Another option is to add a new column to the Products table, called attributes, which is of type XML. Here we can optionally add attributes to any product instance without adding new tables.

What are the advantages / disadvantages of each approach? I am using SQL Server 2008 and ASP.NET 4.0.

Answer 1

This (imho) is one of the classic problems with database design. Call it the “creep attribute”, perhaps as soon as you start another attribute or property will always be added. The key decision is that you store the data in the database using the basic tools provided by the database (tables and columns) to structure and format the data, or you store the data in some other way (XML and name / value pairs being the most common substitutes). Simply put, if you store data in a form different from that supported by the DBMS system, then you lose the ability of the DBMS system to manage, maintain and work with this data. This is not a big problem if you only need to save it as “blob data” (reset it all, pump it all out), but as soon as you start searching, sorting or filtering on this data, it can get very ugly very quickly.

With that said, I have strong opinions about name / value pairs and XML, but alas, none of them are positive. If you need to store your data this way, and yes, it can be the right business / design decision, then I would recommend looking long and hard about how the data that you need to store in the database will be used and access to the future . Weight the advantages and disadvantages of each methodology in the light of how it will be used, and select the one that is easiest to manage and maintain. (Do not choose the one that is easiest to implement, you will support it much longer than you will write).

(This is long, but the essay "RLH" is a classic example of name / value pairs performed by amok.)

(Oh, and if you use it, take a look at SQL Server 2008 “Sparse Columns.” It doesn't seem like what you need, but you never know.)