库存管理与库存选项

我正在尝试创建一个库存管理模式,我可以跟踪与产品相关的各种选项的库存.产品可能有任意数量的选项,但对于此示例,我将使用"大小"和"颜色"选项.

我想出了三张桌子:

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)
);

我已经确定这不会起作用,因为我的库存中可能有"十件小件物品",库存中有"十件白色物品",但库存中没有"十件小白件".

如何改进我的架构以正确跟踪产品可能具有的每个选项的库存？

编辑

我将以下更新包含在其他任何与此相同的问题中.我发现接受的答案起初很难理解.基本上,我可以通过以下修改保留上面的模式shop_product_options:

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)
);

通过添加po_id和组合键作为主键,我现在可以插入和提取"分组"数据,如下所示:

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;

这导致M尺寸的绿色女式鞋和尺寸为XL的绿色女式鞋,每种尺寸和颜色的库存数量不同.