We have created our own in house solution to handle all our sales, inventory, customers, payroll, etc. Currently we are adding some new features to our solution.

I have read on these forums many times that all inventory systems need to be transactional. They need to record all stock modifications (received and sold). Qur current inventory database tracks the amount of stock with one quantity field. We use scripts to add and subtract quantities when needed. Therefore, no transactional records.

My question is, what are the benefits of having a transactional inventory? What does all this data, we will accumulate, show us. At first I thought that it would show us what we have sold during certain times of the year, but we can get that from our current sales line items database. The line items file shows all the information (when ,how much, to whom, etc) that we would need.

If someone could enlighten me, before I dive-in to this any further. I don't want this to be a another file that just grows and grows in file size with no use.

Thank you

You don't have to have a history of inventory movements. As you noted, you could construct inventory out-flow by looking at your sales table and inventory in-flow by looking at your puchases table. The down-side to that is you have to do a lot of stiching together to get a picture of your inventory over time. If you changed your inventory db to show movements in and out, it's much easier to build a report that shows both sales and purchasing trends with respect to inventory. Also, how do you currently process returns (if you allow them)?

Also, how do you currently process returns (if you allow them)?

Answering your question first, we are working on one now. We setup one first that still has some holes that doesn't completely cover all aspects of a true return system. Presently, in regards to inventory, it does not make changes to inventory quantities.

You don't have to have a history of inventory movements. As you noted, you could construct inventory out-flow by looking at your sales table and inventory in-flow by looking at your puchases table. The down-side to that is you have to do a lot of stiching together to get a picture of your inventory over time. If you changed your inventory db to show movements in and out, it's much easier to build a report that shows both sales and purchasing trends with respect to inventory.

So the only reason that you see is that it makes it easier, development wise, to gather inventory trends (purchases and sales) and put it into a report.

If I decided to go ahead with this transactional system, what would be the proper way to get current quantity totals for each item from this file? Would I just create a calculation field that sums up all the entered quantities for that Item through a relation? Then copy that data into a another static field for display.

One of my worries with this method is, if the database becomes huge, and it will because our current inventory file has about 20000 items, it will become very slow at calculating the totals.

Also, how would we handle end of year inventory recounts? Do we erase all the quantity transaction records and start over with the new counted amounts?

If I decided to go ahead with this transactional system, what would be the proper way to get current quantity totals for each item from this file? Would I just create a calculation field that sums up all the entered quantities for that Item through a relation? Then copy that data into a another static field for display.

You need a Products file which is a bit different than your Inventory file. Your Product file contains 1 record for each product you sell. This is where the qty on hand would be stored. You'd have a relationship from Product to Inventory based on product ID. Any changes to the Inventory file for a product would be scripted and you'd set the Qty field in Products based on the sum of all the qtys (in and out) of the related records in Inventory. This way your qty on hand field can be indexed.

One of my worries with this method is, if the database becomes huge, and it will because our current inventory file has about 20000 items, it will become very slow at calculating the totals.

Indeed it would, but since you'd script changes, you'd calculate the totals only when you absolutely needed to.

Also, how would we handle end of year inventory recounts? Do we erase all the quantity transaction records and start over with the new counted amounts?

You'd need a qty adjust record in your Inventory file. If your Inventory file at year's end says you should have 5000 of some product, but a physical count produces 4500, you'd create an additional record and enter in -500 to adjust the total. Ideally you'll have movement codes in your inventory file (a code for products sold, purchased, damaged, returned, etc), and one of them would be an overall adjustment code.

One other option to reduce the number of transaction records which need to be processed, when calculating the current inventory, is to archive the transaction records on a regular schedule and replace the archived transaction records with an opening balance record. You could archive the transactions yearly, monthly or daily as necessary.

A transaction based system is a model of real life. Each transaction record represents chronologically, each thing that happened in the business. In general, model based systems are logical, easy to follow, and easy to modify to reflect changes in how your business operates. When you develop a solution that does not follow a model of your business, it may work, but you run the risk of having the whole thing fall apart when the business makes a small change to operating procedure.