database 库存数据库设计

Inventory database design

提问by nmarmol

This is a question not really about "programming" (is not specific to any language or database), but more of design and architecture. It's also a question of the type "What the best way to do X". I hope does no cause to much "religious" controversy.

这不是一个真正关于“编程”的问题（不特定于任何语言或数据库），而是更多的设计和架构。这也是“做 X 的最佳方法是什么”类型的问题。我希望不会引起太多的“宗教”争议。

In the past I have developed systems that in one way or another, keep some form of inventory of items (not relevant what items). Some using languages/DB's that do not support transactions. In those cases I opted not to save item quantity on handin a field in the item record. Instead the quantity on handis calculated totaling inventory received - total of inventory sold. This has resulted in almost no discrepancies in inventory because of software. The tables are properly indexed and the performance is good. There is a archiving process in case the amount of record start to affect performance.

过去，我开发了以某种方式保留某种形式的物品清单（与哪些物品无关）的系统。一些使用不支持事务的语言/数据库。在这些情况下，我选择不在项目记录的字段中保存手头的项目数量。相反，手头数量是根据收到的库存总额计算的 - 销售的库存总额。这导致几乎没有因软件而导致的库存差异。表被正确索引并且性能良好。有一个存档过程，以防记录量开始影响性能。

Now, few years ago I started working in this company, and I inherited a system that tracks inventory. But the quantity is saved in a field. When an entry is registered, the quantity received is added to the quantity field for the item. When an item is sold, the quantity is subtracted. This has resulted in discrepancies. In my opinion this is not the right approach, but the previous programmers here swear by it.

现在，几年前我开始在这家公司工作，我继承了一个跟踪库存的系统。但是数量保存在一个字段中。登记条目后，收到的数量将添加到项目的数量字段中。当商品售出时，会减去数量。这导致了差异。在我看来这不是正确的方法，但这里以前的程序员发誓。

I would like to know if there is a consensus on what's the right way is to design such system. Also what resources are available, printed or online, to seek guidance on this.

我想知道是否就设计此类系统的正确方法达成共识。还有哪些资源是可用的，印刷的或在线的，以寻求这方面的指导。

Thanks

谢谢

采纳答案by Neil Aitken

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.

我编写的最后一个系统通过将每笔交易存储为具有正数或负数的记录来跟踪库存。我发现它工作得很好。

回答by Patrick Cuff

• The Data Model Resource Book, Vol. 1: A Library of Universal Data Models for All Enterprises
• The Data Model Resource Book, Vol. 2: A Library of Data Models for Specific Industries
• The Data Model Resource Book: Universal Patterns for Data Modeling

• 数据模型资源书，卷。1：适用于所有企业的通用数据模型库
• 数据模型资源书，卷。2：特定行业的数据模型库
• 数据模型资源手册：数据建模的通用模式

I have Vol 1 and Vol 2 and these have been pretty helpful in the past.

我有第 1 卷和第 2 卷，这些在过去非常有用。

回答by lubos hasko

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.

这取决于，库存系统不仅仅是计算物品。例如，出于会计目的，您可能需要了解基于 FIFO（先进先出）模型的库存会计价值。这不能通过简单的“收到的库存总额 - 销售的库存总额”公式来计算。但是他们的模型可能很容易计算出这一点，因为他们会随时修改会计价值。我不想详细说明，因为这不是编程问题，但如果他们发誓，也许您没有完全理解他们必须满足的所有要求。

回答by Steven A. Lowe

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 bugswhich 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!

我已经完成了两种方式的系统，两种方式都可以正常工作 - 只要您不忽略错误！

回答by Eddie Deyo

Take a look at the ARTS (Association for Retail Technology Standards) data model (http://nrf-arts.org). It uses a StockLedger table with a record each item and changes to the inventory are all tracked in InventoryJournalEntries.

查看 ARTS（零售技术标准协会）数据模型 ( http://nrf-arts.org)。它使用一个 StockLedger 表，其中记录了每个项目和库存的变化都在 InventoryJournalEntries 中进行跟踪。

回答by MusiGenesis

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.

但是，如果性能不是一个真正的大问题（并且现代数据库在计算行数方面已经足够好了，我什至很少担心这一点），我每次都会坚持使用实时计数。

回答by Eric Goodwin

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.

如果您想了解大型应用程序如何查看SugarCRM，它们具有库存管理模块，尽管我不确定它如何存储数据。

回答by Galwegian

I would opt for the first way, where

我会选择第一种方式，在那里

the quantity on hand is calculated totaling inventory received - total of inventory sold

手头数量是根据收到的库存总额计算的 - 销售的库存总额

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.

编辑：我还想将系统中的任何库存损失/损坏考虑在内，但我相信您已经涵盖了这一点。

回答by rmeador

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.

我以前研究过解决这个问题的系统。我认为理想的解决方案是预先计算的列，它可以让您两全其美。您的总数将是某处的一个字段，因此不需要昂贵的查找，但它不会与您的其余数据不同步（数据库保持完整性）。我不记得哪些 RDMS 支持预计算列，但如果您没有事务，那也可能不可用。

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.

您可能会使用触发器伪造预先计算的列（非常有效......我看不出有什么缺点）。不过，您可能需要交易。恕我直言，在进行这种受控非规范化时保持数据完整性是触发器的唯一合法用途。

回答by Roberto Rosario

Django-inventorygeared more to fixed assets, but might give you some ideas.

Django-inventory更适合固定资产，但可能会给你一些想法。

IE: ItemTemplate (class) -> ItemsOnHand (instance)

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.

ItemsOnHand 可以链接到更多的 ItemTemplates；示例打印机和墨盒是必需的。这也允许为每个 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.

每个 ItemsOnHand 都链接到 InventoryTransactions，这允许轻松审核。为了避免从数千个库存交易中计算实际的手头物品，使用检查点，它只是一个余额 + 一个日期。要计算现有项目，请查询以查找最近的检查点并开始添加或减去项目以查找当前项目余额。定期定义新的检查点。