Posted by --CELKO-- on 01/31/06 03:11

>> I realize there are many commercial (as well as some non-commercial) inventory offerings, but my client has specific requirements that would necessitate significant customization of any off-the-shelf application. In the end, we decided it would be more feasible to build one to our specifications. <<

I am always surprised that programmers feel that their application in a
commercial area is unique in all the world, so they must write code
instead of buy it off the shelf. Is this the first wholesale business
model in the world? Of course not! What makes it unique in all the
world?

But I have only been doing this for 35+ years, so what do I know? The
only unique systems that had to be done from scratch systems I worked
on were Military. There is no off-the-shelf "atomic bomb package" on
the market -- as far as I know.

>> What I am looking for are a list of best practices/recommendations for the
architecture and modeling of an inventory database. <<

Since you did not tell us anything that makes this inventory unique,
who knows? I would get some of Hay's DB patterns as a start. Then
hang around inventory people on the newsgroups.

For truly weird inventory problems, go to the grocery industry. They
buy and sell in more werid units (buy fruit in metric tons, sell by the
each, etc.), have spoilage, health regulations, worry about the UPC
change this year, etc. And they have packages, so you must be in a
real mess!!

>> 1. Track only transactions (products received, products shipped, etc.) and
calculate the stock based on the sum of all transactions. But as
transaction
volume accumulates over time, this would get very slow and cumbersome.
<<

This does not include spoilage and shrinkage. And, yes, you have
account for all the transactions, too. If Wal-Mart does not have a
problem with speed, how big is your problem that you worry about it?

>> 2. Have a table of "units in stock" and add and subtract to it as transactions occur. This has the advantage of always providing an instant snapshot of inventory levels. But it makes it more difficult to manage changes or corrections to a transaction once it has been entered. <<

Unh? How do you think an inventory works if you do not post
transactions against it? This is RDBMS, not a paper file system that
gets resolved once a week by a human bookkeeper. Of course changes are
done immediately.

3. A combination of the above two concepts. Choose a point in time (for
example, when the warehouse inventory is audited) and capture that in a
(semi) "static" table. After that, transactions are summed as needed
against those static numbers. The next time the warehouse is audited,
the "static" table is updated and all prior transactions are archived.
New transactions are once again summed against the most recent audit
numbers. Of course, this would mean halting operations at the time of
the audit (probably not an issue for my client's business). <<

Physical inventory is done when a transaction fails or when a flag
occurs (time or event). You have to do it, no matter what the
transaction system is doing. You also need to learn how to model a
history in SQL.

[Back to original message]