Re: Inventory-Am I on wrong path?
- From: "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx>
- Date: Thu, 17 Aug 2006 11:10:30 +0800
Josh, if you can calculate the inventory, there is absolutely no need to
store the current inventory levels. It is far better to take the normalized
approach, and NOT store the dependent value.
I didn't understand the bit about not having a Products table though. If you
store anything other than ItemXYZ, it seems to me that you would need
records for the different products.
You probably do need a way to handle transactions a bit more than you have.
The database must cope with weird things such as:
- You ordered 100, but they actually sent 1000.
- You ordered 500. They sent 100, and backordered 400.
- There were 100 backordered from last time, plus the 200 from a new order,
so they sent 300 at once.
- You ordered 100 for a line that is discontinued. They sent the 24 they
had, but the others will never be sent.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Josh" <traygo@xxxxxxxxxxx> wrote in message
news:q347e25fpe1rke8qnnfvp2ouv6gs9pquhu@xxxxxxxxxx
I'm wondering if I'm setting myself up for problems......am I wrong in my
design
of a fairly simple Inventory DB.
This is for receiving only. Some items will be stocked on shelf, but most
items
will be delivered directly to various departments.
Most of the samples I see have a "Products" and/or "Inventory" Table, and
a
Transactions Table. I only have a Transactions Table. (and P.O. Table).
To get current stock level, I sum the transactions field. (+24 for 24
ItemXYZ
received, -8 for when some was delivered to department).
So, my thinking is why have *any* table that stores an Inventory? But now,
as I
said, I'm wondering if I'm missing something obvious....
Also, the P.O. Item(s) when ordered are inserted into the Transactions
Table as
a positive, but with Status of "OnOrder". When Recieved, the Status is
changed
to "Received". I wonder about the wisdom of doing that, also. Would it
be
better to add the P.O. Items at all (to the transactions table) until
Received.
If it would be better to do that, then I suppose, to get current stock
info
would take sum of Transaction table to get Stock on hand, then add the sum
of
P.O. "OnOrder" items (for each item).
Thoughts?
Thanks, Josh
.
- Follow-Ups:
- Re: Inventory-Am I on wrong path?
- From: Josh
- Re: Inventory-Am I on wrong path?
- References:
- Inventory-Am I on wrong path?
- From: Josh
- Inventory-Am I on wrong path?
- Prev by Date: Re: So how do they do that?
- Next by Date: Re: order history database design in access 2003
- Previous by thread: Inventory-Am I on wrong path?
- Next by thread: Re: Inventory-Am I on wrong path?
- Index(es):
Relevant Pages
|
