数据库设计：库存和销售系统？
我需要开发一个库存和销售系统。
对于库存，我需要能够跟踪理想库存水平、当前库存水平、再订购点、成本、售价等。
并非库存中的每件商品都是“可销售的”。例如，我可能想保留用于汽水的塑料杯的库存。这意味着，每次我出售一瓶苏打水，我都需要从塑料杯的库存数量中减去一个。因此，“中度可乐”实际上是塑料杯、一些餐巾纸和液体，每种物品都有自己的当前库存水平、成本等。
然后就有了“组合”的概念。也许一瓶 1 美元的中杯可乐和一个 3 美元的汉堡作为组合一起出售只需 3.50 美元（节省 0.50 美元）。据说可乐中包含一些餐巾纸。假设汉堡包本身还包含餐巾纸。然而，作为一个组合，买家不会得到可乐和汉堡的餐巾纸；相反，买家只得到与他/她只购买可乐相同数量的餐巾纸。
对于销售系统，我需要跟踪每笔销售，并可能与库存记录保持关系（这意味着一旦销售完成，我永远无法真正删除库存中的商品 - 出于历史目的）。当我以 1 美元的价格出售“中号可乐”时，也许我应该将其分解为液体 0.90 美元和塑料杯 0.10 美元。
当我出售“组合”时，也许我需要能够指定汉堡实际售价为 3 美元，中号可乐仅为 0.5 美元（只有苏打水打折以使组合更具吸引力）。
这不可能是一个新问题。 有人有任何想法（或示例）可以解决这个问题吗？我不知道如何对库存、可售商品（尤其是组合）进行建模，以及如何记录销售额。
I need to develop a inventory and sales system.
For inventory, I need to be able to keep track of ideal stock levels, current stock levels, reorder point, cost, selling price, etc.
Not every item in the inventory is "sellable." For example, I may want to keep inventory of plastic cups used for sodas. Meaning, each time I sell a soda, I need to subtract one from the plastic cup's inventory count. Thus, a "medium coke" is actually the plastic cup, some napkins and the fluid, each item having its own current stock levels, cost, etc.
Then there is the concept of "combos." Perhaps a $1 medium Coke and a $3 hamburger are sold together as a combo for just $3.50 (a $0.50 savings). The Coke was mentioned to include some napkins. Say the hamburger also includes napkins on its own. However, as a combo, the buyer does not get the napkin for the Coke and the hamburger; rather the buyer only gets the same amount of napkins as if he/she were buying just the Coke.
For the sales system, I need to keep track of every sale and possibly maintain a relationship with the inventory records (this would mean that I could never really delete an item in the inventory once a sale is made -- for historical purposes). When I sell a "medium Coke" for $1, perhaps I should break it down as $0.90 for the fluid and $0.10 for the plastic cup.
And when I sell a "combo", maybe I need to be able to specify that the hamburger actually sold for $3 and the medium Coke was just $0.50 (only the soda was discounted to make the combo more appealing).
This can't be a new problem. Does anyone have any ideas (or examples) I can look at to solve this problem? I'm not sure how to model inventory, the sellable items (especially the combos), and how to record the sales.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您正在寻找的解决方案将依赖于会计风格模型和一些物料清单 (BOM)。您的主要实体类型将包括：
SKU 这是您销售的商品列表。它的属性将包括产品描述和当前零售价格等内容。您可以花点心思，将价格分解到一个子表中，该子表提供随时间变化的价格。让我们假设您现在要忽略这个问题。有些 SKU 可以是您所讨论的那种“组合”。
组件 这是构成 SKU 的物品列表，例如餐巾、杯子、小圆面包、馅饼、可乐糖浆等 - 以您为例。正如 SKU 有描述和价格一样，组件也有描述和单位成本。 （也可以在子表中进行历史记录。）此表通常也是您存储 ROP 的位置。
组合 这是一个 BOM，它与 SKU 和组件相交，表示每个组件有多少个单元进入一个 SKU 单元。您还需要其中之一来使两个 SKU 相交（对于组合）。为此，您可以使用一张表或两张表。两张表会让纯粹主义者满意，一张表从编码员的角度来看会很方便。
SALE 这是一个交易表，提供用于记录一个或多个 SKU 销售的标头。该表包含交易日期、收银员 ID 和其他标头项目等内容。
SALE_ITEM 这是交易详细信息表，其中包括售出的 SKU（以及数量）和售价。价格是销售时 SKU 价格的非规范化，但也可能包括对价格的任何特殊覆盖。对 SKU 实际收取的价格进行非规范化是一件好事，因为有人可以编辑 SKU 中的标价，然后您就无法跟踪该商品当时实际收取的费用。
INVENTORY_HDR 这是一个事务表，概念上与 SALE 类似，但它是库存事务的标头，例如接收新库存、用完库存（如出售库存）和用于库存调整。同样，这将是日期/描述内容，但如果您愿意，它可以包含指向 SALE_ITEM 的直接链接，用于库存变动（即销售）。您不必这样做，但有些人喜欢在逐笔交易的基础上建立收入和成本之间的联系。
INVENTORY_DTL这是库存交易的详细信息。这表明哪个 COMPONENT 正在输入或输出、输入或输出的数量以及此移动应用到的 INVENTORY_HDR 事务。您还可以在此处保存组件项目的实际支付成本。
位置您还可以（如果您愿意）跟踪您接收和使用/销售的库存的实际位置。在餐厅中，这可能并不重要，但如果您有一家连锁店，或者您的餐厅有一个存放成分的异地仓库，那么您可能会关心。
考虑以下 ERD：
要进行收入核算，您需要将 SALE_ITEM 表中记录的金额相加。
库存水平是根据每个组件的 INVENTORY_DTL 细节相加来计算的。 （不要将当前库存水平存储在表中 - 这注定会导致调节问题。）
要进行成本核算，您需要将 INVENTORY_DTL 表中记录的金额相加。请注意，您通常无法确切知道您销售的是哪种餐巾或面包，因此无法将特定组件收据与特定 SKU 销售链接起来。相反，您需要制定一个约定来确定哪些组件用于任何给定的 SKU。您可能有指定您需要使用的约定的会计规则。大多数人使用先进先出 (FIFO)。有些行业使用后进先出法，我什至见过加权平均成本核算。
The solution you are looking for will rely on an accounting style model and a couple of bills of materials (BOM). Your major entity types will include:
SKU This is the list of things that you sell. It's properties will include things like product description and current retail price. You can get fancy and break price out into a child table that gives prices over time. Let's assume that you are going to leave that wrinkle out for now. Some SKUs can be "combos" of the sort you are talking about.
COMPONENT This is the list of things that make up a SKU, such as napkins, cups, buns, patties, coke syrup etc. - to use your example. Just as SKU has descriptions and prices, COMPONENTs have descriptions and unit costs. (Which can also be historized in a child table.) This table is where you would typically store your ROP too.
COMPOSITION This is a BOM which intersects SKU and COMPONENT and says how many units of each COMPONENT go into a unit of a SKU. You need one of these to intersect two SKUs too (for combos). You can either use one table or two tables for this. Two tables will keep the purists happy, one table will be expedient from a coder point of view.
SALE This is a transaction table that provides a header for recording a sale of one or more SKUs. This table would have things like transaction date, cashier ID, and other header items.
SALE_ITEM This is the transaction detail table that would include which SKU was sold (and how many) and for how much. The how much is a denormalization of the SKU price at time of sale, but could also include any special overrides to the price. The price actually charged for the SKU is a good thing to denormalize because someone could edit the list price in SKU and then you'd lose track of how much was actually charged for the item at the time.
INVENTORY_HDR This is a transactional table that is similar to the SALE conceptually, but it is the header for an inventory transaction, such as receiving new inventory, using up inventory (as in selling it) and for inventory adjustments. Again, this would be date/description stuff, but it can include a direct link to a SALE_ITEM for inventory movements that are sales if you like. You don't have to do it that way, but some people like to establish the connection between revenues and costs on a transaction by transaction basis.
INVENTORY_DTL This is the detail for an inventory transaction. This indicates which COMPONENT is going in or out, the quantity that went in or out, and the INVENTORY_HDR transaction that this movement applied to. This would also be where you keep the actual cost paid for the component item.
LOCATION You can (if you wish) also track the physical location of the inventory that you receive and use/sell. In a restaurant this may not be important but if you have a chain or if your restaurant has an offsite warehouse for component ingredients then you might care.
Consider the following ERD:
To do your revenue accounting you would be adding up the money recorded in the SALE_ITEM table.
Stock levels are calculated based on adding up the INVENTORY_DTL ins and outs for each COMPONENT. (Don't store current stock levels in a table - This is doomed to cause reconciliation problems.)
To do your cost accounting you would be adding up the money recorded in the INVENTORY_DTL table. Note that you won't usually know exactly which napkin or bun you sold, so it won't be possible to link specific component reciepts with specific SKU sales. Instead, you need to have a convention for determining which components were used for any given SKU. You may have accounting rules that specify what convention you are required to use. Most people use FIFO. Some industries use LIFO and I've even seen weighted average cost accounting.
我建议将库存表与货币核算表完全分开。例如，我知道你给出的例子很荒谬：对于普通的快餐店来说，一杯 0.90 美分的可乐成本约为 0.05-0.07 美元，一杯可乐的成本约为 0.05-0.07 美元，液体的成本不到 1 便士，留下了整洁的空间。利润为 0.83 美元左右。为什么成本加起来必须达到 0.90 美元？
表：
InventoryItems字段：InventoryItemId、Name、CurrentInventoryLevel、IdealInventoryLevelInventoryChangeRecords字段：InventoryChangeId、InventoryItemId、Change (int)RetailItems字段：RetailItemId、Name 、价格RetailItemMakeup字段：RetailItemId、InventoryItemId、数量SaleTransactions字段：SaleTransactionId、DateTime、TotalSaleSaleTransactionItems字段：SaleTransactionId、RetailItemId、Quantity对于每次销售，您应该使用存储过程（或触发器）来更新 CurrentInventoryLevel，并插入记录进入 InventoryChangeRecords。通过从 SaleTransaction 联接到 SaleTransactionItems 到 RetailItemMakeup，您可以轻松弄清楚任何销售如何影响库存。
如果您想以更强大（但 IMO 无关）的方式执行此操作，您可以在 SaleTransactionItems 和 InventoryChangeRecords 之间创建另一个多对多表。
I would suggest completely separating the inventory tables from the money accounting tables. For example, the example you gave I know to be ridiculous: For your average fast food restaurant a $.90 cent Coke costs about $.05-$.07 for the cup, and less than a penny for the liquid, leaving a tidy profit of $.83ish. Why do the costs have to add up to $.90?
Tables:
InventoryItemsfields: InventoryItemId, Name, CurrentInventoryLevel, IdealInventoryLevelInventoryChangeRecordsfields: InventoryChangeId, InventoryItemId, Change (int)RetailItemsfields: RetailItemId, Name, PriceRetailItemMakeupfields: RetailItemId, InventoryItemId, QuantitySaleTransactionsfields: SaleTransactionId, DateTime, TotalSaleSaleTransactionItemsfields: SaleTransactionId, RetailItemId, QuantityFor each sale, you should use a sproc (or trigger) to update CurrentInventoryLevel, and insert records into InventoryChangeRecords. You can easily figure out how any sale impacted inventory by joining from SaleTransaction to SaleTransactionItems to RetailItemMakeup.
If you wanted to do it in an even stronger (but IMO extraneous) manner, you could create another many-to-many table between SaleTransactionItems and InventoryChangeRecords.