I have a database with the following details:ProductNameSKUUOM (There's a UOM master, so all purchase and sales are converted to base uom and stored in the db)Some other detailshas_attributehas_batchAttributesNameDetails/RemarksProduct-AttributeProduct (FK)Attribute(FK)Value of attributeInventory Details#This is added for every product lot bought & quantity available is updated after every saleProduct (FK)Warehouse (FK to warehoue model)Purchase DatePurchase PriceMRPTentative sales priceQuantity_boughtQuantity_availableOther batch details if applicable(batch id, manufactured_date, expiry_date)Inventory Ledger#This table records all in & out movement of inventoryProductWarehouse (FK to warehoue model)Transaction Type (Purchase/Sales)Quantity_transacted(i.e. quantity purchased/sold)Inventory_Purchase_cost(So as to calculate inventory valuation)Now, my problem is:I need to find out the historical inventory cost. For example, let's say I need to find out the value of inventory on 10th Feb 2017, what I'll be doing with the current table is not very efficient: I'll find out current inventory and go back through the ledger for all 1000-1500 SKU and about 100 transactions daily (for each sku) for more than 120 days and come to a value. taht's about 1500*100*120. It's Huge. Is there a better DB design to handle this case?
Inventory design management - FIFO / Weighted Average design which also needs historical inventory value
database;web development;database design;relational database;schema