Normalized Inventory Database

[Seth Schrock]

I've started thinking about creating an inventory database, which is something that I've never done before. I definitely want it to be normalized. But my question is the storing of current inventory numbers. My first instinct would be to always perform the math of add all new shipments minus sales equals current inventory so that there is no storage of a calculated value. However, say 10 years down the road, that could be a lot of math happening for many products and result in a serious performance hit. As I said before, I've never done this, so I'm just assuming about the performance hit. Is this a real concern, or should I not worry about it?

I have heard that with banking databases (similar in that there is a lot of ins and outs changing the balance of the account) they store the balance of each account at the end of the processing day. Is this the best way to do it? Just looking for some general direction from someone who has more experience than me.

[jforbes]

The best I've seen in practice is to do both. Both store the current Inventory Levels as well as have a routine that calculates the Inventory Levels on the fly. This gives the flexibility of recalculating the levels as needed, but also having a level to work with readily at hand.

In the Transaction Table, there would also be Transaction Types to Add and Remove Inventory as well as a third type to Set the Inventory Level (with a reason code). This way, you can archive Transactions and create a Set Level Record that would take place of all the Historical Transaction Records. This can be done after a Physical Inventory (where Items are physically counted) or as a part Year End. Ultimately the Set Inventory Level Transaction would allow you to shrink your Transaction Table as needed, saving time on calculations.

The routine to calculate the Inventory Levels could be parameterized to receive an Item Number, to give the flexibility of recalculating the level of a specific Item or Group of Items. If you are using SQL Server as the backend, you could call the calculation in the Transaction Table Insert/Update Trigger which would keep your Inventory Levels current pretty much no matter what.

A subset of suggested Tables would be (at the most basic level):
Item - Detail on an Inventoriable Item.
Warehouse - Warehouse locations, generally a set of shelves where an Item would be inventoried once and only once. Sometimes there are multiple Warehouses at a single street address so that inventory can be moved between Warehouses. For example, Receive Items into Distributor Stock and then move them to Production Stock as needed so that a machine could be built.
ItemWarehouse - This is where the Inventory Levels would be stored. It's a many-to-many between Items and Warehouses. The Transactions Table would reference this Table for the Source and Destination of Item Transfers.
Site - A Site is a Physical Building (or campus), typically with multiple Warehouses. A Warehouse would have a Foreign Key to the Site Table. You may not need this if you aren't planning on having Locations on different sides of town or in different States.

There's a lot more to this, which I'm sure you will are quite capable of tackling. Like always, we are here.

[Seth Schrock]

Thanks for that. I also found Allen Browne's little tutorial about it. It brought up some points that basically force you to perform a count periodically: items go missing from inventory. Between your suggestions and Allen's article, I think that I can do what I need to.

Thanks again.