Inventory Database Question

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
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://info_at_proptions.com
        or mailto://GaryH_at_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! =-----