Inventory DB design help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have developed a (what used to be) simple inventory control database to
replace the old Cardex system the buyers in my company use to track product
inventory.

Here is what I have so far…

tblInvTransactions
TransID
TranTypeID (FactoryCount, Deduction, Return, etc- from tblTransType)
ProductID
POID
QtyOrdered
QtyOut
QtyAdjusted
QtyCounted
QtyIn
BuildingID

tblPurchaseOrders
POID
PONumber
SupplierID
EmplyeeID
ShipViaID
StausID
POMemo


I have very simple forms used to view and manually add transactions into the
InvTransactions table.

Here is my problem…
I would like to be able to have tables that relate in the following way to
track PO Received Goods.

Each PO could have many products
Each Product could have many release Qtys and Dates
Each Release could have many receipt Qtys and Dates

And now my simple DB has gotten very complex. I have tried numerous times
to design the tables and relationships needed but I keep getting hung up on
the qtys in the additional tables get “back into?†the Inv Trans table.

Could one of you smart people get me on the right track?

Thanks,
Diane
 
Any inventory tracking system is, inherently, going to be pretty
complicated. You won't be able to track everything in a single
invTransactions table. Further, when you attempt to merge it with a
Purchasing system, you add another layer of complexity to the system. You
also cannot track purchase orders in a single PurchaseOrder table. You will
need to have a POLines table for each line on the PO and, depending on how
detailed you need to get, a POShipments table (which tracks possible
multiple shipments for a single line) and a PODistributionsTable (which
tracks distribution of costs to multiple cost centers in your company). The
last 2 may not be necessary, but are good to keep in mind just in case.
 
Thanks for the confirmation...that is where I was headed...here is what I
have got now in addition to my initial PO table:

tblPODetails
PODetailID
POID
ProductID
OrderQty

tblPOReleases
POReleaseID
PODetailID
ReleaseQty
RequiredDate
PromiseDate

tblPOReceipts
POReleaseID
POShipID
ReceivedQty
ReceivedDate

I am still struggling with how to make this all "work" now.

Am I correct only including ProductID in my POdetails table? or should I put
it in all of them?

I am guessing that my QtyOrdered in my InvTrans table is now not going to
exist (it will now be a calucated field in my forms and reports that totals
the ReleaseQtys for each PODetail...right?).

and somehow I am going to have to make the QtyReceived add to the QtyIn in
the transaction table...or will that be a calculated field as well?

I was trying to keep all of the transactions in one table so that I could
display all of the transactions for one product together in Chronological
order. Surely I will still be able to do that with this design, but how?

Sorry this is so much, but for some reason I just can't wrap my head around
how this is all going to tie together.

I will keep reading, doing, and redoing....fortunately most of this is just
being done for the learning experience...

Thanks again for your help,
Diane
 
tblPODetails
PODetailID
POID
ProductID
OrderQty

tblPOReleases
POReleaseID
PODetailID
ReleaseQty
RequiredDate
PromiseDate
Click to expand...

I'm not quite sure what you have in mind as a Release. In most purchasing
systems, that would represent a detail line for a Planned Purchase order, in
which the release would be sent to the vendor to order a partial order from
the full quantity on the planned order. Is that what you have in mind.
tblPOReceipts
POReleaseID
POShipID
ReceivedQty
ReceivedDate

I am still struggling with how to make this all "work" now.

Am I correct only including ProductID in my POdetails table? or should I
put
it in all of them?
Click to expand...

That will depend on more than one factor, but for your situation, I would
say just the one table.
I am guessing that my QtyOrdered in my InvTrans table is now not going to
exist (it will now be a calucated field in my forms and reports that
totals
the ReleaseQtys for each PODetail...right?).

and somehow I am going to have to make the QtyReceived add to the QtyIn in
the transaction table...or will that be a calculated field as well?
Click to expand...

You need to readdress the multiple quantity fields in your InvTransactions
table. You really only need one quantity field, in most cases, and what
happens to that quantity would be determined by what KIND of transaction it
is. For a receipt, quantity on hand would increase. For an issue out of
stores, quantity on hand would decrease, and so on.
I was trying to keep all of the transactions in one table so that I could
display all of the transactions for one product together in Chronological
order. Surely I will still be able to do that with this design, but how?
Click to expand...

If your tables are properly normalized, you will have no problem selecting
them in chronological order.
 
You need to readdress the multiple quantity fields in your InvTransactions
table. You really only need one quantity field, in most cases, and what
happens to that quantity would be determined by what KIND of transaction it
is. For a receipt, quantity on hand would increase. For an issue out of
stores, quantity on hand would decrease, and so on.
Click to expand...

Funny...now I am where I started three weeks ago.

One table for every transaction type...each with its own TransID, ProductID
and Qty

that was where I got stuck and changed to a master transaction table housing
all of the transaction info...but I think you are offering me a comprimise...

