数据库设计：库存和销售系统？ [英] Database Design: inventory and sales system?

问题描述

我需要开发库存和销售系统。

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.

组合的概念。也许一个$ 1中等的可乐和一个$ 3的汉堡包一起销售作为一个组合只需$ 3.50（一个$ 0.50的储蓄）。提到可乐包括一些餐巾。说汉堡包也包括餐巾自己。然而，作为一个组合，买家没有拿到可乐和汉堡的餐巾;而是买家只得到相同数量的餐巾，就像他/她只购买可乐。

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美元销售中型可乐时，或许我应该把它分解为流体的0.90美元和塑料杯的0.10美元。

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.

当我卖组合，也许我需要能够指定汉堡包实际售出3美元，中等可乐只是0.50美元（只有苏打折扣，使组合更有吸引力）。

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.

推荐答案

您正在寻找的解决方案将依赖于会计风格模型和几张材料清单（BOM）。您的主要实体类型包括：

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 这是您销售的物品清单。它的属性将包括产品描述和当前零售价格。你可以得到花哨和打破价格一个儿童表，提供价格随着时间的推移。让我们假设你现在要离开这个皱纹了。

• 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 这是组成的东西的列表SKU，如餐巾，杯子，馒头，肉饼，焦糖浆等 - 使用您的例子。正如SKU有描述和价格，COMPONENT有描述和单位成本。

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 这是一个与SKU和COMPONENT相交的物料清单，并说明每个组分的多少单位进入SKU的单位。你需要其中一个与两个SKU相交（对于组合）。您可以使用一个表或两个表。

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.

销售这是一笔交易表，其提供用于记录一个或多个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.

SALE_ITEM 这是交易详情表，其中包含交易日期，收银员ID和其他标题项目。将包括哪些SKU被出售（以及多少）以及多少。销售时SKU价格的非正规化有多少，但也可以包括对价格的任何特殊替代。实际收取SKU的价格是反正规化的好办法，因为有人可以编辑SKU中的定价，然后你会忘记当时该项目的实际收费。

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 这是一个事务表，与概念上的SALE类似，但它是库存交易的标题，例如接收新库存， （如销售）和库存调整。再次，这将是日期/描述的东西，但它可以包括直接链接到SALE_ITEM的库存移动，如果你喜欢的销售。你不必这样做，但有些人喜欢在交易基础上建立收入和成本之间的联系。

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 这是库存交易的明细。这表示哪个COMPONENT进入或退出，进入或退出的数量以及此移动应用的INVENTORY_HDR事务。

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 您可以（如果愿意）也可以跟踪您收到和使用/销售的库存的物理位置。在餐厅，这可能不重要，但如果你有一个链，或者如果你的餐厅有一个异地仓库的组件成分，那么你可能会关心。

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.

请考虑以下ERD：

Consider the following ERD:

要进行收入会计，您需要将SALE_ITEM表中记录的金额相加。

To do your revenue accounting you would be adding up the money recorded in the SALE_ITEM table.

，基于为每个COMPONENT添加INVENTORY_DTL个插入和输出。 （不要将当前库存水平存储在表格中 - 这注定会导致对账问题。）

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表。请注意，您通常不会准确知道您售出的餐巾纸或小圆面包，因此无法将特定的零件库存与特定的SKU销售相关联。相反，您需要有一个惯例来确定哪些组件用于任何给定的SKU。您可能具有指定您需要使用的约定的会计规则。大多数人使用FIFO。一些行业使用LIFO，我甚至看到加权平均成本会计。

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.

这篇关于数据库设计：库存和销售系统？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！