I'm trying to create an inventory management schema where I can track the stock of various options related to products. A product may have any number of options, but for this example I'll use "size" and "color" options.
I've come up with three tables:
CREATE TABLE shop_options (
option_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
option_name VARCHAR(40) NOT NULL,
PRIMARY KEY (option_id)
);
INSERT INTO shop_options (option_id, option_name) VALUES (1, 'Size');
INSERT INTO shop_options (option_id, option_name) VALUES (2, 'Color');
CREATE TABLE shop_option_properties (
prop_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
prop_name VARCHAR(40) NOT NULL,
PRIMARY KEY (prop_id)
);
INSERT INTO shop_option_values (prop_id, prop_name) VALUES (1, 'XS');
INSERT INTO shop_option_values (prop_id, prop_name) VALUES (2, 'S');
INSERT INTO shop_option_values (prop_id, prop_name) VALUES (3, 'M');
INSERT INTO shop_option_values (prop_id, prop_name) VALUES (4, 'L');
INSERT INTO shop_option_values (prop_id, prop_name) VALUES (5, 'XL');
INSERT INTO shop_option_values (prop_id, prop_name) VALUES (6, 'White');
INSERT INTO shop_option_values (prop_id, prop_name) VALUES (7, 'Black');
INSERT INTO shop_option_values (prop_id, prop_name) VALUES (8, 'Red');
INSERT INTO shop_option_values (prop_id, prop_name) VALUES (9, 'Green');
INSERT INTO shop_option_values (prop_id, prop_name) VALUES (10, 'Blue');
CREATE TABLE shop_product_options (
product_id INTEGER UNSIGNED NOT NULL,
option_id INTEGER UNSIGNED NOT NULL,
prop_id INTEGER UNSIGNED DEFAULT NULL,
surcharge DECIMAL(7,2) NOT NULL DEFAULT '0.00',
stock INTEGER UNSIGNED DEFAULT NULL, /* NULL = stock is not limited */
FOREIGN KEY (product_id)
REFERENCES shop_products(product_id),
FOREIGN KEY (option_id)
REFERENCES shop_options(option_id),
FOREIGN KEY (prop_id)
REFERENCES shop_option_properties(prop_id)
);
I've determined that this won't work, because I may have "ten total small items" in stock, and "ten total white items" in stock, but not "ten total small white items" in stock.
How can I improve my schema to properly track stock for each option a product might have?
EDIT
I'm including the update below for anyone else having the same trouble with this as I was. I found the accepted answer difficult to understand at first. Basically, I can keep the schema I have above with the following modification on the shop_product_options table:
CREATE TABLE shop_product_options (
po_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
product_id INTEGER UNSIGNED NOT NULL,
option_id INTEGER UNSIGNED NOT NULL,
prop_id INTEGER UNSIGNED NOT NULL,
surcharge DECIMAL(7,2) UNSIGNED NOT NULL DEFAULT '0.00',
stock INTEGER UNSIGNED DEFAULT NULL,
PRIMARY KEY (po_id, product_id, option_id, prop_id),
FOREIGN KEY (product_id)
REFERENCES shop_products(product_id),
FOREIGN KEY (option_id)
REFERENCES shop_options(option_id),
FOREIGN KEY (prop_id)
REFERENCES shop_option_properties(prop_id)
);
With the added po_id and combination of keys as primary, I can now insert and extract "grouped" data as follows:
INSERT INTO shop_products (product_id, title) VALUES (1, 'Womens Shoe');
INSERT INTO shop_product_options (po_id, product_id, option_id, prop_id, surcharge, stock)
VALUES (1, 1, 1, 3, '0.00', 10);
INSERT INTO shop_product_options (po_id, product_id, option_id, prop_id, surcharge, stock)
VALUES (1, 1, 2, 9, '0.50', 20);
INSERT INTO shop_product_options (po_id, product_id, option_id, prop_id, surcharge, stock)
VALUES (2, 1, 1, 5, '1.00', 30);
INSERT INTO shop_product_options (po_id, product_id, option_id, prop_id, surcharge, stock)
VALUES (2, 1, 2, 9, '0.75', 40);
SELECT t1.po_id, t2.title, t3.option_name, t4.prop_name, t1.surcharge, t1.stock FROM shop_product_options AS t1
JOIN shop_products AS t2 ON t1.product_id = t2.product_id
JOIN shop_options AS t3 ON t1.option_id = t3.option_id
JOIN shop_option_properties AS t4 ON t1.prop_id = t4.prop_id
WHERE t1.product_id = 1 ORDER BY t1.po_id ASC;
This results in a size M green womens shoe, and size XL green womens shoe, with different stock quantities on the sizes and colors for each.
