Inventory Control System

[egorzik]

I've designed and run three of four different inventory control systems over the past 13 years or so in Access.
Along the way I have found things that work better than other in reference to accuracy and performance.

Any suggestions on design that works well or that you have implemented?

Specifically I am wondering about table design and query performance.

As an example, I ran one system that stored transactions in different tables:
tblReceived
tblShipped
etc...

In another system I stored them all in one table:


tblInventoryAdjustments

Where each record was identified as a Shipment or Receipt, etc... I could filter the records through query then add and subtract them.

Thoughts from any gurus out there?

NOTE: The system has to support multiple warehouse locations as well as build assemblies tied to bill of materials so that when production is reported it can automatically subtract and add to inventory based on master item to component ratios.

We've reached a point in our company that this needs to be redesigned anyway so I am looking at the design as a whole to maximize performance.

Thanks for considering my question(s)!

[Xipooo]

Normalize, normalize, normalize.

The more separate you make your data, the more efficient your queries will be and smaller your database.

If you have 500 Shipped, and 500 Received with both in a single table.. every query will have to look through 1000 rows to find your data rather than just the 500.

If you can break the information about each shipped and recieved item into their own tables, that is good too. It's better to return 5 columns instead of 8 or 10.

[NTC]

transactions (in/out of inventory) should be a single table