Inventory ERD that involves multiple locations

Question

I am trying to layout an Entity Relationship Diagram that involves inventory. All of the items make up one big inventory, but they can be split throughout multiple locations. For example: 20 Cases of Item A are at Location 1 and 5 Cases of Item A are at Location 2.

If they click into an Item they need to be able to see at what locations it is at and how many at each location.

If they click into a Location they need to be able to see what items are at that location and how many.

I know one of my tables is going to be 'Item' and I know another one of my tables is going to be 'Location'

However, I am having trouble figuring out how to branch off from here with join tables and an Inventory table that actually keeps track of each item and how many are throughout each location.

Can someone please help get me started. Anything will help.

Answer 1

Assuming that you want to go with a simplified system of tracking a current snapshot of your physical inventory, you can use a model like this:

The intersection between ITEM and LOCATION tracks the quantity on hand (i.e. your inventory) of a specific item at a specific location.

To get the total inventory of items at all locations you can use this:

SELECT 
  I.ItemID
, I.Description
, SUM(Q.Quantity)
FROM ITEM I INNER JOIN ON_HAND Q
  ON I.ItemID = Q.ItemID
GROUP BY
  I.ItemID
, I.Description

Note, however, that in most practical scenarios, keeping a simple snapshot of physical inventory is a little too simplistic. You should also consider my answer to this question as a more sophisticated alternative for tracking inventory movements.