0

I am having trouble grasping this concept about managing something like inventory in SQL Server.

There are 3 use cases that I need to make sure I handle:

  1. Adding new inventory
  2. Removing inventory
  3. Moving inventory from one location to another

I am trying to figure out if the below tables are really enough to keep track of the above 3 transactions.

Here would be a simple table structure:

  • Inventory: (Id, LocationId, ProductId, Quantity)
  • Product: (Id, Name)
  • Location: (Id, Name)

With this data available I am trying to figure out a couple of things:

For an individual product would I be able to see the available grand total?

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

For an individual product would I be able to see how many are available at Location A and Location B?

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

Would I be able to keep track of history of Check-Ins and Check-Outs?
This is the part where I start getting confused...

Is this where it would be worth having an inventory header table? Or is an InventoryHeader table not necessary due to Purchase Orders and Sales Orders being the primary ways a user Checks-In and Checks-Out products.

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)?

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)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Blake Rivell
  • 13,105
  • 31
  • 115
  • 231

1 Answers1

1

You could keep the inventory table as is and have an inventoryTransactions table and then use a trigger on the transactions table to update the inventory table as transactions go in and out. This way you would have the full audit history in the transactions table, but have a smaller, more efficient read from the inventory table. You obviously would need to ensure that no writes could be performed by users to the inventory table though, and that it is only written to by the trigger.

Rich Linnell
  • 973
  • 5
  • 14
  • So your saying I would create a table called InventoryTransaction with columns (Id, InventoryId, Date, etc...)? Then everytime an item is added to it have a trigger update or add to the Inventory table? Is it going to be possible to send the trigger the params that I will need for the Inventory record such as ProductId and LocationId since I won't be storing them in the InventoryTransaction table? – Blake Rivell Aug 08 '16 at 06:07
  • Hmm after researching and finding this post which is very similar to what you are telling me to do: http://stackoverflow.com/questions/287097/inventory-database-design I believe I only need one table InventoryTransaction with no header. They are saying it takes seconds to get the sum of a product. Any other suggestions? Maybe it would be nice to still do two tables like you were saying so I always have one to pull from really fast. – Blake Rivell Aug 08 '16 at 06:30
  • So if a user moves an inventory item from one location to another would I create two InventoryTransaction records? Lets say there are 10 bikes at Location A and 10 bikes at Location B. The user moves 5 bikes from Location A to Location B. I think I would update the Inventory table where ProductId and LocationId match and then create one InventoryTransaction (w/ InventoryId for Location B) where quantity is 5 to Location B and another InventoryTransaction (w/ InventoryId for Location A) where the quantity is -5 from Location A. – Blake Rivell Aug 08 '16 at 06:41
  • 1
    Yeah, that seems right. Reading through the post you linked to they lean towards storing all transactions and calculating the stock on hand as a total of those transactions. This is perfectly fine if you can be sure your DB will be performant enough. Their main concern with the "Stock On Hand" table that I suggested is that it could get out of sync with the sum of the transactions. Firstly, I would view that as very unlikely if it is being updated by triggers, and secondly you could avoid it by making the trigger calculate the stock on hand as being the sum of all transactions. – Rich Linnell Aug 08 '16 at 07:36