Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am working on updating our website's product database. It’s built in MySQL but this is more of a general database design pattern question.

I’m planning on switching to a Supertype/Subtype pattern. Our current/previous database is mainly a single table that has data on a single type of product. We’re looking at expanding our product offering to include dissimilar products.

I have a question regarding the product attributes tables. The idea here is a product can have a list of given attributes such as color: red, green, blue, or material: plastic, wood, chrome, aluminum, etc.

This list would be stored in a table and the primary key (PK) for that attribute item will be used in the specific product table as a foreign key (FK).

This allows a website interface to pull the list of attributes for a given attribute type and spit it out in a drop-down select menu or some other UI element. This list can be considered an "authorized" list of attribute values.

The number of joins that ends up happening when pulling a specific product appears excessive to me. You must join every product attribute table to the product so you can get that attribute's fields. Commonly, that field might simply be nothing more than a string (varchar) for its name.

This design pattern ends up creating a large number of tables as well as you end up with a table for each attribute. One idea to counteract this would be to create something more of a “grab bag” table for all product attributes. Something like this:

This could help reduce table creep but it doesn’t cut down the number of joins and it feels a little wrong combining so many different types into a single table. But you would be able to get all the available “color” attributes pretty easily.

However, there might be an attribute that has more fields than just "name" such as a color's RGB value. This would require that specific attribute to possibly have another table or to have a single field for name:value pair (which has it's own drawbacks).

The last design pattern I can think of is storing the actual attribute value in the specific product table and not have an “attribute table” at all. Something like this:

This would eliminate joins and prevent table creep (maybe?). However, this prevents having an “authorized list” of attributes. You could return all the currently entered values for a given field (ie: color) but this also eliminates the idea of having an “authorized list” of values for a given attribute.

To have that list, you would have to still create a “grab bag” attribute table or have multiple tables (table creep) for each attribute.

This creates the bigger drawback (and why I’ve never used this approach) of now having the product name in multiple locations.

If you have the color value of “red” in the “master attribute table” and also store it in the “product_[type]” table, an update to the “master” table will cause a potential data integrity issue if the application doesn’t update all the records with the old value in the “product_type” table as well.

So, after my long winded explanation and analysis of this scenario, my realization is that this can’t be an uncommon scenario and there might even a name for this type of situation.

Are there generally accepted solutions to this design challenge? Is the potentially large number of joins acceptable if the tables are relatively small? Is storing the attribute name, instead of a attribute PK acceptable under some situation? Is there another solution I’m not thinking about?

A few notes about this product database / application:

Products aren’t frequently updated/added/removed

Attributes aren’t frequently updated/added/removed

The table is most frequently queried for reading / returning information

Server side caching is enabled to cache the result of a given query/result

I plan on starting with just one product type and extending/adding others over time and will have potentially 10+ different types

Good question. It will start small 3-4 but potentionally grow larger to 10+
–
jmbertucciSep 20 '12 at 23:19

What do you mean by "Authorized list of attributes"?
–
Emmad KareemSep 22 '12 at 3:12

Sorry, it Should be "attribute value". The idea that you have a table listing all the values allowed for an attribute. Ie. here is a list of 10 colors that this product type can be. These 10 are the "authorize" values some one could choose.
–
jmbertucciSep 22 '12 at 14:42

I am wondering if it would be fine having all these attribute values joined to the product type table if I ultimately create a "view" on top of it?
–
jmbertucciSep 24 '12 at 13:56

As you case see the data might be in a better format for you, but if you have an unknown number of attributes, it will easily become untenable due to hard-coding attribute names, so in MySQL you can use prepared statements to create dynamic pivots. Your code would be as follows (See SQL Fiddle With Demo):

+1 - A fantastically written answer. I'm still taking a few moments to re-read and digesting this answer before accepting. It does look like a good solution to my question about joins and product attributes and even goes above and beyond with examples of pivots and prepared statements. So, I'll start with a +1 for that. =)
–
jmbertucciSep 26 '12 at 13:37

@jmbertucci you seemed concerned about querying the tables so I figured I would provide you some samples. :)
–
bluefeetSep 26 '12 at 13:38

Indeed. I'm going "doh" that I didn't see doing a cross table of product to attribute. Probably a case of over thinking especially after immersing design patterns and theories. Also, my DBA experience is basic and doing more with prepared statements is something I need, so your inclusion is most helpful. And this answer has helped break down that "writers block" I was having so I can move on with this project, which makes my day. =)
–
jmbertucciSep 26 '12 at 14:23