I'm developing a database using MySQL 5 to have an eCommerce site. I would like the database to become as flexible as you possibly can to ensure that the proprietors of the site can also add custom characteristics for various items. For example, they may have a merchandise that has 4 shirt dimensions and three colors for every size available, or perhaps a product which has 6 shirt dimensions, 4 colors for every size and perhaps a 3rd attribute.

The issue I'm encountering is they should have the ability to control the amount for any product according to its various characteristics, not for that product itself. The organization could have a product which has 25 available of 1 color and style but have 13 available of the different size and color combination.

It is possible to good solution regarding how to structure this inside a MySQL database? Presently I've got a table which will keep product id, quantity and also the characteristics is going to be concatenated in 1 area utilizing a "key:value" syntax that's comma-delimited.

This really is my very first time trying to produce a system such as this. Any information/help could be greatly appreciated. If you want more details, I'm able to provide that a lot.

Hey men, I truly appreciate the recommendations. But to get this done "Derived Item" method, would I have to produce a different database table for every kind of product because the items might have variable characteristics connected together?

The easiest option would be clearly to create every shirt-color combo an entirely separate item, and abandon the attribute concept. In my opinion this is the way most real stores operate. It seems sensible considering how frequently the "base" products change anyway.

In the event that is not acceptable, you might have a DerivedItem table, where each row would be a separate derived item, which in fact had a mention of base item inside a BaseItem table. That will eliminate some redundancy at the expense of the more complicated design.

I'd opt for the items and derived items, or whatever you might like to refer to it as.

You are able to still put characteristics on these if you want.

After that you can put common characteristics around the product table (description etc) and individuals that vary around the derived product (colour, size, cost etc).

The characteristics might be best implemented like a separate table, having a foreign key around the derived attribute for such things as colour. That removes the risk of customers entering such things as "Dark Blue" and "Blue (Dark)" and expecting the body to like magic know that they're exactly the same colour ...!

Please consider the link below to obtain a fair idea how you ought to have your products table. I believe that one describes all of your doubts. http://www.webmasterworld.com/ecommerce/3586815.htm

Okay - the easiest method to do that is to possess a good consider what it's you are attempting to complete.

Undoubtedly, the very best solutions I've discovered to help you do this can be a chapter within this book:

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

If you're attempting to adopt this to technical characteristics however, you might want to take a look at how Magento Commerce get it done his or her technique is quite good too.