带有股票期权的库存管理

2021-12-26 00:00:00 database schema mysql relational-database database-design

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

我想出了三个表格:

创建表 shop_options (option_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,option_name VARCHAR(40) 非空，主键(option_id));INSERT INTO shop_options (option_id, option_name) VALUES (1, 'Size');INSERT INTO shop_options (option_id, option_name) VALUES (2, 'Color');创建表 shop_option_properties (prop_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,prop_name VARCHAR(40) 非空，主键 (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');创建表 shop_product_options (product_id INTEGER UNSIGNED NOT NULL,option_id INTEGER UNSIGNED NOT NULL,prop_id INTEGER UNSIGNED DEFAULT NULL,附加费 DECIMAL(7,2) NOT NULL DEFAULT '0.00',stock INTEGER UNSIGNED DEFAULT NULL,/* NULL = 库存不受限制 */外键 (product_id)REFERENCES shop_products(product_id),外键(option_id)参考 shop_options(option_id),外键(prop_id)参考 shop_option_properties(prop_id));

我已经确定这行不通，因为我的库存中可能有总共十件小件商品"和总共十件白色商品"，但没有库存总共十件小件白色商品".

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

编辑

<小时>

我将更新内容包括在下面，供其他和我一样有同样问题的人使用.我发现接受的答案一开始很难理解.基本上，我可以通过对 shop_product_options 表进行以下修改来保留上面的架构:

创建表 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,附加费 DECIMAL(7,2) UNSIGNED NOT NULL DEFAULT '0.00',stock INTEGER UNSIGNED DEFAULT NULL,PRIMARY KEY (po_id, product_id, option_id, prop_id),外键 (product_id)REFERENCES shop_products(product_id),外键(option_id)参考 shop_options(option_id),外键(prop_id)参考 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)值 (1, 1, 1, 3, '0.00', 10);INSERT INTO shop_product_options (po_id, product_id, option_id, prop_id, surcharge, stock)值 (1, 1, 2, 9, '0.50', 20);INSERT INTO shop_product_options (po_id, product_id, option_id, prop_id, surcharge, stock)值 (2, 1, 1, 5, '1.00', 30);INSERT INTO shop_product_options (po_id, product_id, option_id, prop_id, surcharge, stock)值 (2, 1, 2, 9, '0.75', 40);从 shop_product_options AS t1 中选择 t1.po_id、t2.title、t3.option_name、t4.prop_name、t1.surcharge、t1.stock加入 shop_products AS t2 ON t1.product_id = t2.product_idJOIN shop_options AS t3 ON t1.option_id = t3.option_id加入 shop_option_properties AS t4 ON t1.prop_id = t4.prop_idWHERE t1.product_id = 1 ORDER BY t1.po_id ASC;

这导致了 M 码绿色女鞋和 XL 码绿色女鞋，每种鞋的尺码和颜色都有不同的库存数量.

解决方案

我认为草案模型 (是一种常见模式.正如您在文章中看到的那样，这将带来优缺点.

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

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

检查对 ProductSpecification 的约束 {OptionValue.optionId =productTypeOption.optionId} 将阻止像White"这样的值被分配给大小".
检查对 ProductSpecification 的约束 {product.productTypeId =productTypeOption.productTypeId} 这将阻止像这样的产品耐克"被分配给汽车"的产品规格.

如果你使用代理标识符，你的数据库中就不能有这些类型的约束(试试这个).
需要在应用程序实现中完成额外的工作才能获得它们.
BTW 使用代理标识符，检查数据一致性，如果更感兴趣，请参阅选择主键:自然键或代理键.

<块引用>

基本价格、库存和附加费应该去哪里?

Nike"的Mens Shoe"似乎需要有价格、库存和附加费，所以它们是Product表的自然属性.

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.

解决方案

I think the draft model (following 6NF and 3NF) will help you.
I simplified naming convention by removing 'shop' keyword.
(Also shop entity may leads a separate concept AKA SaaS)

SqlFiddle Demo

About the questions in the comments:

Is it possible to have a unique product ID

Yes, it is a common pattern to use surrogate identifier on your tables. As you may see in the article, that will come with its pros and cons.

For example, in the question, you will see that primary key of ProductSpecification table is a composition of ProductTypeOptions, OptionValue and Product foreign keys.
In the mean time primary key of other tables like OptionValue is a composite key (OptionId + ValueName)
It looks like life will be more easy to have an ID field in every table as the primary key, yes it is but as a database designer you will loos something valuable, business logic.

In the current design you can have these constraint in Product-Specification table, they will show part of your business logic:

Check constraint on ProductSpecification {OptionValue.optionId = productTypeOption.optionId} that will prevent a value like "White" being assigned to "Size".
Check constraint on ProductSpecification {product.productTypeId = productTypeOption.productTypeId} that will prevent a product like "Nike" being assigned to productSpecifications of "Cars".

If you use surrogate identifier you can not have these type of constraints inside your data base (try this).
Extra work will be needed to be done inside you application implementation to gain them.
BTW use surrogate identifier, check data consistency, if more interested see choosing a Primary Key: Natural or Surrogate.

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

It seems that "Mens Shoe" of "Nike" needs to have price, stock and surcharge, so they are natural property of Product table.

相关文章