title: Inventory design management - FIFO / Weighted Average design which also needs historical inventory value
body: <p>I have a database with the following details:</p>
  
  <p><strong>Product</strong></p>
  
  <pre><code>Name
  SKU
  UOM (There is a UOM master, so all purchase and sales are converted to base uom and stored in the db)
  Some other details
  has_attribute
  has_batch
  </code></pre>
  
  <p><strong>Attributes</strong></p>
  
  <pre><code>Name
  Details/Remarks
  </code></pre>
  
  <p><strong>Product-Attribute</strong></p>
  
  <pre><code>Product (FK)
  Attribute(FK)
  Value of attribute
  </code></pre>
  
  <p><strong>Inventory Details</strong></p>
  
  <pre><code>#This is added for every product lot bought &amp; quantity available is updated after every sale
  
  Product (FK)
  Warehouse (FK to warehoue model)
  Purchase Date
  Purchase Price
  MRP
  Tentative sales price
  Quantity_bought
  Quantity_available
  Other batch details if applicable(batch id, manufactured_date, expiry_date)
  </code></pre>
  
  <p><strong>Inventory Ledger</strong></p>
  
  <pre><code>#This table records all in &amp; out movement of inventory
  
  Product
  Warehouse (FK to warehoue model)
  Transaction Type (Purchase/Sales)
  Quantity_transacted(i.e. quantity purchased/sold)
  Inventory_Purchase_cost(So as to calculate inventory valuation)
  </code></pre>
  
  <p>Now, my problem is:</p>
  
  <p>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?</p>