Database Answers Allotments in Covent Garden, London (Click for Web Site)

Home Ask a Question Best Practice Careers Contact Us Data Models Search Site Map
Product Catalogs for Paintss   
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
About Us Contact Us