Product inventory table design

[Michael Wong]

Hi,

I'm still a newbie in database design (this one is only my third
database). I have some difficulties designing the tables and
relationship for an inventory database.

First, let me explain the business:

The business has several shops (Branch).
The products can be located in these different branches.
The inventory is made separately on the different branches. I need to
keep track of the day the last inventory is made on each branch.

So I came up with these two tables:

Product (ProductId PRIMARY, other fields)
Branch (BranchId, other fields)

Now the difficult part for me is how to design the inventory.
One idea I have is the following tables, but I'm not sure if this is a
correct design:

BranchInventory (ProductId PK, BranchId PK, InventoryDate)
InventoryDetail (InventoryDetailId PK, ProductId FK, BranchId FK, Qty,
Location)

One other design is the following:

BranchInventory (BranchInventoryId PK, ProductId FK, BranchId FK,
InventoryDate)
InventoryDetail (InventoryDetailId PK, BranchInventoryId FK, Qty, Location)

Any suggestion would be very much appreciated.

Thanks

 

[tina]

okay, so you have tblProducts and tblBranches, to provide a list of each in
those two "categories". good.

BranchInventory (ProductId PK, BranchId PK, InventoryDate)
InventoryDetail (InventoryDetailId PK, ProductId FK, BranchId FK, Qty,
Location)

BranchInventory (BranchInventoryId PK, ProductId FK, BranchId FK,
InventoryDate)
InventoryDetail (InventoryDetailId PK, BranchInventoryId FK, Qty,

Location)

close, but not quite, in either proposal. ProductID doesn't belong in
tblBranchInventory, because it doesn't describe the inventory for that
branch *as a whole*, the way the inventory date does.

to track the product inventory numbers for a specific branch on a specific
date, suggest:

tblInventory
InventoryID (primary key, Autonumber)
BranchID (foreign key from tblBranches)
InventoryDate
<note: you could actually use BranchID and InventoryDate as a combination
primary key, instead of using an Autonumber field as primary key. i would go
with the Autonumber field, personally, mainly because i don't like having to
use combination keys as foreign keys in other tables, seems like a big waste
of space to me.>

tblInventoryDetails
DetailID (pk, Autonumber)
InventoryID (fk from tblInventory)
ProductID (fk from tblProducts)
Qty
Location (what is this field for? if it's the "location" of the Branch, it
should *not* be in this table)
<note: again, you could use InventoryID and ProductID as a combination
primary key, instead of using an Autonumber field as primary key. there's no
real problem with doing that, that i can see; so do whichever suits you.>

hth

 

[Guest]

Most of the databases I have seen for keeping track of inventory, are using
the database to record items as they are received, and then as they are sold.
Is your database simply for recording the results of a 100% count of all
current inventory items? If so, is this done about once a quarter?

 

[Michael Wong]

Hi Mnature,

The shop we are running is very rudimentary, and keeping track of each
item sold is nearly impossible (unless we use barcodes).
What we do here, is to only keep track of what is in the warehouse. That
is how many cartons of each product (which we will add to the counting
in the shop).
We do something like a cycle counting, that is we count the products one
at a time (depending on their importance, we will count them more or
less often). That's why I need an inventory date for the specific
product in the specific branch, so we don't have to count the same
product in different branches on the same day, that makes the branches
more independant.


Thanks

 

[Michael Wong]

Hi Tina,

I didn't expect to get a so quick response and I'm very grateful.

I just need to make sure I clearly understand your design, let me know
if I'm wrong:

A branch can make an inventory on a product (on a given day). On that
same day, that branch can count the stock for other products (which will
be on the same inventory)
For that given inventory + product, I can have multiple inventory
details, such as in which carton I have what qty,...

If this is the case, then great!

Actually, the Location field is not really a location, I should have
named it comments or notes to make a comment on the inventory detail
(the carton marking and number for example)


You said that for the table Inventory, I can use InventoryId and
InventoryDate as a combination primary key. In this case, how would I
declare the table InventoryDetail? Would I need to have 3 foreign keys
then (BranchId, ProductId, InventoryDate)?

Now for the programmming part. Sorry if I have so many questions to ask...

How would I get the total of the inventory (on hand qty) for a given
product in a given branch?
Here is what I came up with, except I don't know how to easily filter to
a specific branch:

SELECT Product.ProductName, Sum(ID.Qty) AS QtyOnHand
FROM Product JOIN InventoryDetail AS ID ON ID.ProductId = Product.ProductId
JOIN Inventory ON Inventory.InventoryId = ID.InventoryId
WHERE Inventory.BranchId = @BranchId AND Product.ProductId = @ProductId


Thanks

 

[tina]

comments inline.

Hi Tina,

I didn't expect to get a so quick response and I'm very grateful.

