清单数据库设计 [英] Inventory database design

问题描述

这不是真正关于编程的问题（不是特定于任何语言或数据库），而是更多的设计和架构。这也是一个类型的问题什么最好的方法做X。

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.

在过去，我已经开发了系统，以某种方式或另一种方式，保持某种形式的库存的物品（不是相关什么项目）。一些使用不支持事务的语言/ DB。在这些情况下，我选择不保存项目记录中的字段中的项目 。而是根据所接收的库存 - 销售的库存的总计来计算 的数量。这导致由于软件而导致的库存差异几乎没有。表格被正确索引，性能良好。有一个归档过程，以防记录开始影响性能。

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 hand in a field in the item record. Instead the quantity on hand is 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.

感谢

推荐答案

我在目前的公司看过这两种方法，肯定会倾向于第一种（根据股票交易计算总额）。

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屋！