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.

Panegyrize

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.

Fairlie

Heck, the accounting methods make it all the harder to keep track of. I knew that “unit cost” last addition was definitely NOT a good idea... Thanks for the answer. I’m going to keep reading it with special attention. – Shrievalty

Hey there! If I were to use FIFO, how would I relate the fields? If I just sold two items, I would subtract from the oldest (order_date) SourceOrder, right? Sorry, just trying to relate information. Thanks! Oh, by the way, if I handle locations’ orders/inventory individually, shouldn’t location_id go into SourceOrder instead of SourceOrderLine? – Shrievalty

Yes, on the FIFO questions. On the SourceOrder vs SourceOrderLine, you can go either way. I'd put it at the line level to increase flexibility later if you want to issue a single order for multiple stores. Also, please accept the answer. – Fairlie

Where's the best place to read up on things like this? (Database models for warehouse inventory, purchase orders, etc) – Tengdin

great job bro, keep it up – Derwood

Someone know a good book that explain the design pattern of this accounting point of sale system? – Commutation

Answer 2

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.

Waters