I just need to make sure I clearly understand your design, let me know
if I'm wrong:

A branch can make an inventory on a product (on a given day). On that
same day, that branch can count the stock for other products (which will
be on the same inventory)
For that given inventory + product, I can have multiple inventory
details, such as in which carton I have what qty,...

well, i'm afraid you lost me here. do you need to track *where* the product
is distributed, within a branch? as in, 10 coats in carton A, 12 coats in
carton B, etc - where the product you're counting is "coats"; in other
words, you aren't just tracking that you have 22 coats at Branch X?

the table suggestions i made were with the following idea in mind: each
record in tblBranchInventory describes a specific inventory at a specific
branch on a specific date, and nothing else. example:
InventoryID BranchID InventoryDate
1 1 1/15/2005
2 1 2/15/2005
3 2 1/18/2005
4 2 2/20/2005
5 2 3/22/2005

and each record in tblInventoryDetails describes the quantity of a specific
product counted during a specific inventory, and nothing else. example:

DetailID InventoryID ProductID Quantity
1 2 1 20
2 2 2 15
3 2 3 48
4 4 1 12
5 4 2 50
6 5 2 39

if that's not how you're counting your inventory, then the structure i
posted may very well be inappropriate for your process. (this is the danger
we face in answering these kinds of questions in the newsgroups - trying to
analyze someone else's process and recommend a table structure based on
scanty information.)

If this is the case, then great!

Actually, the Location field is not really a location, I should have
named it comments or notes to make a comment on the inventory detail
(the carton marking and number for example)


You said that for the table Inventory, I can use InventoryId and
InventoryDate as a combination primary key.

actually i didn't. i said "you could actually use BranchID and InventoryDate
as a combination primary key" *instead of* using an autonumber field. in
other words, if you use those two fields as a combo primary key, *don't
include an InventoryID field in the table at all.* sorry i didn't make my
meaning clear on that.

In this case, how would I
declare the table InventoryDetail? Would I need to have 3 foreign keys
then (BranchId, ProductId, InventoryDate)?

