库存管理与股票期权 [英] Inventory management with stock options

查看：116
本文介绍了库存管理与股票期权的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在尝试创建库存管理模式，其中我可以跟踪与产品相关的各种选项的库存。产品可以有任意数量的选项，但在本例中，我将使用size和color选项。



  CREATE TABLE shop_options（
 option_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT，
 option_name VARCHAR ，
 
 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 ，2）NOT NULL DEFAULT'0.00'，
股票INTEGER UNSIGNED DEFAULT NULL，/ * NULL =股票不限制* / 
 
 FOREIGN KEY（product_id）
参考shop_products product_id），
 FOREIGN KEY（option_id）
 REFERENCES shop_options（option_id），
 FOREIGN KEY（prop_id）
 REFERENCES shop_option_properties（prop_id）
）;

我确定这不会工作，因为我可能有十个​​小项目在库存，和十个白色项目的股票，但不是十个小小的白色项目股票。



如何提高我的模式正确跟踪股票



EDIT





我包括的更新下面的任何人有同样的麻烦，因为我是。我发现接受的答案很难理解起初。基本上，我可以在 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 ，
 fee 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）
参考shop_options（option_id），
 FOREIGN KEY b 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型绿色女鞋，

解决方案

我认为草图模型（。正如你在文章中可能看到的，这将带来它的优点和缺点。


例如，在问题中，您将看到 ProductSpecification 表是 ProductTypeOptions ， OptionValue 和产品外键。
同时，其他表如 OptionValue 的主键是一个复合键（ OptionId + ValueName ）

看起来生活会更容易在每个表中有一个 ID 字段作为主键，是的但作为一个数据库设计师，你会失去一些有价值的，业务逻辑。


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




  • 检查上的约束 {OptionValue.optionId =
    productTypeOption.optionId} c> c> {product.productTypeId =
    productTypeOption.productTypeId}     ，将阻止类似
    Nike的产品分配给Cars的productSpecifications。 li>


如果您使用代理标识符，您的数据库中不能有这些类型的约束（请尝试这样）。

需要在应用程序实现中完成额外的工作才能获得它们。

BTW 使用替代标识符，检查数据一致性，如果更感兴趣，请参阅选择主键：自然或代理。


基本价格，库存和附加费应该在哪里？ / p>

Nike的Mens Shoe需要有价格，库存和附加费，所以它们是产品表。


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.

这篇关于库存管理与股票期权的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