title: Managing Product Inventory in SQL Server
body: <p>I am having trouble grasping this concept about managing something like inventory in SQL Server.</p>
  
  <p>There are 3 use cases that I need to make sure I handle:</p>
  
  <ol>
  <li>Adding new inventory </li>
  <li>Removing inventory </li>
  <li>Moving inventory from one location to another</li>
  </ol>
  
  <p>I am trying to figure out if the below tables are really enough to keep track of the above 3 transactions.</p>
  
  <p>Here would be a simple table structure:</p>
  
  <ul>
  <li><code>Inventory</code>: (Id, LocationId, ProductId, Quantity)  </li>
  <li><code>Product</code>: (Id, Name)  </li>
  <li><code>Location</code>: (Id, Name)  </li>
  </ul>
  
  <p>With this data available I am trying to figure out a couple of things:</p>
  
  <p>For an individual product would I be able to see the available grand total?</p>
  
  <pre><code>SELECT p.Name, SUM(i.Quantity) AS QuantityOnHand 
  FROM Inventory i
  INNER JOIN Product p ON i.ProductId = p.Id
  WHERE i.ProductId = 1
  GROUP BY p.Name
  </code></pre>
  
  <p>For an individual product would I be able to see how many are available at Location A and Location B?</p>
  
  <pre><code>SELECT p.Name, SUM(i.Quantity) AS QuantityOnHand 
  FROM Inventory i
  INNER JOIN Product p ON i.ProductId = p.Id
  WHERE i.ProductId = 1 AND i.LocationId = 3
  GROUP BY p.Name
  </code></pre>
  
  <p>Would I be able to keep track of history of Check-Ins and Check-Outs?<br>
  This is the part where I start getting confused...</p>
  
  <p>Is this where it would be worth having an inventory header table? Or is an <code>InventoryHeader</code> table not necessary due to Purchase Orders and Sales Orders being the primary ways a user Checks-In and Checks-Out products.</p>
  
  <p>The main concern in the back of my head is that I am scared to just keep updating Quantities on the Inventory table without any log of these transactions. Is this log usually kept from Purchase Orders (Check-In) and Sales Orders (Check-Out)?</p>
  
  <p>I just feel like if I go with an InventoryHeader and InventoryDetail approach the InventoryDetail table will have a whole lot of records in it and I won't be able to query how many there currently are of an item without going through every single InventoryDetail doing SUM(Credits) - SUM(Debits)</p>