yes. BranchID and InventoryDate would be the foreign key linking to
tblBranchInventory, and ProductID would be the foreign key linking to
tblProducts. personally, i really wouldn't use a combo primary key in
tblBranchInventory. it seems such a waste of space to have all those dates
included in tblInventoryID; a single-field autonumber primary key/foreign
key is much cleaner. (again, this is my personal opinion and my standard
practice; a lot of experienced developers do use combo primary keys and i've
never seen any evidence that it's a bad development decision.)

Now for the programmming part. Sorry if I have so many questions to ask...

that's no problem. but before we get into queries, forms, or whatever, let's
figure out what the "right" base structure is first. there's no point in
talking about things that may well change if the table structure has to be
changed.

How would I get the total of the inventory (on hand qty) for a given
product in a given branch?
Here is what I came up with, except I don't know how to easily filter to
a specific branch:

SELECT Product.ProductName, Sum(ID.Qty) AS QtyOnHand
FROM Product JOIN InventoryDetail AS ID ON ID.ProductId = Product.ProductId
JOIN Inventory ON Inventory.InventoryId = ID.InventoryId
WHERE Inventory.BranchId = @BranchId AND Product.ProductId = @ProductId


Thanks

hth

 

[Michael Wong]

Hi Tina,

You are right. I need to make myself clearer, now I understand why
developpers always have difficulties to understand their customers.
It also took me a long time to understand this case...

well, i'm afraid you lost me here. do you need to track *where* the product
is distributed, within a branch? as in, 10 coats in carton A, 12 coats in
carton B, etc - where the product you're counting is "coats"; in other
words, you aren't just tracking that you have 22 coats at Branch X?

Correct, when I'll need the total in Branch X, I'll just add them up. If
it's my branch, I'll need the details so I can get to them quicker, but
if it's on another branch, I'll just need to have their total (for
placing the order for example)

The inventory in each branch is done by cycle counting (the table's name
Inventory might not be appropriate).
What I have done is just shifting the foreign key ProductId in
InventoryDetail to Inventory (note that I have changed the table's name):

Table Inventory:
InventoryId: PK
BranchId: FK // The combination BranchId and ProductId would be
ProductId: FK // unique
InventoryDate

Table InventoryDetail:
DetailId: PK
InventoryId: FK
Qty
Notes

The reason I put ProductId and BranchId in the same table is to easily
get to the InventoryDetails for a given product in a given branch.

And now, the inventory details would be associated to the counting for
the product in that specific branch.

Maybe an example would be a lot clearer:
Lady coat no 634:
Main branch: counted on 14/02/06
- 60 pcs, ctn 123
- 6 pcs, on the shelf (in the shop)
Second branch: counted on 16/02/06
- 3 pcs, on the shelf
Lady pant no 132:
Main branch: counted on 21/02/06
- 2 pcs, a customer has reserved these
No second branch inventory here, because this was a special
order for a customer.
Lady shoes no 852:
Main branch: counted on 22/02/06
- 12 pcs, in stock shelf no 3
- 12 pcs, waiting to be sent to branch 2
- 2 pcs, in the shop
Second branch: counted on 20/02/06
- 0 pcs, placed the order from Main branch already

 

[tina]

comments inline.

Hi Tina,

You are right. I need to make myself clearer, now I understand why
developpers always have difficulties to understand their customers.
It also took me a long time to understand this case...

yes, that really is the biggest challenge for any developer, because you
can't write a program to fully support a process until you fully understand
the process.

Correct, when I'll need the total in Branch X, I'll just add them up. If
it's my branch, I'll need the details so I can get to them quicker, but
if it's on another branch, I'll just need to have their total (for
placing the order for example)

The inventory in each branch is done by cycle counting (the table's name
Inventory might not be appropriate).
What I have done is just shifting the foreign key ProductId in
InventoryDetail to Inventory (note that I have changed the table's name):

Table Inventory:
InventoryId: PK
BranchId: FK // The combination BranchId and ProductId would be
ProductId: FK // unique
InventoryDate

actually, to make this work you'd have to include the InventoryDate field in
the unique index, too - not just BranchID and ProductID.

Table InventoryDetail:
DetailId: PK
InventoryId: FK
Qty
Notes

The reason I put ProductId and BranchId in the same table is to easily
get to the InventoryDetails for a given product in a given branch.

putting the ProductID field in tblInventory does nothing to make it easier
to query for details about a specific product in a specific branch. it's
just as easy to get the records for a given product at a given branch when
the ProductID is in tblInventoryDetails. more on that below.

And now, the inventory details would be associated to the counting for
the product in that specific branch.

Maybe an example would be a lot clearer:
Lady coat no 634:
Main branch: counted on 14/02/06
- 60 pcs, ctn 123
- 6 pcs, on the shelf (in the shop)
Second branch: counted on 16/02/06
- 3 pcs, on the shelf
Lady pant no 132:
Main branch: counted on 21/02/06
- 2 pcs, a customer has reserved these
No second branch inventory here, because this was a special
order for a customer.
Lady shoes no 852:
Main branch: counted on 22/02/06
- 12 pcs, in stock shelf no 3
- 12 pcs, waiting to be sent to branch 2
- 2 pcs, in the shop
Second branch: counted on 20/02/06
- 0 pcs, placed the order from Main branch already

okay, yes, the examples help a lot. i'm assuming that the information
"ctn 123"
"on the shelf (in the shop)"
"on the shelf"
"a customer has reserved these"
"in stock shelf no 3"
(etc)

are all "free-hand" text notes that are entered in the location field in
each record - and now i understand why you named the field "location".

i still recommend that you put the ProductID field in tblInventoryDetails
rather than in tblInventory. and the reason is still the same: BranchID and
InventoryDate describe a specific inventory performed in a specific branch
on a specific date; so they describe the inventory "as a whole". ProductID,
on the other hand, describes a specific *item* that was inventoried, just as
quantity and location do - so ProductID belongs in the same table as the
quantity and location fields.

as i said above, you can easily join tblInventory and tblInventoryDetails in
a query, and set criteria on field tblInventory.BranchID and field
tblInventoryDetails.ProductID, to pull all the records for a specific
product at a specific branch. putting ProductID in tblInventory does nothing
to make the query run better or faster or easier, and putting ProductID in
tblInventoryDetails does nothing to make the query run worse or slower or
harder.

so i'm back to recommending the following table design, as

tblInventory
InventoryID (primary key, Autonumber)
BranchID (foreign key from tblBranches)
InventoryDate

tblInventoryDetails
DetailID (pk, Autonumber)
InventoryID (fk from tblInventory)
ProductID (fk from tblProducts)
Qty
Location

because InventoryID and ProductID are *not* a unique index, you can add 1
record for "Lady coat no 634" for a specific inventory record, or 5 records,
or 20 - the system will allow it, and support it, and it will work just
fine.

hth

 

[Michael Wong]

Hi Tina,

Having a good sleep last night, my head is clearer and I finally
understand your point of view.
By putting ProductId in the InventoryDetail, an inventory will include
the counting of several products (which is the case), instead of one
product per inventory counting.

Now, I will continue with the remaining of the design, before trying the
programming part.

Thanks a lot

 

[tina]

you got it! it all goes back to the data modeling principle: every field in
a table should be a characteristic of the subject that the table is "about".
if a piece of data doesn't directly describe an instance of the entity, it
shouldn't be in that table.

you're welcome, and good luck with the rest of the db. if you need a hand
with anything, we're always here.