Back to the Data Model.
The Area being Modelled is :-
Product Catalogs for Paints
Subject: How do I structure a Product Catalogue for paint with different colours and sizes, AND track stock levels?
Question: If it helps you, I posted my question here: http://forums.mysql.com/read.php?125,397571,397571#msg-397571
But I thought I'd kindly ask for your help in the absence of a response :-)
I am doing an e-commerce paint shop myself from the ground up, but have run into a brick wall with the database design!
There will be paints in different sized cans (400ml, 600ml and 1000ml) in the catalogue.
Each size has it's own unique set of available colours, but there is some cross-over of available colours.
I could have just one product catalogue table and store each paint can as a physical entity, but then it will
not be in normal 2nd normal form, because the "can_size" and "colour" columns would have repeating, identical values!
But then at least I could have a column for "stock_level".
However, if I have a separate "colours" table, yes I could reference the colour_id from the products table (via an associative table),
but how on earth would i be able to store info on stock levels for each can size+colour?
There are also going to be accessories for sale in the catalogue, e.g. brushes, which will not have the same product attributes as the paints!
Another problem. What suggestion do you have for storing the product information?
Thanks so much in advance.
Please feel free to request any feedback about your site too :)
Bye! Alec Pritchard
Barry has identified the 'Things of Interest' as follows :-
1. Can Sizes
2. Paint Colours
3. Others to be determined
© DataBase Answers Ltd. 2010