Help with database schema for e-commerce paint shop
Hi!
I am designing an e-commerce site primarily for selling paints and other associated products, eg. brushes, safety gear...
I have been trying to create a properly normalised (up to 3rd normal form) schema to achieve this. However, I have run into a bit of a dilemma! How do I go about tracking the stock for the paints?
There will be 400ml, 600ml and 1 litre paint canisters, but each size has a completely different set of available colours.
Here is the products table I have come up with so far:
--------------------------------
CREATE TABLE products (
product_id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
product_name VARCHAR(80) NOT NULL,
product_alias VARCHAR(120) default NULL,
price DECIMAL(6,2) NOT NULL,
summary VARCHAR(250) default NULL,
description TEXT default NULL,
image_name VARCHAR(40) default NULL,
thumb_name VARCHAR(40) default NULL,
weight_kg DECIMAL(6,3) default NULL,
volume_litres DECIMAL(6,3) default NULL,
colour VARCHAR(20) default NULL,
stock_level INT(5) default 0,
postage_category CHAR(1) default NULL,
PRIMARY KEY (product_id),
UNIQUE KEY product_name (product_name)
) ENGINE=MyISAM;
--------------------------------
The thing is, there will be repeat values in the colour column (as some colours are available for each paint canister size), meaning that this table will then violate 2nd normal form. The BIG problem though: if I add the product "400ml paint can" and create another table, "colours" to store all the colour info, how would I keep records of stock levels? Each paint can in a specific colour is 1 physical entity of stock, which is why it would be better to have "400ml, cobalt_blue" and "400ml, ruby_red" as seperate products in the above table, i.e. the stock level could be decremented for a sale/incremented when new stock is taken.
Please help me work out the best solution here!
Thanks very much to anyone who tries :)
Al