I am designing the Inventory Control portion of our database so that (for the most part) additions are added from Purchase Orders that we send to suppliers and subtractions would be subtracted based on Orders (sales) we process. Of course I would use the
corresponding tables in the database to pull that data directly from the Orders & Purchase Orders Tables. The advantages of this method was explained to me previously vs collecting
all inventory transactions in a transaction table. This made a lot of sense, especially in light of potential corrections or adjustments to an order or purchase order which occur all too often. So this method of
using Sales & Purchase Orders will take care of that.
But, I can see that there may be still be other transaction types that may occur that aren't accounted for with those two processes, like returns, damage, and maybe a few others I can't think of right now. So should I still be designing an Inventory Transaction
Table to hold those transactions and probably an Inventory Transaction Type Table even if I am using the two tables from Orders (Sales) & Purchase Orders? Because this way, the calculations for Available Qty would have to come from multiple tables.
Also, regarding, Purchase Orders, though we want to know what's on order from a supplier, the calculation of the actual Available Qty can only really happen when any or part of that order is delivered to the warehouse. So I think I need to track deliveries
to the warehouse as well and note them separately from what is ordered in a purchase order. Does that sound correct?
