我正在尝试创建一个库存管理模式，我可以在其中跟踪与产品相关的各种选项的库存。产品可能有任意数量的选项，但在本示例中，我将使用“尺寸”和“颜色”选项。

我想出了三个表:

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

我确定这行不通，因为我可能有“总共 10 个小商品”和“总共 10 个白色商品”的库存，但没有“总共 10 个白色小商品”的库存。

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

编辑

我将下面的更新包括在内，以供与我遇到同样问题的其他人使用。一开始我发现接受的答案很难理解。基本上，我可以通过对 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 码绿色女鞋，每种鞋码和颜色的库存数量不同。

最佳答案

我认为草案模型(following 6NF 和 3NF)会对您有所帮助。
我通过删除“shop”关键字简化了命名约定。
(此外，商店实体可能会导致一个单独的概念 AKA SaaS)

SqlFiddle Demo

关于评论中的问题:

Is it possible to have a unique product ID

是的，使用 surrogate identifier 是一种常见的模式在你的 table 上。正如您在文章中看到的那样，这有利也有弊。

例如，在该问题中，您会看到 ProductSpecification 表的主键是由 ProductTypeOptions、OptionValue 和 Product<> 外键。
同时其他表的主键如OptionValue 是复合键(OptionId + ValueName)
在每个表中都有一个 ID 字段作为主键看起来生活会更容易，是的，但是作为数据库设计者，您将失去一些有值(value)的东西，业务逻辑。

在当前的设计中，您可以在 Product-Specification 表中设置这些约束，它们将显示您的部分业务逻辑:

• 检查 ProductSpecification 的约束 {OptionValue.optionId = productTypeOption.optionId} 将阻止像“White”这样的值 被分配给“尺寸”。
• 检查 ProductSpecification 的约束 {product.productTypeId = productTypeOption.productTypeId} 这将阻止类似的产品 “Nike”被分配给“Cars”的产品规范。

如果你使用代理标识符，你就不能在你的数据库中有这些类型的约束(试试这个)。
需要在您的应用程序实现内部完成额外的工作才能获得它们。
顺便说一句 use surrogate identifier, check data consistency ，如果更感兴趣，请参阅 choosing a Primary Key: Natural or Surrogate .

Where should the base price, stock, and surcharge go?

好像“Nike”的“Mens Shoe”需要有price、stock和surcharge，所以它们是Product表的自然属性。

关于mysql - 使用股票期权进行库存管理，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/21010251/