Ruby-on-rails 销售点和库存数据库架构

Point of Sale and Inventory database schema

提问by Andrés Botero

I'm trying to create a basicPoint of Sale and Inventory management system.

我正在尝试创建一个基本的销售点和库存管理系统。

Some things to take into account:

需要考虑的一些事项：

• The products are always the same (same ID) through the whole system, but inventory (available units for sale per product) is unique per location. Location Y and Z may both have for sale units of product X, but if, for example, two units are sold from location Y, location Z's inventory should not be affected. Itsstocked units are still intact.
• Selling one (1) unit of product X from location Y, means inventory of location Y should subtract one unit from its inventory.

• 整个系统中的产品始终相同（相同 ID），但每个位置的库存（每个产品的可销售单位）是唯一的。地点 Y 和 Z 可能都有产品 X 的待售单位，但如果，例如，地点 Y 出售两个单位，地点 Z 的库存不应受到影响。它的库存单位仍然完好无损。
• 从位置 Y 销售一 (1) 单位产品 X，意味着位置 Y 的库存应从其库存中减去一单位。

From that, I thought of these tables:

由此，我想到了这些表：

• locations

  • id
  • name

• products

  • id
  • name

• transactions

  • id
  • description

• inventories_header

  • id
  • location_id
  • product_id

• inventories_detail

  • inventories_id
  • transaction_id
  • unit_cost
  • unit_price
  • quantity

• orders_header

  • id
  • date
  • total (calculated from orders_detail quantity * price; just for future data validation)

• orders_detail

  • order_id
  • transaction_id
  • product_id
  • quantity
  • price

• 地点

  • ID
  • 姓名

• 产品

  • ID
  • 姓名

• 交易

  • ID
  • 描述

• 库存_标题

  • ID
  • location_id
  • 产品编号

• 库存_详细信息

  • 库存_id
  • 交易编号
  • 单位成本
  • 单价
  • 数量

• 订单标题

  • ID
  • 日期
  • 总计（从 orders_detail 数量 * 价格计算；仅用于未来数据验证）

• 订单详情

  • 订单编号
  • 交易编号
  • 产品编号
  • 数量
  • 价钱

Okay, so, are there any questions? Of course.

好的，那么，有什么问题吗？当然。

1. How do I keep track of changes in units cost? If some day I start paying more for a certain product, I would need to keep track of the marginal utility ((cost*quantity) - (price*quantity) = marginal utility) some way. I thought of inventories_detail mostly for this. I wouldn't have cared otherwise.
2. Are relationships well stablished? I still have a hard time thinking if the locations have inventories, or if inventories have several locations. It's maddening.
3. How would you keep/know your current stock levels? Since I had to separate the inventory table to keep up with cost updates, I guess I would just have to add up all the quantities stated in inventories_detail.
4. Any suggestions do you want to share?

1. 如何跟踪单位成本的变化？如果有一天我开始为某种产品支付更多费用，我需要以(cost*quantity) - (price*quantity) = marginal utility某种方式跟踪边际效用 ( )。我主要为此想到了inventory_detail。否则我不会在意的。
2. 人际关系是否稳定？我仍然很难考虑这些位置是否有库存，或者库存是否有多个位置。令人抓狂。
3. 您将如何保持/了解您当前的库存水平？由于我必须分开库存表以跟上成本更新，我想我只需要将inventory_detail 中规定的所有数量相加。
4. 你有什么建议想分享吗？

I'm sure I still have some questions, but these are mostly the ones I need addressing. Also, since I'm using Ruby on Rails for the first time, actually, as a learning experience, it's a shame to be stopped at design, not letting me punch through implementation quicker, but I guess that's the way it should be.

我确定我仍然有一些问题，但这些大多是我需要解决的问题。此外，由于我是第一次使用 Ruby on Rails，实际上，作为一种学习经验，在设计上停下来是一种耻辱，没有让我更快地完成实现，但我想这应该是这样的。

Thanks in advance.

提前致谢。

回答by Brian Glick

The tricky part here is that you're really doing more than a POS solution. You're also doing an inventory management & basic cost accounting system.

这里棘手的部分是，您实际上所做的不仅仅是 POS 解决方案。你也在做一个库存管理和基本成本会计系统。

The first scenario you need to address is what accounting method you'll use to determine the cost of any item sold. The most common options would be FIFO, LIFO, or Specific Identification (all terms that can be Googled).

