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.
然后是“组合”的概念。也许一个1美元的中杯可乐和一个3美元的汉堡组合在一起只卖3.5美元(节省0.5美元)。有人提到可乐里有一些餐巾纸。说汉堡包也有自己的餐巾纸。然而，作为一种组合，顾客不会得到可乐和汉堡的餐巾;相反，买家只买了和买可乐一样的餐巾纸。
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.
对于销售系统，我需要跟踪每一笔销售，并可能与库存记录保持关系(这意味着一旦销售完成，我永远无法真正删除库存中的一个项目——出于历史目的)。当我以1美元的价格出售“中杯可乐”时，或许我应该把它分成0.90美元的液体和0.10美元的塑料杯。
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).
当我卖“套餐”的时候，也许我需要明确的是，汉堡的实际售价是3美元，中杯可乐的价格是0.5美元(只有汽水打折了，让套餐更有吸引力)。
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.
这不可能是一个新的问题。有人有什么想法(或例子)我可以看解决这个问题吗?我不知道如何建模库存，可销售的项目(特别是组合)，以及如何记录销售。
2 个解决方案
#1
20
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:
您正在寻找的解决方案将依赖于会计风格模型和一些材料清单(BOM)。您的主要实体类型将包括:
-
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.
这是你要卖的东西的清单。它的属性包括产品描述和当前零售价格。你可以把价格想象成一个孩子的桌子，随着时间的推移，它会给你价格。让我们假设你现在将把这条皱纹去掉。有些sku可能是您正在谈论的那种“组合”。
-
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.
这是构成SKU的列表，比如餐巾、杯子、小圆面包、小馅饼、可乐糖浆等等。正如SKU有描述和价格一样，组件也有描述和单位成本。(在儿童桌上也可以编历史。)这个表是您通常存储您的ROP的地方。
-
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.
这是一个BOM，它与SKU和分量相交，表示每个分量有多少个单位组成一个SKU。你需要其中的一个与两个sku相交(对于组合)。你可以用一张桌子或两张桌子。两张表会让纯粹主义者满意，一张表从编码者的角度来说是权宜之计。
-
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.
这是一个事务表，为记录一个或多个sku的销售提供了一个头。该表将包含诸如事务日期、出纳ID和其他标题项等内容。
-
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.
SALE_ITEM这是一个事务细节表，它将包含已出售的SKU(以及出售的SKU数量)和出售的SKU数量。在出售时，SKU价格的反规格化程度是多少，但也可能包括价格上的任何特殊覆盖。实际上，SKU的价格是一件好事，因为有人可以在SKU中编辑列表价格，然后你就会忘记该项目在当时实际收取多少费用。
-
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_HDR这是一个事务表，在概念上与销售类似，但它是一个库存事务的头，例如接收新的库存、耗尽库存(如销售库存)和库存调整。同样，这将是日期/描述的内容，但是它可以包含到SALE_ITEM的直接链接，用于库存移动，如果您喜欢的话，这是销售。你不需要那样做，但是有些人喜欢在交易基础上建立收入和成本之间的联系。
-
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.
这是库存事务的详细信息。这指示了哪个组件要输入或输出，输入或输出的数量，以及该移动应用到的INVENTORY_HDR事务。这也是您为组件项目保留实际成本的地方。
-
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: 
考虑下面的ERD:
To do your revenue accounting you would be adding up the money recorded in the SALE_ITEM table.
要做收入核算，你需要把在SALE_ITEM表中记录的钱加起来。
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.)
库存水平是根据每个组件的INVENTORY_DTL的加入和退出来计算的。(不要将当前库存水平存储在一个表中——这注定会导致和解问题。)
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.
要进行成本核算，您需要将INVENTORY_DTL表中记录的钱相加。请注意，您通常不会确切地知道您出售的是哪种餐巾或小圆面包，因此不可能将特定的组件收据与特定的SKU销售联系起来。相反，您需要有一个约定来确定为任何给定SKU使用了哪些组件。您可能有会计规则来指定需要使用的约定。大多数人使用FIFO。有些行业使用LIFO，我甚至见过加权平均成本核算。
#2
0
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?
我建议将库存表与货币会计表完全分开。例如，你给我的例子我知道是荒谬的:你的快餐店平均只卖一美元。90%的可口可乐售价约为0.05至0.50美元。07美元买杯子，不到一分钱买液体，剩下的利润约为0.83美元。为什么这些费用要加起来是0.90美元?
Tables:
表:
InventoryItems fields: InventoryItemId, Name, CurrentInventoryLevel, IdealInventoryLevel
InventoryItems字段:InventoryItemId, Name, CurrentInventoryLevel, IdealInventoryLevel
InventoryChangeRecords fields: InventoryChangeId, InventoryItemId, Change (int)
InventoryChangeId, InventoryItemId, Change (int)
RetailItems fields: RetailItemId, Name, Price
RetailItems字段:RetailItemId, Name, Price
RetailItemMakeup fields: RetailItemId, InventoryItemId, Quantity
RetailItemMakeup field: RetailItemId, InventoryItemId, Quantity
SaleTransactions fields: SaleTransactionId, DateTime, TotalSale
SaleTransactions字段:SaleTransactionId, DateTime, TotalSale。
SaleTransactionItems fields: SaleTransactionId, RetailItemId, Quantity
SaleTransactionItems字段:SaleTransactionId, RetailItemId, Quantity
For 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.
对于每次销售，您应该使用sproc(或触发器)更新当前库存级别，并将记录插入InventoryChangeRecords。通过从销售交易到销售交易项目再到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.
如果您想以一种更强的(但在我看来是无关的)方式进行，您可以在SaleTransactionItems和InventoryChangeRecords之间创建另一个多对多表。
#1
20
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:
您正在寻找的解决方案将依赖于会计风格模型和一些材料清单(BOM)。您的主要实体类型将包括:
-
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.
这是你要卖的东西的清单。它的属性包括产品描述和当前零售价格。你可以把价格想象成一个孩子的桌子，随着时间的推移，它会给你价格。让我们假设你现在将把这条皱纹去掉。有些sku可能是您正在谈论的那种“组合”。
-
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.
这是构成SKU的列表，比如餐巾、杯子、小圆面包、小馅饼、可乐糖浆等等。正如SKU有描述和价格一样，组件也有描述和单位成本。(在儿童桌上也可以编历史。)这个表是您通常存储您的ROP的地方。
-
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.
这是一个BOM，它与SKU和分量相交，表示每个分量有多少个单位组成一个SKU。你需要其中的一个与两个sku相交(对于组合)。你可以用一张桌子或两张桌子。两张表会让纯粹主义者满意，一张表从编码者的角度来说是权宜之计。
-
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.
这是一个事务表，为记录一个或多个sku的销售提供了一个头。该表将包含诸如事务日期、出纳ID和其他标题项等内容。
-
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.
SALE_ITEM这是一个事务细节表，它将包含已出售的SKU(以及出售的SKU数量)和出售的SKU数量。在出售时，SKU价格的反规格化程度是多少，但也可能包括价格上的任何特殊覆盖。实际上，SKU的价格是一件好事，因为有人可以在SKU中编辑列表价格，然后你就会忘记该项目在当时实际收取多少费用。
-
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_HDR这是一个事务表，在概念上与销售类似，但它是一个库存事务的头，例如接收新的库存、耗尽库存(如销售库存)和库存调整。同样，这将是日期/描述的内容，但是它可以包含到SALE_ITEM的直接链接，用于库存移动，如果您喜欢的话，这是销售。你不需要那样做，但是有些人喜欢在交易基础上建立收入和成本之间的联系。
-
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.
这是库存事务的详细信息。这指示了哪个组件要输入或输出，输入或输出的数量，以及该移动应用到的INVENTORY_HDR事务。这也是您为组件项目保留实际成本的地方。
-
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:
考虑下面的ERD:
To do your revenue accounting you would be adding up the money recorded in the SALE_ITEM table.
要做收入核算，你需要把在SALE_ITEM表中记录的钱加起来。
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.)
库存水平是根据每个组件的INVENTORY_DTL的加入和退出来计算的。(不要将当前库存水平存储在一个表中——这注定会导致和解问题。)
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.
要进行成本核算，您需要将INVENTORY_DTL表中记录的钱相加。请注意，您通常不会确切地知道您出售的是哪种餐巾或小圆面包，因此不可能将特定的组件收据与特定的SKU销售联系起来。相反，您需要有一个约定来确定为任何给定SKU使用了哪些组件。您可能有会计规则来指定需要使用的约定。大多数人使用FIFO。有些行业使用LIFO，我甚至见过加权平均成本核算。
#2
0
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?
我建议将库存表与货币会计表完全分开。例如，你给我的例子我知道是荒谬的:你的快餐店平均只卖一美元。90%的可口可乐售价约为0.05至0.50美元。07美元买杯子，不到一分钱买液体，剩下的利润约为0.83美元。为什么这些费用要加起来是0.90美元?
Tables:
表:
InventoryItems fields: InventoryItemId, Name, CurrentInventoryLevel, IdealInventoryLevel
InventoryItems字段:InventoryItemId, Name, CurrentInventoryLevel, IdealInventoryLevel
InventoryChangeRecords fields: InventoryChangeId, InventoryItemId, Change (int)
InventoryChangeId, InventoryItemId, Change (int)
RetailItems fields: RetailItemId, Name, Price
RetailItems字段:RetailItemId, Name, Price
RetailItemMakeup fields: RetailItemId, InventoryItemId, Quantity
RetailItemMakeup field: RetailItemId, InventoryItemId, Quantity
SaleTransactions fields: SaleTransactionId, DateTime, TotalSale
SaleTransactions字段:SaleTransactionId, DateTime, TotalSale。
SaleTransactionItems fields: SaleTransactionId, RetailItemId, Quantity
SaleTransactionItems字段:SaleTransactionId, RetailItemId, Quantity
For 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.
对于每次销售，您应该使用sproc(或触发器)更新当前库存级别，并将记录插入InventoryChangeRecords。通过从销售交易到销售交易项目再到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.
如果您想以一种更强的(但在我看来是无关的)方式进行，您可以在SaleTransactionItems和InventoryChangeRecords之间创建另一个多对多表。