高级交易库存数据库设计的教程或帮助？
我正在为我们的电子商务公司开发一个交易库存系统，并且我正在寻找某种关于如何使用 MySQL 数据库完成此任务的指南或教程。有关于如何编程的指南，以及关于如何使用此类系统的指南，但我无法找到具有建议的表结构或实现您自己的表结构的最佳实践的资源。
在这个系统中，所有物品都被购买、存储、转售，然后运输（没有制造）。该系统应支持多个“位置”（即威斯康星州、北卡罗来纳州）。每个位置可以有一个或多个“仓库”（彼此相邻的建筑物）。每个仓库可以有一个或多个“岛”，每个岛有一个或多个“货架”，每个货架有一个或多个“箱”。商品存放在箱子中，每种尺寸/颜色都有自己的箱子。 可能会存放在多个箱子中（即，如果我们通过一次订购 2000 个获得折扣，我们可能会在低箱子中储存 10 个，并将其余的放在“库存过多”的高架上。
有些物品 它们各自的位置很简单，我可能会有一个 Bins 表，例如：
BinID BinName LocationID WarehouseID IsleID ShelfID --------------------------------------------------------------------------------- 1 Widget Bin A 1 1 1 1 2 Widget Bin B 1 2 2 5 3 Large Widget Rack 1 1 5 17 4 Widget Overstock 2 3 6 23
然后使用库存交易表在 Bins 之间移动物品，例如：
TransID SourceBinID DestBinID QTY Date Memo --------------------------------------------------------------------------------- 1 4 1 10 7-22-2011 Moved 10 Widgets...
但这是我感到困惑的地方：
假设现在是午夜，一位客户从一个箱子里有 5 个小部件。客户已经为他的 2 个小部件付款了，所以没有人可以购买它们，但现在是午夜，没有人在工作，所以他的小部件仍然放在箱子里，我需要某种东西。交易会减少“可销售”的小部件数量，但不会减少货架上的实际数量。
第二天，员工挑选这些小部件并将其移至包装区域，以记录这些物品现在已经到货。在包装线上，不再在垃圾箱中，但包装需要在一个特殊的位置，因为它与常规的“垃圾箱”不同，对吧？因此，我们需要以某种方式将物品从垃圾箱移动到特殊的非垃圾箱位置，并且我们需要一个事务条目来执行此操作。
然后还有其他“特殊”地方，例如退货隔离区，以及当该订单的其他商品缺货时搁置商品的地方。
如果您知道一本书或在线资源可以从编程/数据库的角度解释如何做到这一点，那就太好了。或者，如果有人已经知道如何做到这一点并且愿意分享，那就太好了！
谢谢！
更新：
我对此进行了更多思考，“可销售”库存（我认为它被称为“现有”）可能可以动态计算。 “所有箱的总数”-“未完成的订单”=“现有”。问题是，这是否会太慢而无法实现？它需要执行多个查询，然后循环结果以获得现有总数。
另一种方法可能是为“现有”设置一个单独的事务表，但这样您就有两个库存日记帐 - 一个“现有”和一个“实际”。即使它们应该始终保持同步（如果不是，那就是一个错误！），但仍然感觉不对劲？
我仍然不确定如何处理物理场所。当你发货时，它就从库存中消失了，但对于复式记账式会计，它需要去某个地方。所以我需要某种“消失”的垃圾箱。但把“消失”和“打包台”变成“垃圾箱”感觉也不对，因为它们实际上并不是垃圾箱。
更新 3
动向：
MoveID TransID SourceBinID DestBinID Memo --------------------------------------------------------------------------------- 1 1 4 1 Moved 10 Widgets to bin 1 2 2 1 4 Received 10 Widgets from bin 4
问题：
IssueID TransID SourceBinID Memo --------------------------------------------------------------------------------- 1 3 4 Shipped Widget to Customer 2 4 1 Shipped Widget to Customer
交易：
TransID ItemID Date QTY Type --------------------------------------------------------------------------------- 1 1 7-22-2011 10 Move 2 1 7-22-2011 -10 Move 3 1 7-23-2011 1 Issue 4 1 7-24-2011 2 Issue
更新 4
好的，我将再次尝试解决此问题，暂时不进行分配。
位置表 - “位置”是指物品可以物理“存在”的地方。
LocationID LocationTypeID LocationName ------------------------------------------------------------- 1 1 A Widget Bin 2 1 A Widget Bin 3 1 A Widget Bin 4 1 A Widget Bin 5 5 Vendor (nowhere) 6 3 Packing Table 1 7 4 Gone (shipped to customer)
位置有一个“类型”。位置可以是仓位、积压位置、装箱表，也可以代表客户（对于出库）或供应商（对于入库）。
位置类型
LocationTypeID LocationTypeName ------------------------------------------------------------- 1 Picking Bin 2 Overstock Bin 3 Packing/Shipping Table 4 Shipped Items 5 Vendor (for reviving)
物理交易 - 当物品移动时。
TransID LocationID Debit Credit Memo ------------------------------------------------------------- 1 5 10 Initial purchase of 10 Red Widgets 2 1 10 Initial purchase of 10 Red Widgets 3 1 2 Pick 2 Widgets AND.... 4 3 2 Move them to the packing table 5 3 2 Ship Widgets to Customers 6 4 2 Customer Gets Widgets
I'm working on a transactional inventory system for our e-commerce company, and I'm looking for some sort of a guide or tutorial on how to accomplish this with a MySQL database. There's guides on how to program, and guides on how to USE such systems, but I've been unable to locate a resource with suggested table structures or best practices for implementing your own.
In this system, all of the items are bought, stored, resold, then shipped (no manufacturing). The system should support multiple "Locations" (ie. Wisconsin, North Carolina). Each Location can have one or more "Warehouses" (buildings next to each other). Each warehouse can have one or more "Isles", with each Isle having one or more "Shelves", and each shelf having one or more "Bins". Merchandise is stored in bins, and each size/color variation has it's own bin. Some items may be stored in more than one bin (ie. if we get a discount by ordering 2000 at a time, we might stock 10 in a low bin and put the rest up on a high shelf in "overstock".
Storing items in their respective locations is simple enough. I would probably have a Bins table like:
BinID BinName LocationID WarehouseID IsleID ShelfID --------------------------------------------------------------------------------- 1 Widget Bin A 1 1 1 1 2 Widget Bin B 1 2 2 5 3 Large Widget Rack 1 1 5 17 4 Widget Overstock 2 3 6 23
And then move items between Bins with an Inventory Transaction Table Like:
TransID SourceBinID DestBinID QTY Date Memo --------------------------------------------------------------------------------- 1 4 1 10 7-22-2011 Moved 10 Widgets...
But here's where I get confused:
Let's say that it's midnight, and a customer orders 2 Widgets from the website. There's 5 widgets in a bin. The customer has paid for his 2 widgets, so no one else can buy them, but it's midnight and no one is working, so his widgets are still sitting in the bin. I need some sort of transaction that decreases the "sellable" number of widgets without decreasing the number actually on the shelf.
The next day, an employee picks those widgets and moves them to the packing area. Now a physical transaction needs to occur to note that the items are now on a packing line and no longer in their bin. But packing needs to be a special location, since it's not the same as a regular "Bin", right? So somehow we need to move things from a bin to a special non-bin location, and we need a transaction entry for doing that.
Then there's other "special" places like a return quarantine, and a place for setting aside items when other items for that order are on backorder.
If you know of a book or online resource that can explain how to do this from a programming/database standpoint, that would be great. Or if someone already knows how to do this and would be willing to share, that would be great too!
Thanks!
UPDATE:
I've been giving this some more thought and it's possible that the "sellable" inventory (I think it's called "on hand") could be calculated dynamically. "Total of all bins" - "unfilled orders" = "on hand". The question is, is that going to be too slow to be practical? It would need to perform several queries and then loop over the results to get the on-hand total.
The alternative might be to have a separate transaction table for "on-hand", but then you have two inventory journals- an "on hand" and a "physical". Even though they should always stay in sync (if not it's a bug!) it still doesn't feel right?
I'm still not sure what to do about physical places. When you ship it, it's gone from inventory, but for double entry style accounting it needs to go somewhere. So I would need some sort of "gone" bin. But it doesn't feel right to make "gone" and "packing table" "bins" either, because they aren't actually bins.
UPDATE 3
Movements:
MoveID TransID SourceBinID DestBinID Memo --------------------------------------------------------------------------------- 1 1 4 1 Moved 10 Widgets to bin 1 2 2 1 4 Received 10 Widgets from bin 4
Issue:
IssueID TransID SourceBinID Memo --------------------------------------------------------------------------------- 1 3 4 Shipped Widget to Customer 2 4 1 Shipped Widget to Customer
Transactions:
TransID ItemID Date QTY Type --------------------------------------------------------------------------------- 1 1 7-22-2011 10 Move 2 1 7-22-2011 -10 Move 3 1 7-23-2011 1 Issue 4 1 7-24-2011 2 Issue
UPDATE 4
Ok, I'm going to take another stab at this, without allocations for now.
Locations Table - A "Location" is a place where stuff can physically “be”.
LocationID LocationTypeID LocationName ------------------------------------------------------------- 1 1 A Widget Bin 2 1 A Widget Bin 3 1 A Widget Bin 4 1 A Widget Bin 5 5 Vendor (nowhere) 6 3 Packing Table 1 7 4 Gone (shipped to customer)
A location has a "type". A location can be a bin, an overstock location, a Packing Table, or represent a customer (for outbound) or a vendor (for inbound).
Location Types
LocationTypeID LocationTypeName ------------------------------------------------------------- 1 Picking Bin 2 Overstock Bin 3 Packing/Shipping Table 4 Shipped Items 5 Vendor (for reviving)
Physical Transactions - When things move around.
TransID LocationID Debit Credit Memo ------------------------------------------------------------- 1 5 10 Initial purchase of 10 Red Widgets 2 1 10 Initial purchase of 10 Red Widgets 3 1 2 Pick 2 Widgets AND.... 4 3 2 Move them to the packing table 5 3 2 Ship Widgets to Customers 6 4 2 Customer Gets Widgets
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
最常用的术语是“分配”。您手头有 5 个小部件，其中 2 个已分配。剩下 3 个“可用”。当这些物品被挑选时，您需要取消该分配。
当您从库存中删除商品（我称之为“发放”）时，您需要提供某种“成本帐户”，以便您确定该价值的去向。
我的另一条建议是使用两个事务进行库存移动。删除一个位置的数量并将其添加到另一位置。通过这样做，您可以将影响现有数量的所有交易保留在一个表中，并且可以非常轻松地动态确定现有数量。
我所做的看起来像这样：
这使我能够在单独的文件中保留有关特定交易类型的更多详细信息表和主事务表中的常见内容。
The term that is most commonly used is "allocated". You have 5 widgets on hand and 2 have been allocated. That leaves 3 "available". When those items get picked you need to cancel that allocation.
When you remove items from inventory (I call that "issuing"), you need to provide some kind of "cost account" that allows you to identify where that value has gone.
The other piece of advice that I would have is to do an inventory movement using two transactions. Remove quantity at one location and add it to another. By doing this you can keep all the transactions that affect on hand quantity in a single table and it makes determining the on hand quantity on the fly pretty easy.
What I do looks like this:
This allows me to keep more detailed information about the particular type of transaction in the separate tables and the common stuff in the main transaction table.