Point of Sale and Inventory database schema

Question

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.

Answer 1

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.