Postgresql – Inventory Database Design? Get historical data from DB for reporting & accounting

database-designpostgresqlschema

Following is the design of my db:

Product:

ID
Name
Quantity in Hand (as on date - total across all format)
Reorder_point
Remarks

Warehouse:

ID
Name
Address
Remarks
is_default

Inventory:

ID
Product FK
Warehouse FK
Quantity
Purchased_on (This field is to be used to consider product ageing)
Purchase Rate
Tentative Sales Rate
MRP

Purchase Order/Sales Invoice Table (Both are similar)

ID
Total
Date
Warehouse FK
Vendor FK/Customer FK
Amount Paid/Amount Received

Line Item for Purchase/Sales

Product FK
Unit FK (We have a unit table to maintain conversion from one unit to other, eg: Dozen to Nos, etc)
Quantity
purchase_price/sales_price

Now, I wanted to know:

  1. Is this a good DB design, or does it have an inherent fault somewhere?

  2. Suppose two products, varying only in flavor – how would that be reflected here? (They may not need separate product ID/SKU)

  3. Most importantly, I can always get the inventory in hand, but let's say, today is 2nd May & I need to know inventory details as on 15th April, how can this db design do it? Or is there a better DB design to handle that?

Edit

As of now let's assume purchase and sales happen immediately (as in, there's no purchase order & sales order in between).

Now, the issue with the inventory table is I can't calculate the historical value of the inventory. As in, lets say the value of the inventory in 3rd May s $500000, how can I find out the inventory value in 10th April from the data I have here?

Best Answer

Answer originally left in comments by Nick McDermaid

  1. It depends on your requirements. In the current model you can't record the same order, different item taken from a warehouse on a different day - the date is at order level.
  2. You could add a flavour attribute either to the Product table (in which case it gets a new id), or to the LineItem table.
  3. Maybe. If the correct product IN date is in Inventory, and the correct product OUT date is in the purchase order header you can. But this doesn't appear to have the correct level of detail for inventory, more sales... depends.

By correct level of detail, what I mean is: Does PurchaseOrder.Date record when every widget left the warehouse? I don't think so. It most likely records when you a purchase order was raised. That PO might consist of 5 widgets. Three were picked and supplied from warehouse A on 1st May, and two were picked and supplied from warehouse B on 3rd May. I don't think your current set of tables have enough detail to describe when things left the warehouse (unless they all leave the same warehouse immediately that you get the PO, highly unlikely).

You don't have a 'left warehouse' transaction table so you don't know what date items left the warehouse so you can't work back and calculate inventory. warehouse db's usually have a 'warehouse transaction' table that records when items arrived and left the warehouse. You don't have that in your model. PurchaseOrder has a date but it doesn't really record when items left. Inventory has a date but it probably doesn't really record when the item arrived. You also need to periodically record stocktake levels.

To "find out the inventory value in 10th April from the data I have here" you take the current inventory value (today) and reverse all the inventory in transactions back to 10th April (from inventory table I guess), and also reverse all the sale (inventory out) transactions and you'll arrive at where you were on 10th April. As long as you are recording all inventory in and out transactions, and you know what you have today, you can work it out. But how do you know what you have today? Do you do a stocktake every day?

So if you have 10 cogs and 20 widgets today and over the last two weeks you had a total of 6 cogs in and 8 cogs out, you know that you effectively had -2 cogs over that time, so you must've had 10 - (-2) = 12 cogs two weeks ago. It's actually more realistic to calculate a stock balance based off a historical stock take and add forwards. You might have a stocktake every 3 months. From your stocktake in Jan you had 12 cogs, and from Jan to Apr you had 7 cogs in and sold 2 cogs so you know in April you had 12+7 - 2 = 17 cogs.

Notice a theme here? You need to understand your business. You need to analyse it. Back to your actual schema: it's not really stock focused, it's sales focused. You need a dedicated stock transaction table if you want to track stock levels. Lastly, there are many sample stock schemas online. Like this one: https://stackoverflow.com/questions/4380091/best-structure-for-inventory-database

Related Question