I have seen both approaches at my current company and would definitely lean towards the first (calculating totals based on stock transactions).
If you are only storing a total quantity in a field somewhere, you have no idea how you arrived at that number. There is no transactional history and you can end up with problems.
The last system I wrote tracks stock by storing each transaction as a record with a positive or negative quantity. I have found it works very well.
It depends, inventory systems are about far more than just counting items. For example, for accounting purposes, you might need to know accounting value of inventory based on FIFO (First-in-First-out) model. That can't be calculated by simple "totaling inventory received - total of inventory sold" formula. But their model might calculate this easily, because they modify accounting value as they go. I don't want to go into details because this is not programming issue but if they swear by it, maybe you didn't understand fully all their requirements they have to accommodate.
both are valid, depending on the circumstances. The former is best when the following conditions hold:
the number of items to sum is relatively small
there are few or no exceptional cases to consider (returns, adjustments, et al)
the inventory item quantity is not needed very often
on the other hand, if you have a large number of items, several exceptional cases, and frequent access, it will be more efficient to maintain the item quantity
also note that if your system has discrepancies then it has bugs which should be tracked down and eliminated
i have done systems both ways, and both ways can work just fine - as long as you don't ignore the bugs!
It's important to consider the existing system and the cost and risk of changing it. I work with a database that stores inventory kind of like yours does, but it includes audit cycles and stores adjustments just like receipts. It seems to work well, but everyone involved is well trained, and the warehouse staff aren't exactly quick to learn new procedures.
In your case, if you're looking for a little more tracking without changing the whole db structure then I'd suggest adding a tracking table (kind of like from your 'transaction' solution) and then log changes to the inventory level. It shouldn't be too hard to update most changes to the inventory level so that they also leave a transaction record. You could also add a periodic task to backup the inventory level to the transaction table every couple hours or so so that even if you miss a transaction you can discover when the change happened or roll back to a previous state.
If you want to see how a large application does it take a look at SugarCRM, they have and inventory management module though I'm not sure how it stores the data.
I think this is actually a general best-practices question about doing a (relatively) expensive count every time you need a total vs. doing that count every time something changes, then storing the count in a field and reading that field whenever you need a total.
If I couldn't use transactions, I would go with the live count every time I needed a total. If transactions are available, it would be safe to perform the inventory update operations and the saving of the re-counted total within the same transaction, which would ensure the accuracy of the count (although I'm not sure this would work with multiple users hitting the database).
But if performance is not really a huge problem (and modern databases are good enough at counting rows that I would rarely even worry about this) I'd just stick with the live count each time.
I've worked on systems that solve this problem before. I think the ideal solution is a precomputed column, which gets you the best of both worlds. Your total would be a field somewhere, thus no expensive lookups, but it can't get out of sync with the rest of your data (the database maintains the integrity). I don't remember which RDMSs support precomputed columns, but if you don't have transactions, that might not be available either.
You could potentially fake precomputed columns (very effectively... I see no downside) using triggers. You'd probably need transactions though. IMHO, keeping data integrity when you're doing this sort of controlled denormalization is the only legitimate use for a trigger.
ItemsOnHand can be linked to more ItemTemplates; Example Printer & the ink cartridges is requires. This also allows to set Reorder points for each ItemOnHand.
Each ItemsOnHand is linked to InventoryTransactions, this allows for easy auditing. To avoid calculating actual on hand items from thousand of invetory transactions, checkpoints are used which are just a balance + a date. To calculate items on hand query to find the most recent checkpoint and start adding or substracting items to find the current balance of items. Define new checkpoints periodically.
I can see some benefit to having the two columns, but I'm not following the part about discrepancies - you seem to be implying that having the two columns (in and out) is less prone to discrepancy than a single column (current). Why is that?
Please keep in mind that I oversimplified this and my initial explanation. I know there is much more to inventory that just keeping track of quantities, but in this case that's were the problem lies and what we want to fix. At this point the reason to change it is preciselly the cost of supporting the problems caused by the current design.
Also I wanted to mention that although this is not a "coding" question is related to algoritms and design which IMHO are very important topics.
Then, you could run a process (daily?) along the lines of:
SELECT *
FROM Inventory
WHERE estimated_on_hand != inventory_received - inventory_sold
Of course, this relies on users looking at this alert, and doing something about it.
Also, you could have a function to reset inventory some how, either by updating inventory_sold/received, or perhaps adding another field "inventory_adjustment", which could be positive or negative.
发布评论
评论（12）
我在现在的公司见过这两种方法，并且肯定会倾向于第一种（根据股票交易计算总数）。
如果您仅将总数量存储在某个字段中，则您不知道如何得出该数字。 没有交易历史记录，您最终可能会遇到问题。
我编写的最后一个系统通过将每笔交易存储为具有正数或负数的记录来跟踪库存。 我发现它效果很好。
I have seen both approaches at my current company and would definitely lean towards the first (calculating totals based on stock transactions).
If you are only storing a total quantity in a field somewhere, you have no idea how you arrived at that number. There is no transactional history and you can end up with problems.
The last system I wrote tracks stock by storing each transaction as a record with a positive or negative quantity. I have found it works very well.
我有第 1 卷和第 2 卷，这些在过去非常有帮助。
I have Vol 1 and Vol 2 and these have been pretty helpful in the past.
视情况而定，库存系统不仅仅是计数物品。 例如，出于会计目的，您可能需要了解基于 FIFO（先进先出）模型的库存的会计价值。 这不能通过简单的“收到的库存总额 - 销售的库存总额”公式来计算。 但他们的模型可能很容易计算出这一点，因为他们会随时修改会计价值。 我不想详细说明，因为这不是编程问题，但如果他们发誓，也许您没有完全理解他们必须满足的所有要求。
It depends, inventory systems are about far more than just counting items. For example, for accounting purposes, you might need to know accounting value of inventory based on FIFO (First-in-First-out) model. That can't be calculated by simple "totaling inventory received - total of inventory sold" formula. But their model might calculate this easily, because they modify accounting value as they go. I don't want to go into details because this is not programming issue but if they swear by it, maybe you didn't understand fully all their requirements they have to accommodate.
两者都有效，具体取决于具体情况。 当满足以下条件时，前者是最好的：
另一方面， ，如果您有大量物品、一些特殊情况并且频繁访问，则维护物品数量会更有效。
另请注意，如果您的系统存在差异，则它存在错误，应对其进行跟踪下来并消除
我已经用两种方式完成了系统，并且两种方式都可以很好地工作 - 只要您不忽略错误！
both are valid, depending on the circumstances. The former is best when the following conditions hold:
on the other hand, if you have a large number of items, several exceptional cases, and frequent access, it will be more efficient to maintain the item quantity
also note that if your system has discrepancies then it has bugs which should be tracked down and eliminated
i have done systems both ways, and both ways can work just fine - as long as you don't ignore the bugs!
我会选择第一种方式，其中
以正确的方式出售。
编辑：我还想将任何库存损失/损坏纳入系统，但我确信您已经涵盖了这一点。
I would opt for the first way, where
The Right Way, IMO.
EDIT: I would also want to factor in any stock losses/damages into the system, but I'm sure you have that covered.
重要的是要考虑现有系统以及更改它的成本和风险。 我使用的数据库存储的库存与您的类似，但它包括审计周期和存储调整，就像收据一样。 它似乎运作良好，但参与的每个人都训练有素，而且仓库工作人员并不能很快学习新程序。
在您的情况下，如果您正在寻找更多的跟踪而不更改整个数据库结构，那么我建议添加一个跟踪表（类似于您的“交易”解决方案），然后将更改记录到库存级别。 更新库存水平的大多数更改应该不会太难，以便它们也留下交易记录。 您还可以添加一个定期任务，每隔几个小时左右将库存水平备份到事务表中，这样即使您错过了某个事务，您也可以发现更改何时发生或回滚到之前的状态。
如果您想了解大型应用程序如何运行，请查看 SugarCRM，他们有库存管理模块我不确定它如何存储数据。
It's important to consider the existing system and the cost and risk of changing it. I work with a database that stores inventory kind of like yours does, but it includes audit cycles and stores adjustments just like receipts. It seems to work well, but everyone involved is well trained, and the warehouse staff aren't exactly quick to learn new procedures.
In your case, if you're looking for a little more tracking without changing the whole db structure then I'd suggest adding a tracking table (kind of like from your 'transaction' solution) and then log changes to the inventory level. It shouldn't be too hard to update most changes to the inventory level so that they also leave a transaction record. You could also add a periodic task to backup the inventory level to the transaction table every couple hours or so so that even if you miss a transaction you can discover when the change happened or roll back to a previous state.
If you want to see how a large application does it take a look at SugarCRM, they have and inventory management module though I'm not sure how it stores the data.
我认为这实际上是一个一般的最佳实践问题，即每次需要总数时进行（相对）昂贵的计数，而不是每次发生变化时进行计数，然后将计数存储在一个字段中，并在需要时读取该字段。全部的。
如果我无法使用交易，那么每次需要总计时我都会使用实时计数。 如果交易可用，则可以安全地执行库存更新操作并在同一交易中保存重新计数的总数，这将确保计数的准确性（尽管我不确定这是否适用于多个用户）访问数据库）。
但是，如果性能并不是真正的大问题（并且现代数据库在计算行数方面足够好，我什至很少担心这一点），我每次都会坚持实时计数。
I think this is actually a general best-practices question about doing a (relatively) expensive count every time you need a total vs. doing that count every time something changes, then storing the count in a field and reading that field whenever you need a total.
If I couldn't use transactions, I would go with the live count every time I needed a total. If transactions are available, it would be safe to perform the inventory update operations and the saving of the re-counted total within the same transaction, which would ensure the accuracy of the count (although I'm not sure this would work with multiple users hitting the database).
But if performance is not really a huge problem (and modern databases are good enough at counting rows that I would rarely even worry about this) I'd just stick with the live count each time.
我之前研究过解决这个问题的系统。 我认为理想的解决方案是预先计算的列，它可以让您两全其美。 您的总计将是某个字段，因此不需要昂贵的查找，但它不会与其余数据不同步（数据库保持完整性）。 我不记得哪些 RDMS 支持预计算列，但如果您没有事务，那也可能不可用。
您可能会使用触发器伪造预先计算的列（非常有效......我认为没有任何缺点）。 不过，您可能需要交易。 恕我直言，在进行这种受控非规范化时保持数据完整性是触发器的唯一合法用途。
I've worked on systems that solve this problem before. I think the ideal solution is a precomputed column, which gets you the best of both worlds. Your total would be a field somewhere, thus no expensive lookups, but it can't get out of sync with the rest of your data (the database maintains the integrity). I don't remember which RDMSs support precomputed columns, but if you don't have transactions, that might not be available either.
You could potentially fake precomputed columns (very effectively... I see no downside) using triggers. You'd probably need transactions though. IMHO, keeping data integrity when you're doing this sort of controlled denormalization is the only legitimate use for a trigger.
Django-inventory 更适合固定资产，但可能会给您一些想法。
IE：ItemTemplate（类）-> ItemsOnHand（实例）
ItemsOnHand可以链接到更多ItemTemplates； 示例打印机和 需要墨盒。 这还允许为每个 ItemOnHand 设置重新订购点。
每个 ItemsOnHand 都链接到 InventoryTransactions，这样可以轻松进行审核。
为了避免从数千个库存交易中计算实际现有物品，使用的检查点只是余额+日期。 计算现有物品查询以查找最近的检查点并开始添加或减去物品以查找物品的当前余额。 定期定义新的检查点。
Django-inventory geared more to fixed assets, but might give you some ideas.
IE: ItemTemplate (class) -> ItemsOnHand (instance)
ItemsOnHand can be linked to more ItemTemplates; Example Printer & the ink cartridges is requires. This also allows to set Reorder points for each ItemOnHand.
Each ItemsOnHand is linked to InventoryTransactions, this allows for easy auditing.
To avoid calculating actual on hand items from thousand of invetory transactions, checkpoints are used which are just a balance + a date. To calculate items on hand query to find the most recent checkpoint and start adding or substracting items to find the current balance of items. Define new checkpoints periodically.
我可以看到拥有两列的一些好处，但我没有遵循有关差异的部分 - 您似乎暗示拥有两列（输入和输出）比单列（当前）更不容易出现差异。 这是为什么？
I can see some benefit to having the two columns, but I'm not following the part about discrepancies - you seem to be implying that having the two columns (in and out) is less prone to discrepancy than a single column (current). Why is that?
没有一两列，我的意思是“收到的库存总额 - 销售的库存总额”是这样的：
那么
请记住，我过度简化了这一点和我最初的解释。 我知道库存除了跟踪数量之外还有更多的内容，但在这种情况下，这就是问题所在以及我们想要解决的问题。 此时改变它的原因恰恰是支持当前设计所带来的问题的成本。
我还想提一下，虽然这不是一个“编码”问题，但与算法和设计有关，恕我直言，这是非常重要的主题。
感谢大家迄今为止的回答。
纳尔逊·马莫尔
Is not having one or two columns, what I meant with "totaling inventory received - total of inventory sold" is something like this:
then
Please keep in mind that I oversimplified this and my initial explanation. I know there is much more to inventory that just keeping track of quantities, but in this case that's were the problem lies and what we want to fix. At this point the reason to change it is preciselly the cost of supporting the problems caused by the current design.
Also I wanted to mention that although this is not a "coding" question is related to algoritms and design which IMHO are very important topics.
Thanks everybody for your answers so far.
Nelson Marmol
我们解决不同的问题，但您可能会对我们解决其中一些问题的方法感兴趣。
我们允许系统做出“最佳猜测”，并定期向用户反馈任何看起来错误的猜测。
要将其应用于库存，您可以有 3 个字段：
然后，您可以按照以下方式运行一个流程（每天？）：
当然，这依赖于用户查看此警报并对此采取措施。
此外，您还可以有一个功能来重置库存，方法是更新 inventory_sold/received，或者添加另一个字段“inventory_ adjustment”，该字段可以是正数或负数。
...只是一些想法。 希望它有帮助。
We solve different problems, but our approach to some of them might be interesting to you.
We allow the system to make a "best guess", and give the users regular feedback about any of those guesses that look wrong.
To apply this to inventory, you could have 3 fields:
Then, you could run a process (daily?) along the lines of:
Of course, this relies on users looking at this alert, and doing something about it.
Also, you could have a function to reset inventory some how, either by updating inventory_sold/received, or perhaps adding another field "inventory_adjustment", which could be positive or negative.
... just some thoughts. Hope it's helpful.