clothing ecommerce mysql php

hi,

I'm developing a php based CMS for an ecommerce store and it will be selling clothes (amongst other things) and I'd like a bit of insight from other developers into how to approach the issue of stock levels of different colours and of different sizes of the same garment.

At the moment I've just got a single table in my DB called 'products' - which has the following structure:

product_id,
product_category_id,
product_type_id,
product_ref,
product_name,
product_desc,
product_format,
product_sizes,
product_colours,
product_price,
product_quantity

When a product is added into the system, the admin adds sizes, such as 'small, medium, large' and colours 'red, green, blue' for the product, but there's no relationship between them. What I really need is to have the following (I think):

product
>> colours
>>>> colour 1
>>>>>>> sizes
>>>>>>>>> size A
>>>>>>>>>>> quantity

I was originally going to have a table for each (colours, sizes etc), but that was becomming quite a monster to administer, so (I think) I would prefer to do it all with php. I was thinking of doing something along the lines of:

$sVariations = "Red-Extra Small=50,Small=50,Medium=10,Large=25,Extra Large=25;Green-Small=25,Medium,15";

and then using php to split it up like so:

[PHPNET]<?php

$sVariations = "Red-Extra Small=50,Small=50,Medium=10,Large=25,Extra Large=25;Green-Small=25,Medium,15";

$aVariations = split(";", $sVariations);

$aColours = split("-", $aVariations[0]);

$aSizes = split(",", $aColours[1]);

print_r ($aVariations);

print_r ($aColours);

print_r ($aSizes);

?>[/PHPNET]

what does anyone think of either approach, any ideas on which I should go for, or any suggestions on another approach I haven't seen.

Many thanks,

Jon

I have moved this thread to Database development.

Although you're mentioning MySQL and PHP, the question seems more about confirming your database structure than solving an error in your PHP code.

I think seperate tables for sizes and colours isn't a bad idea...

If you consider the fact that you might want to know how many small white shirts you have, it's a simple SELECT statement away.
Or tell me how many green shirts [of any size] you have...

Is this for your personal development as a programmer, or is it for a full retail store?
If it's for proper retail, I would advise a proper structure, well planned.