Inventory Database Question
PROptions
I have a quick question regarding what is the best method for
tracking
transactions and balances in an inventory system. I have been building
systems for many years and have built inventory systems. I have built
them a variety of ways - depending on the type and size of the system.
But one thing that has bothered me over the years is what is the best
method for tracking transactions and balances, as it relates to the data
model. I will make this as simple as possible. Most inventory systems
have an item table, or tables, which describes the items in the
inventory. Now off of the item table you will have one or more
transaction tables. One method is to basically track all the positives
and negatives and then add them up when you create a report. But over a
period of time, these tables could get pretty large, thus, reporting
slows down. One method is to track the balance in each record, but this
means you must have a calculated field in your table. Some schools of
thought says putting calculated fields in a table is a taboo. Another
method is to have a table that tracks all of your increases in your
inventory and then a table that tracks your decreases in inventory, then
does the calculating off these two tables. And I have seen several
other variations of these "simplistic" examples I described. I have
used variations of these methods based on the type and size of the
system. Now my question: What is the "best" method? Please let me
know from your experiences of what has worked the best for you.
Thank you.
--
Gary A. Harris, CSP, BS
PROptions
Post Office Box 5879
Olympia, Washington 98509-5879
Internet: http://www.proptions.com
Antiques/Collectables: http://www.proptions.com/antiques.htm
E-Mail: mailto://in...@proptions.com
or mailto://Ga...@proptions.com
Phone: (360) 438-3705 (Voice and Fax)
(360) 923-6713
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 80,000 Newsgroups - 16 Different Servers! =-----
Jerry Gitomer
>Greetings,
> I have a quick question regarding what is the best method for
[big snip]
> Thank you.
>--
>Gary A. Harris, CSP, BS
Since most of my experience with inventory systems is with online systems
where instant feedback is required when a customer places an order I prefer to
include a calculated quantity-on-hand column in the inventory table.
--
Jerry Gitomer
Once I learned how to spell DBA, I became one
PROptions
Thanks for your input. I've done that before, but I had to write some
very tight code so I wouldn't leave myself open to have a quantity
descrepancy between the the receieved and outgoing items and the
qty_on_hand fields. I assume you then posted the received and sold
quantities to this qty_on_hand field for each transaction? Also, one
last quick question, for a single item that had multiple lot numbers,
such as chemicals or medicines, would you then have a qty_on_hand field
for each lot number and/or a single qty_on_hand for the item itself? I
have ran into this many times and so far I have been lucky.
Again thanks!
..........Gary
Ray Smith
The current stock level is now "stock at the start of the month" + this
months movements.
This method needs an end of month to be run to update transactions to flag
them to have passed an end of month and therefore do not use to calculate
the current stock levels.
This end of month can also move (or delete) transactions (or transactions of
a certain date) to an archive table.
I used to have a record in a table for each product for each end of month.
This had fields like:
qty in for month,
qty out for month,
value in for month,
value out for month,
stock at end of month,
and month and year number!
The down side of all this is "End of Month" processing and extra fields and
extra tables.
Let me know if it was helpful
Ray Smith.
PROptions <Ga...@proptions.com> wrote in message
news:38D2E740...@proptions.com...
> Greetings,
> I have a quick question regarding what is the best method for
> tracking
> transactions and balances in an inventory system. I have been building
> systems for many years and have built inventory systems. I have built
> them a variety of ways - depending on the type and size of the system.
> But one thing that has bothered me over the years is what is the best
> method for tracking transactions and balances, as it relates to the data
> model. I will make this as simple as possible. Most inventory systems
> have an item table, or tables, which describes the items in the
> inventory. Now off of the item table you will have one or more
> transaction tables. One method is to basically track all the positives
> and negatives and then add them up when you create a report. But over a
> period of time, these tables could get pretty large, thus, reporting
> slows down. One method is to track the balance in each record, but this
> means you must have a calculated field in your table. Some schools of
> thought says putting calculated fields in a table is a taboo. Another
> method is to have a table that tracks all of your increases in your
> inventory and then a table that tracks your decreases in inventory, then
> does the calculating off these two tables. And I have seen several
> other variations of these "simplistic" examples I described. I have
> used variations of these methods based on the type and size of the
> system. Now my question: What is the "best" method? Please let me
> know from your experiences of what has worked the best for you.
> Thank you.
> --
> Gary A. Harris, CSP, BS