Re: Product inventory table design

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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
.

Relevant Pages

  • Re: Product inventory table design
    ... Inventory might not be appropriate). ... FK // The combination BranchId and ProductId would be ... putting the ProductID field in tblInventory does nothing to make it easier ... pcs, on the shelf ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Product inventory table design
    ... By putting ProductId in the InventoryDetail, ... pcs, on the shelf ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Product inventory table design
    ... By putting ProductId in the InventoryDetail, an inventory will include the counting of several products, instead of one product per inventory counting. ... putting the ProductID field in tblInventory does nothing to make it easier ... pcs, on the shelf ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Audit Software
    ... I just got a job to clean the dust and crap out of 50 PCs for a customer, and also give them an inventory of the hardware/software on each one. ...
    (alt.comp.hardware.pc-homebuilt)
  • Re: Audit Software
    ... I just got a job to clean the dust and crap out of 50 PCs for a customer, and also give them an inventory of the hardware/software on each one. ...
    (alt.comp.hardware.pc-homebuilt)