Custom Product Attributes for eCommerce Web Site

Question

I am creating a database using MySQL 5 for an eCommerce web site. I want the database to be as flexible as possible so that the owners of the web site can add custom attributes for various types of products. For instance, they can have a product which has 4 shirt sizes and 3 colors for each size available, or a product that has 6 shirt sizes, 4 colors for each size and possibly a 3rd attribute.

The problem I am running into is that they should be able to control the quantity for a product based on its various attributes, not for the product itself. The company may have a product that has 25 in stock of one style and color but have 13 in stock of a different size and color combination.

Is there a good solution on how to structure this in a MySQL database? Currently I have a table that will store the product id, quantity and the attributes will be concatenated in 1 field using a "key:value" syntax that is comma-delimited.

This is my first time trying to create a system like this. Any information/help would be greatly appreciated. If you need more information, I can provide that as well.

Answer 1

The simplest solution is obviously to make every shirt-color combo a totally separate item, and abandon the attribute concept. I believe this is how most real stores operate. It makes sense when you consider how often the "base" items change anyway.

If that isn't acceptable, you could have a DerivedItem table, where each row was a separate derived item, which had a reference to the base item in a BaseItem table. That would eliminate some redundancy at the cost of a more complex design.

Answer 2

I'd go with the products and derived products, or whatever you might want to call it.

You can still put attributes on these if you wish.

You can then put common attributes on the product table (description etc) and those that vary on the derived product (colour, size, price etc).

The attributes would be best implemented as a separate table, with a foreign key on the derived attribute for things like colour. That eliminates the chance of users entering things like "Dark Blue" and "Blue (Dark)" and expecting your system to magically know that they are the same colour ...!

Answer 3

Hey guys, I really appreciate the recommendation. But to do this "Derived Item" method, would I need to create a different database table for each type of product since the products could have variable attributes associated with them?

Answer 4

Hi,

Please look at the link below to get a fair idea on how you should have your product table. I think this one explains all your doubts. http://www.webmasterworld.com/ecommerce/3586815.htm

Answer 5

Okay - the best way to do this is to have a good think about what it is you are trying to do.

By far, the best solutions I have found to enable you to do this is a chapter in this book:

Beginning PHP and MySQL E-Commerce: From Novice to Professional

If you are trying to adopt this to technical attributes however, you may want to look at how Magento Commerce do it as their method is quite good too.