mysql - 组件及其子零件的库存管理(关系)

Question

我必须跟踪单个零件和套件(组件)的库存，但找不到令人满意的方法。样本伪造和超简化数据库:

Table prod:prodID  1prodName Flux capacitorprodCost 900prodPrice 1350 (900*1.5)prodStock 3-prodID  2prodName Mr FusionprodCost 300prodPrice 600 (300*2)prodStock 2-prodID  3prodName Time travel kitprodCost 1200 (900+300)prodPrice 1560 (1200*1.3)prodStock 2Table relsrelID  1relSrc  1 (Flux capacitor)relType  4 (is a subpart of)relDst  3 (Time travel kit)-relID  2relSrc  2 (Mr Fusion)relType  4 (is a subpart of)relDst  3 (Time travel kit)

prodPrice:它是根据成本计算的，但不是线性的。在此示例中，对于 500 或更少的成本，加价为 200%。对于 500-1000 的成本，加价为 150%。对于 1000+ 的成本，加价为 130%这就是为什么时间旅行套件比单个部件便宜得多的原因

prodStock:这是我的问题。我可以出售套件或单个零件，因此套件的库存是虚拟的。

购买时遇到的问题:一因此，当我加载股票时，我不知道如何估算它。

我卖的时候遇到的问题:如果我只卖套件，计算库存会很容易:“我有 3 个磁通电容器和 2 个 Mr Fusions，所以我有 2 个时间旅行套件和一个磁通电容器”但我可以出售套件或单个零件。因此，我必须同时跟踪单个零件和可能的套件的库存(并且我必须补偿售价)

可能这真的很简单，但我看不到简单的解决方案。恢复:我必须找到一种跟踪库存的方法，数据库/程序是必须做的(我不能要求店员更正库存)

我正在使用 php+MySql。但这是一个逻辑问题而不是编程问题

更新:很遗憾，Eagle 的解决方案无法工作。

• 关系可以而且是递归的(一个套件使用另一个套件)
• 有些套件确实使用了不止一个相同的部件(2 个通量电容器 + 1 个 Mr Fusion)
• 我确实需要存储套件库存的值(value)。相同的数据库用于用户想要购买零件的网页。而且我应该展示可用的库存(否则他们甚至不会尝试购买)。并且无法计算每次用户在网页上搜索的股票

但我喜欢用 bool 值将股票标记为虚拟的想法

Answer 1

好的，首先，由于时间旅行套件的 prodStock 是虚拟的，您不能将它存储在数据库中，它本质上是一个计算字段。如果您在表中有一个 bool 值，说明 prodStock 是否已计算，这可能会有所帮助。我会假装你在表中有这个字段，我现在将其称为 isKit(其中 TRUE 表示它是一个工具包，而 prodStock 应该计算)。

现在计算每个库存商品的数量:

select p.prodID, p.prodName, p.prodCost, p.prodPrice, p.prodStock from prod p where not isKit
union all
select p.prodID, p.prodName, p.prodCost, p.prodPrice, min(c.prodStock) as prodStock
from 
  prod p
  inner join rels r on (p.prodID = r.relDst and r.relType = 4)
  inner join prod c on (r.relSrc = c.prodID and not c.isKit)
where p.isKit
group by p.prodID, p.prodName, p.prodCost, p.prodPrice

我为第二个产品使用别名 c 来代表“组件”。我明确地写了 not c.isKit 因为这不会递归地工作。出于效率原因，使用 union all 而不是 union，因为它们都将返回相同的结果。

注意事项:

• 这不会以递归方式工作(例如，如果套件需要来自另一个套件)。
• 这只适用于套件只需要一个特定的项目(例如，如果时间旅行套件是需要2个通量电容器和1Fusion 先生，这行不通)。
• 我没有对此进行测试，因此可能存在轻微的语法错误。
• 这只计算prodStock 字段；做其他领域你需要类似的逻辑。

如果您的查询比我假设的复杂得多，我深表歉意，但我希望这可以帮助您找到可行的解决方案。

至于购买套件时如何处理数据，这是假设您仅将 prodStock 存储在组件中。因此，例如，如果您从供应商处购买时间机器，而不是增加时间机器产品的 prodStock，您会增加磁通电容器和 Mr. fusion 的价格。