So I revert to the multiple tables I had (one for deductions, one for
returns, one for factory count adjustments, and one for POs (actually a few
for POs as discussed earlier)

And I change my
tblInvTrans
InvTransID
ProductID
TransType
TransQty

and I will use this to calculate my QOH...right?
but...how is this new inventory transaction table linked to my other tables
that house my actual transactions? In other words, when I recieve goods in
how does that qty get into the InvTransaction table?

I'm not quite sure what you have in mind as a Release. In most purchasing
systems, that would represent a detail line for a Planned Purchase order, in
which the release would be sent to the vendor to order a partial order from
the full quantity on the planned order. Is that what you have in mind.
Click to expand...

Similar....the buyers place one order, with one POnumber, on one
date....but, the PO may say 50 of part A to be shipped 10 a month for the
next 5 months. Those are what they call releases.

Thanks again for all of your help...

Diane
 
returns, one for factory count adjustments, and one for POs (actually a
few
for POs as discussed earlier)

And I change my
tblInvTrans
InvTransID
ProductID
TransType
TransQty

and I will use this to calculate my QOH...right?
but...how is this new inventory transaction table linked to my other
tables
that house my actual transactions? In other words, when I recieve goods
in
how does that qty get into the InvTransaction table?
Click to expand...

This is where the fun begins. You will have to write VBA code to update yoru
InvTransaction table when transactions are entered into the other tables.
Thus, when a product is received into the receiving tables, you will need to
update that quantity in the transactions table.
Similar....the buyers place one order, with one POnumber, on one
date....but, the PO may say 50 of part A to be shipped 10 a month for the
next 5 months. Those are what they call releases.
Click to expand...

Excellent. That is exactly what I meant by a release too.
 
This is where the fun begins. You will have to write VBA code to update yoru
InvTransaction table when transactions are entered into the other tables.
Thus, when a product is received into the receiving tables, you will need to
update that quantity in the transactions table.
Click to expand...

So I was on the right track all along. No wonder I got stuck...my VBA
knowledge is limited...I have done very little code writing from scratch,
only copied, or tried to fix others code. But I have been learning it
slowly, now is the time to break out my books I have been ignoring.

One last question before I go off to teach myself VBA...

In the IVTrans table...
My VBA will update the ProductID, TransQty, and TransType all from my other
tables...Right?
Can the InvTransID be updated like the TransQty as well..for example if my
ReceiptID is R5000 (autonumber) then can that also be my InvTransID? or does
it have to be another unique number?

Also, in my table relationships window (I need the visual) will my InvTrans
table only be linked to my Products table and my TransactionTypes table for
RI?

Thanks so much for all of your help...

Diane
 
My VBA will update the ProductID, TransQty, and TransType all from my
other
tables...Right?
Yes

Can the InvTransID be updated like the TransQty as well..for example if my
ReceiptID is R5000 (autonumber) then can that also be my InvTransID? or
does
it have to be another unique number?
Click to expand...

If I understand your situation right, the InvTransID is a unique value for
each transaction that occurs and will not be updatable. I'm not sure quite
how you have an "autonumber" value of 'R5000'.
 
If I understand your situation right, the InvTransID is a unique value for
each transaction that occurs and will not be updatable. I'm not sure quite
how you have an "autonumber" value of 'R5000'.
Click to expand...

For each of my seperate tables (deductions, factorycounts, receipts, etc) I
have my PK set as an autonumber preceded by a letter ("R"&[autonumber] for
receipts, "D"&[autonumber] for deductions, etc). I was hoping to use these
same numbers as my InvTransID's. They would each be unique due to the
letters preceding the autonumbers. That way when I look at the PK feild in
the InvTrans table I could tell at a quick glance what type of transaction
they were from the InvTransID.

Not sure it makes that much of a difference, it was just a thought.

Thanks again for all of your help,
Diane
 
For each of my seperate tables (deductions, factorycounts, receipts, etc)
I
have my PK set as an autonumber preceded by a letter ("R"&[autonumber] for
receipts, "D"&[autonumber] for deductions, etc). I was hoping to use
these
same numbers as my InvTransID's. They would each be unique due to the
letters preceding the autonumbers. That way when I look at the PK feild
in
the InvTrans table I could tell at a quick glance what type of transaction
they were from the InvTransID.

Not sure it makes that much of a difference, it was just a thought.
Click to expand...

So you are concatenating a letter to the autonumber field. That's just fine
for display purposes. You should probably not use those values as the
transaction ids. You are asking for trouble if you do that.
 
Lynn

Thanks so much for all of your advice...I have learned tons since we started
this conversation.

Here is my next stumbling block....

In my InvTrans table I have an InvTransID [autonumber] set as my PK.

In addition, I would really like to include in my InvTransactions table a
feild which houses the ID (autonumber FK) of the table from which the data
came.

In other words...if a product was received in my ReceivedGoods table and was
assigned a ReceiptID, I would like that too to be added to the InvTrasactions
table...so I can refer back to where that line got added from.

I would like all of these FK's to be appended into the same feild (TransID)
in the InvTrans table. My problem is that this results in Key violations,
the number 5 could be assigned to the deductionsID and the receivedGoodsID
etc....

I know one way to work around this is to have each FK ID feed a different
feild, but is that the only way?


Thanks,

Diane
 
I would like all of these FK's to be appended into the same feild
(TransID)
in the InvTrans table. My problem is that this results in Key violations,
the number 5 could be assigned to the deductionsID and the receivedGoodsID
etc....

I know one way to work around this is to have each FK ID feed a different
feild, but is that the only way?
Click to expand...

Do you have a unique index set on the TransID field? If so, you need to
remove that.
 
After further thought this is where I am at...

I went ahead and had each transID (deductID, recievedID, FCID, etc) go into
its own field in the InvTrans Table and have this feild set at Indexed(no
duplicates). This way, only new records will be added to the InvTrans table
due to key violations (Good).

But, what if the user edits a line in the form linked to the deductions
table? Say they recounted and relalized that the first qty entered was wrong.


This edit will be made on a record that already has been added to the
InvTrans table...so now my key violation wont let it be added again (good,
but it also won't allow my new Qty to be changed..bad). How do I get it
InvTrans table to update any changes made to records already in the table.
In other words, I changed the Qty in a record in the factorycount table, how
do I get the same change to be made in the InvTrans table at the same time...

Thanks again for your time and help.

Diane
 
Diane,
In most inventory systems, you would never do an update to a
TransactionsTable record. Rather, you would enter a new record as an
adjustment.
 
Back
Top