Re: Product inventory table design

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

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. :)

"Michael Wong" <nospam@xxxxxxxxxx> wrote in message
news:egYem2%23NGHA.748@xxxxxxxxxxxxxxxxxxxxxxx
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 wrote:
comments inline.

"Michael Wong" <nospam@xxxxxxxxxx> wrote in message
news:uhqUi43NGHA.1192@xxxxxxxxxxxxxxxxxxxxxxx

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. :)


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


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




.

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, 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: Product inventory table design
    ... The inventory in each branch is done by cycle counting. ... What I have done is just shifting the foreign key ProductId in InventoryDetail to Inventory: ... pcs, on the shelf ... a customer has reserved these ...
    (microsoft.public.access.tablesdbdesign)
  • Running totals in a query
    ... Group by [ProductID] ... Change them to suit ... >I am trying to build a query to only show the inventory ...
    (microsoft.public.access.queries)