有趣的数据库架构场景
我目前正在开发一个自定义订单处理系统。我当前的结构非常标准，发票、采购订单和项目都保存在单独的表中。项目通过跟踪表单的 id 知道它们位于哪个表单上，但表单不知道其中（在数据库中）有哪些项目。这一切都很好，直到我添加了一个新的要求：库存订单。
库存订单的工作方式是，有时客户下的订单数量多于库存数量，因此我们希望向供应商下订单，购买足够的数量来履行订单并补充库存。然而，我们经常必须建立这些订单，因为最低订单非常高，因此一个库存订单通常由多个客户订单（有时是同一商品）以及一些与订单无关且仅包含在订单中的商品组成。用于库存目的。
这给我当前的架构带来了一个问题，因为我现在需要跟踪库存订单中的内容，因为供应商通常会运送部分订单、已分配的物品以及哪些传入物品用于库存。
我最初的想法是创建一个新的数据库表，它主要模仿项目表，但有点像一个聚合（但不是计算）表，它只跟踪项目及其相应的元数据（收到了多少单位，有多少单位）库存等）仅适用于库存订单。如果其中一个表发生变化（例如数量），我必须保持两个表同步。
这是否太过分了，也许有更好的方法来做到这一点？数据库架构绝对不是我的强项，所以我希望有人可以告诉我这是一种不错的做事方式，或者有更好、更正确的方法来做到这一点。
非常感谢！
对于它的价值，我正在使用什么：VB、.NET 4.0、MySQL 5.0
另外，如果您想澄清任何事情，请询问！我将密切关注这个问题。
I am currently in the process of rolling a custom order-processing system. My current structure is pretty standard, invoices, purchase orders, and items are all kept in separate tables. Items know which form(s) they are on by keeping track of the form's id, but forms don't know what items are in them (in the database). This was all well and good until I had a new requirement added to the mix: stocking orders.
The way a stocking order works is that sometimes a customer places an order for more units than what is in stock, so we want to place an order with our supplier for enough units to fulfill the order and replenish our stock. However, we often have to build these orders up as the minimums are pretty high, so one stocking order is usually comprised of several customer orders (sometimes for the same item) PLUS a few line items that are NOT connected to an order and are just for stocking purposes.
This presents a problem with my current architecture because I now need to keep track of what comes in from the stocking orders as often suppliers ship partial orders, where items have been allocated, and which incoming items are for stock.
My initial idea was to create a new database table that mostly mimics the items table, but is kind of like an aggregate (but not calculated) table that only keeps track of the items and their corresponding metadata (how many units received, how many for stock, etc) for only the stocking orders. I would have to keep the two tables in synch if something changed from one of them (like a quantity).
Is this overkill, and maybe there's a better way to do it? Database architecture is definitely not my forte, so I was hoping that someone could either tell me that this is an ok way to do things or that there is a better, more correct way to do it.
Thanks so much!
For what it's worth, what I'm using: VB, .NET 4.0, MySQL 5.0
Also, if you want clarification on anything, please ask! I will be closely monitoring this question.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
访问databaseanswers.com。导航到“免费数据模型”，然后查找“库存管理”。你应该找到一些很好的例子。
Visit databaseanswers.com. Navigate to "free data models", and look for "Inventory Management". You should find some good examples.
您没有提到它们，但您需要以下表格：
供应商、订单和库存
，以及您提到的“了解”的基表 - 这些可能需要关联样式的多对多表格，告诉您哪些项目在哪个订单上，以及哪些供应商供应哪些项目、交货时间、成本等信息等等，
查看您的实际架构会很有帮助。
you didnt mention them but you will need tables for:
SUPPLIERS, ORDERS, and INVENTORY
also, the base tables you mention 'knowing about' - these probably need associative style many to many tables which tell you things like which items are on which order, and which suppliers supply which items, lead times, costs etc.
it would be helpful to see your actual schema.
我使用一个带有 DocType 字段的文档表。客户文件（订单、发票、形式、交货、贷项票据）与供应商文件（采购订单、接待）混合在一起。
这使得计算客户延期交货、供应商延期交货等变得非常容易......
我只是有点不高兴，因为我有不同的 SUPPLIERS 和 CLIENTS 表，因此 DOCUMENTS 表同时具有 SupplyId 字段和 ClientId 字段，这有点糟糕。如果我必须重做，我可能会考虑使用包含客户和供应商的单个公司表。
我使用自动增量的 PK (DocId) 和类似于 XYY00000 的唯一键 (DocNum)，其中
x= 文档类型
YY=年份
00000 = 增量。
这是因为文档可以保存，但仅在验证时才会收到 DocNum。
要跟踪延期交货（供应商或客户），您需要在 DocDetails 表中有一个分组字段，这样，如果您有订单行 12345，则可以将该链接字段复制到与其相关的每个详细信息行（发票、交货）。
希望我不会感到困惑。经过 3 年和超过 50,000 份文档，该产品运行良好。
I use a single Documents table, with a DocType field. Client documents (Order, Invoice, ProForma, Delivery, Credit Notes) are mixed with Suppliers documents (PO, Reception).
This makes it quite easy to calculate Client backorders, Supplier backorders, etc...
I am just a bit unhappy because I have different tables for SUPPLIERS and CLIENTS, and therefore the DOCUMENTS table has both a SupplierId field and a ClientId field, which is a bit bad. If I had to redo it I might consider a single Companies table containing both Clients and Suppliers.
I use a PK (DocId) that's autoincrement, and a unique key (DocNum) that's like XYY00000, with
x= doc type
YY= year
00000 = increment.
This is because a doc can be saved but is only at validation time it receives a DocNum.
To track backorders (Supplier or Client), you will need to have a Grouping field in the DocDetails table, so that if you have an Order line 12345, you copy that Link field to every Detail line related to it (invoice, Delivery).
Hope I am not to confusing. The thing works well, after 3 years and over 50,000 docs.
这种方法还意味着您将拥有分配给订单的库存 - 如果没有单个项目跟踪，管理起来有点棘手。考虑一下，客户 A 下订单
，但您的库存只有 1 个粉色小部件 - 您订购了 3 个粉色小部件和 1 个蓝色部件。
客户 B 订单
但您仍然只有 1 个库存 - 您订购了另一个粉红色小部件
第一个供应商订单中收到了 3 个粉红色小部件。你会怎样做？您可以为客户 A 的订单保留所有这些小部件并等待蓝色和红色小部件到达，也可以履行客户 B 的订单。
如果粉色小部件的交货时间为 3 天，蓝色小部件的交货时间为 3 周，该怎么办？您是否向客户运送部分订单？您持有的股票数量有限制吗？
仅保留新的缺货订单表是不够的。
这东西很快就会变得可怕而复杂。你当然需要花更多的时间来分析问题。
This approach also implies that you will have a stock holding which is allocated for orders - without individual item tracking its a bit tricky to manage this. Consider, customer A orders
But you only have 1 pink widget in stock - you order 3 pink widgets, and 1 blue.
Customer B orders
But you've still only got 1 in stock - you order another pink widget
3 pink widgets arrive from the first supplier order. What are you going to do? You can either reserve all of them for customer A's order and wait for the blue and red widget to arrive, or you can fulfill customer B's order.
What if the lead time on a pink widget is 3 days and for a blue widget it's 3 weeks? Do you ship partial orders to your customers? Do you have a limit on the amount of stock you will hold?
Just keeping a new table of backorders is not going to suffice.
This stuff gets scary complicated really quickly. You certainly need to spend a lot more time analysing the problem.