销售点和库存数据库模式 [英] Point of Sale and Inventory database schema

问题描述

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

有些事情需要考虑： p>

• 产品在整个系统中始终是相同的（相同的ID），但库存（每个产品的销售可用单位）是每个位置唯一的。位置Y和Z都可以具有产品X的出售单元，但是如果例如从位置Y出售两个单元，则不应影响位置Z的库存。


• 从位置Y销售一（1）个产品X单位，表示位置Y的库存应从其库存中减去一个单位

从此，我想到了这些表：

• 位置

  
  
  
  
  
  • id
  
  
  • 名称
  
  



• 产品

  
  
  
  
  
  • id
  
  
  • 名称
  
  



• 交易

  
  
  
  
  
  • id
  
  
  • 描述
  
  



• inventoryories_header

  
  
  
  
  
  • id
  
  
  • location_id
  
  
  • product_id
  
  



• inventoryings_detail

  
  
  
  
  
  • inventoryories_id
  
  
  • transaction_id
  
  
  • unit_cost
  
  
  • unit_price
  
  
  • 数量
  
  
  
  

  • orders_header

    
    
    
    
    
    • id
    
    
    • 日期
    
    
    • 总计（从orders_detail数量*价格计算;仅用于将来的数据验证）
    
    
  
  

  • orders_detail

    
    
    
    
    
    • order_id
    
    
    • transaction_id
    
    
    • product_id
    
    
    • 数量
    
    
    • 价格
    
    
  
  
  
  
  

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

  
  
  1. 如何跟踪单位成本的变化？如果有一天我开始为某种产品付款，我需要跟踪边际效用（（成本*数量） - （价格*数量）=边际效用 ）某种方式我以为库存大多是为此而设计的。我不会有其他的关心。
  
  
  2. 关系是否稳固？我仍然很难想想，如果地点有库存，或者库存有几个位置。这很疯狂。
  
  
  3. 您如何保持/了解您当前的库存水平？由于我不得不分开库存表以跟上成本更新，我想我只需要加上库存中所列的所有数量。
  
  
  4. 你想分享什么建议？
  
  

  我确定我还有一些问题，但这些都是我需要解决的问题。此外，由于我第一次使用Ruby on Rails，实际上，作为一种学习体验，在设计时被阻止是不幸的，而不是让我更快地执行实现，但我猜想是这样的。 / p>
  
  

  提前感谢

  解决方案

  这里的棘手部分是你真正做的不仅仅是一个POS解决方案。你也在做一个库存管理&基本成本会计系统。

  
  
  

  您需要解决的第一种情况是您将用于确定任何出售物品的成本的会计方法。最常见的选项是FIFO，LIFO或特定标识（所有可以Google格式的条款）。

  
  
  

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

  
  
  

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

    SourcingOrder 
    -  order_number 
    -  order_date 
   
   SourcingOrderLine 
    -  product_id 
    -  unit_cost 
    - 数量
    -  location_id 
    

  库存可以是一个级别...

    InventoryTransaction 
    -  product_id 
    - 数量
    -  sourcing_order_line_id 
    -  order_line_id 
   -  location_id 
    -  source_inventory_transaction_id 
    

  每次在商店收到一个SourcingOrderLine，你会创建一个具有正数量的InventoryTransaction和FK引用 sourcing_order_line_id ， product_id 和 location_id 。

  
  
  

  每次进行销售时，您都将创建一个库存交易，其数量为负，FK引用 order_line_id ， product_id 和 location_id ， source_inventory_transaction_id 。
  
  

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

  
  
  

  位置的当前库存将为 SELECT sum（quantity）FROM inventory_transactions WHERE product_id =？和location_id =？
GROUP BY product_id，location_id 。

  
  
  

  边际成本将通过从销售追溯到2个相关库存交易来计算请注意：您必须处理在2个库存交易中分配一个订单行的情况，因为订单数量大于剩余的订单数量下一个库存交易要分配。这个数据结构将会处理这个问题，但是你需要自己编写逻辑并查询。

  
  

  I’m trying to create a basic Point 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. Its stocked 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.

  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

  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?

  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.

  Thanks in advance.

  解决方案

  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.

  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).

  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).

  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.

  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_id and 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_id and location_id, source_inventory_transaction_id.

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

  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.

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

  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.

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