您需要解决的第一个场景是您将使用哪种会计方法来确定任何已售商品的成本。最常见的选项是 FIFO、LIFO 或特定标识（所有可以通过 Google 搜索的术语）。

In all 3 scenarios, you should record your purchases of your goods in a data structure (typically called PurchaseOrder, but in this case I'll call it SourcingOrder to differentiate from your orders tables in the original question).

在所有 3 种情况下，您都应该在数据结构中记录您购买的商品（通常称为 PurchaseOrder，但在这种情况下，我将其称为 SourcingOrder，以区别于原始问题中的订单表）。

The structure below assumes that each sourcing order line will be for one location (otherwise things get even more complex). In other words, if I buy 2 widgets for store A and 2 for store B, I'd add 2 lines to the order with quantity 2 for each, not one line with quantity 4.

下面的结构假设每个采购订单行都针对一个位置（否则事情会变得更加复杂）。换句话说，如果我为商店 A 购买 2 个小部件，为商店 B 购买 2 个小部件，我会在订单中添加 2 行，每行数量为 2，而不是一行数量为 4。

SourcingOrder
 - order_number
 - order_date

SourcingOrderLine
 - product_id
 - unit_cost
 - quantity
 - location_id

Inventory can be one level...

库存可以是一级...

InventoryTransaction
 - product_id
 - quantity
 - sourcing_order_line_id
 - order_line_id
 - location_id
 - source_inventory_transaction_id

Each time a SourcingOrderLine is received at a store, you'll create an InventoryTransaction with a positive quantity and FK references to the sourcing_order_line_id, product_idand location_id.

每次在商店收到 SourcingOrderLine 时，您将创建一个 InventoryTransaction，其中包含正数量和对sourcing_order_line_id,product_id和 的FK 引用location_id。

Each time a sale is made, you'll create an InventoryTransaction with a negative quantity and FK references to the order_line_id, product_idand location_id, source_inventory_transaction_id.

每次进行销售时，您将创建一个 InventoryTransaction，其中包含负数量和对order_line_id,product_id和location_id, 的FK 引用source_inventory_transaction_id。

The source_inventory_transaction_idwould be a link from the negative quantity InventoryTransaction back to the postiive quantity InventoryTransaction calculated using whichever accounting method you choose.

这source_inventory_transaction_id将是从负数量 InventoryTransaction 返回到使用您选择的任何会计方法计算的正数量 InventoryTransaction 的链接。

Current inventory for a location would be SELECT sum(quantity) FROM inventory_transactions WHERE product_id = ? and location_id = ? GROUP BY product_id, location_id.

某个位置的当前库存为SELECT sum(quantity) FROM inventory_transactions WHERE product_id = ? and location_id = ? GROUP BY product_id, location_id。

Marginal cost would be calculated by tracing back from the sale, through the 2 related inventory transactions to the SourcingOrder line.

边际成本将通过从销售追溯至 SourcingOrder 行的 2 个相关库存交易来计算。

NOTE: You have to handle the case where you allocate one order line across 2 inventory transactions because the ordered quantity was larger that what was left in the next inventory transaction to be allocated. This data structure will handle this, but you'll need to work the logic and query yourself.

注意：您必须处理跨 2 个库存交易分配一个订单行的情况，因为订购的数量大于要分配的下一个库存交易的剩余数量。此数据结构将处理此问题，但您需要自己处理逻辑和查询。

回答by koch

Brian is correct. Just to add additional info. If you are working into a complete system for your business or client. I would suggest that you start working on the organizational level down to process of POS and accounting. That would make your database experience more extensive... :P In my experience in system development, Inventory modules always start with the stock taking+(purchases-purchase returns)=SKU available for sales. POS is not directly attached to Inventory module but rather will be reconciled daily by the sales supervisor. Total Daily Sales quantities will then be deducted to SKU available for sales. you will work out also the costing and pricing modules. Correct normalization of database is always a must.

布莱恩是对的。只是为了添加额外的信息。如果您正在为您的企业或客户开发一个完整的系统。我建议您从组织层面开始工作，直到 POS 和会计流程。这将使您的数据库经验更加丰富... :P 根据我在系统开发方面的经验，库存模块总是从盘点+（采购-采购退货）=可供销售的 SKU 开始。POS 不直接附加到库存模块，而是由销售主管每天进行核对。每日总销售数量将被扣除到可供销售的 SKU 中。您还将制定成本核算和定价模块。数据库的正确规范化始终是必须的。