I have been design and developing inventory tracking software for the pass 7 years, and from my experience, the most difficult part of Inventory software is the month end calculation. Any programmer can do that, but if the data grow from 4 GB per month, improper design will just kill the database in less than a year! Let me share some ideas I use to counter such a problem: Idea #1 - Design the system to batch processing. You must admit you can't have all eggs in a basket! While the market is screaming for Realtime software, it is just not practical to invoke the business rules every-time someone entered a transaction.
Any inventory system design in this way will require 10 times processing power, so unless it is a must have feature, I won't recommend to design the system to cater for Realtime processing at all. Idea #2 - Consolidate the processing into daily, weekly and monthly. You need only to store the monthly summary for a large inventory system, but you must process and store weekly and even daily data temporarily.
Every day summary will be consolidated into the weekly summary, then clear daily data for next week daily process. Perform the same with the weekly data after consolidated into the monthly table. If you are using Microsoft SQL server, extra care must be taken when clearing the temporary data.
There are some special way other than SQL "delete from" command to speed up the process! Idea #3 - Report processing. Do not store all report summaries into tables, use the tables as summary cache. Trust me on this, there will be many report to come, and every time you need a report, a summary table will be created, then the database will grow into a monster very fast! Instead, combine daily, weekly and monthly data in the process and use SQL statement to join the different table into logical dataset, and you report from there! Unless there are many people requesting the same report all the time, you might want to consider create data warehouse for the reporting purposes. Idea #4 - Use multi-tier technology. Multi-tier technology allows you to separate the system business rules from processing, may be even taking some of the processing, such as reporting processing from the database server into the application server, load balance the machine processing power and provide better user experience.
There are still many things to consider, but these 4 major ideas should outline the critical requirement of the heavy duty Inventory software. Just keep in mind the design process must always align with the business environment of your client, the volume of data and the business nature will decide how the system should handle the processing, so keep study, study and study until you understand what is your client requirements. Copyright © 2008 - Cynics Software. Feel free to distribute this article, along with the resources box in place without modification.
If you need my help to customize an Inventory System, please feel free to visit my website: Cynis Software - Customization.
