Managing Product Inventory in SQL Server
<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>