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.

score 4 · Accepted Answer · edited May 23 '17 at 10:27

4

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.

edited May 23 '17 at 10:27

Community

1
1

answered Jul 31 '16 at 12:46

Joel Brown

14,123
4
52
64

Thank you for explaining this. So the join table manages the quantity which is perfect. I sometimes forget all that I am capable of doing with SQL when I am laying out an ERD. As far as your more sophisticated example is there any difference when it comes to Item, Ineventory_DTL = OnHand, and Location? – Blake Rivell Jul 31 '16 at 14:21
1

@BlakeRivell - The key difference with the more sophisticated model is that in it you don't record a static count of quantity. Instead you record ins and outs and getting the quantity on hand at a point in time is a matter of adding up all the ins and outs. This gives you more information (quantity at any point in time, where things came from/went) so you can reconcile actual physical inventory counts with your data. Depending on what you're doing, this extra sophistication may not be needed, but it might very well be. Only you will be able to judge this. – Joel Brown Jul 31 '16 at 14:41
Additionally, I left a question on the other post about how you would incorporate Item Rentals along with item sales in an ERD like that. The main difference is that rentals have a return date and the user needs to be able to see how many are available and in use and by who. – Blake Rivell Jul 31 '16 at 14:45

score 1 · Answer 2 · answered Jul 31 '16 at 08:38

If you record items individually, e.g.

Item (item_id PK, description)
Location (location_id PK, name)
Inventory (inv_id PK, item_id FK, location_id FK)

Then you can query for totals when you need it:

SELECT l.location_id, l.name, i.item_id, i.description, COUNT(*)
FROM Inventory v
INNER JOIN Location l ON v.location_id = l.location_id
INNER JOIN Item i ON v.item_id = i.item_id
GROUP BY l.location_id, l.name, i.item_id, i.description

Inventory ERD that involves multiple locations

2 Answers2