问题描述
我正在尝试创建一个库存管理模式,我可以在其中跟踪与产品相关的各种选项的库存.一个产品可能有任意数量的选项,但在本例中,我将使用尺寸"和颜色"选项.
我想出了三个表格:
我已经确定这行不通,因为我的库存中可能有总共十件小件商品"和总共十件白色商品",但没有库存总共十件小件白色商品".
如何改进我的架构以正确跟踪产品可能具有的每个选项的库存?
编辑
<小时>我将更新内容包括在下面,供其他和我一样有同样问题的人使用.我发现接受的答案一开始很难理解.基本上,我可以通过对 shop_product_options
表进行以下修改来保留上面的架构:
使用添加的 po_id
和键组合作为主键,我现在可以插入和提取分组"数据,如下所示:
这导致了 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:
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:
With the added po_id
and combination of keys as primary, I can now insert and extract "grouped" data as follows:
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.
这篇关于带有股票期权的库存管理的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!