数据库设计——记录交易
我希望得到一些关于如何为以下场景构建数据库的建议：
我正在使用 Ruby on Rails，因此我有下表：
- 产品
- 销售人员
- 商店
产品是批量生产的，因此每个产品都有一个批次代码，所以我想我还需要一个批次表，它指的是产品类型。
- 批次
在现实世界中，销售人员获取产品项目（来自特定批次）并在适当的时候将其发送给商店。重要的是，批次很大，可能分布在许多销售人员以及随后的商店中。
在将来的某个日期，我想运行以下报告：
- 显示发布到特定商店的所有产品批次。
- 显示销售人员持有的所有批次（即尚未售出）。
现在，我假设我需要构建一个事务表，例如
- 事务
- salesperson_id
- batch_id（通过它可以确定产品）
- store_id
- typeOfTransaction（销售人员是否获得了一些库存，或出售了一些库存）
- 数量
通过动态运行交易记录表，我可以得出上述报告。然而，这似乎效率低下，而且随着时间的推移，速度越来越慢。
我的问题是：跟踪此类交易的最佳方法是什么，最好不需要动态处理所有交易来从给定商店的批次中获取总商品。
我不相信我可以只保留库存的中央记录，因为产品是批量进来的，并且批次是由销售人员在商店中分发的。
谢谢。
I would appreciate some advice on how to structure a database for the following scenario:
I'm using Ruby on Rails, and so I have the following tables:
- Products
- Salespeople
- Stores
Products are manufactured in batches, so each product item has a Batch code, so I think I will also need a table of batches, which refers to a product type.
- Batch
In the real world, Salespeople take Product items (from a specific Batch) and in due course issue it to a Store. Importantly, Batches are large, and may be spread across many Salespeople, and subsequently, Stores.
At some future date, I would like to run the following reports:
- Show all Batches of a Product issued to a specific Store.
- Show all Batches held by a Salesperson (i.e. not yet sold).
Now, I'm assuming I need to build a table of Transactions, something like,
- Transaction
- salesperson_id
- batch_id (through which the product can be determined)
- store_id
- typeOfTransaction (whether the Salesperson has obtained some stock, or sold some stock)
- quantity
By dynamically running through a table of Transaction records, I can could derive the above reports. However, this seems inefficient and, over time, increasingly slow.
My question is: what is the best way to keep track of transactions like this, preferably without requiring dynamic processing of all transactions to derive total items from a batch given to a given store.
I don't believe I can just keep a central record of stock as Product comes in Batches, and Batches are distributed by Salepeople across Stores.
Thank you.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
相信它。 :-)
根据我的经验，存储此类内容的唯一正确方法是将其分解为类似于 T-leger 会计的内容，即使用会计科目表进行借记/贷记。正如您所发现的，它需要动态处理才能得出总计，但在处理报告和审计跟踪时，任何不足都会导致棘手的查询。
您可以通过使用触发器（例如每个商店每月的库存变动）维护部分或全部总余额来显着加快速度。这将减少运行较大查询时需要求和的行数。您想要维护其中哪一个取决于您的应用程序和报告要求。
Believe it. :-)
In my experience, the only correct way to store this kind of stuff, is to break it down to something akin to T-leger accounting, i.e. debit/credit with a chart of accounts. It requires dynamic processing to derive totals as you've found out, but anything short of that will lead to tricky queries when dealing with reports and audit trails.
You can speed things up significantly, by maintaining partial or complete aggregate balances using triggers (e.g. monthly stock movements per store). This will reduce the number of rows you need to sum when running larger queries. Which of these you'll want to maintain will depend on your app and your reporting requirements.