Answer originally left in comments by Nick McDermaid
- 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.
- You could add a flavour attribute either to the Product table (in which case it gets a new id), or to the LineItem table.
- 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