Rqst for Inventory Database Best Practices

I am developing an inventory database in SQL Server. 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.

What I am looking for are a list of best practices/recommendations for the
architecture and modeling of an inventory database. This inventory
application will be for managing a wholesale products operation. It must
manage purchase orders to the manufacturer of the products, sales invoices
for the retail customers of the products, as well as manage product stock
levels in the warehouse. We will need a number of reports, including:

1. What products are in stock.
2. What products are on order from the manufacturer.
3. What products are needed to fulfill outstanding sales.
4. Warehouse operations (e.g., receipt of delivery, inspection, add to
inventory, pull from inventory, packing lists, invoices, return to
inventory, etc.)
5. Sales analysis (e.g., product velocity, sales by associate, etc.)

My main question is regarding the managing of products in stock. As I see it
there are three ways of accomplishing this:

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.

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.

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).

Recommendations? Suggestions? Comments?

(Btw, I've tried to find this info in books and online, but so far have not
found anything that makes specific inventory system design recommendations.
If you know of a good reference, please let me know.)

Thanks in advance!
Michael

On Mon, 30 Jan 2006 01:49:09 -0800, DeepDiver wrote:

(snip)

Replied in public.microsoft.sqlserver.programming.

Please ask your questions in one group only. And if you really feel that
it is necessary to post to two groups, use a cross-post, not two
seperate messages.

--
Hugo Kornelis, SQL Server MVP

>> I realize there are many commercial (as well as some non-commercial) inve
ntory offerings, but my client has specific requirements that would necessit
ate 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.
 
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!!
 
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?
 
nce 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.

"Hugo Kornelis" <hugo@perFact.REMOVETHIS.info> wrote in message
 news:p07tt11n3h6huub
 163im1i4bmdg2qcp3v2@
4ax.com...
> On Mon, 30 Jan 2006 01:49:09 -0800, DeepDiver wrote:
>
> (snip)
>
> Replied in public.microsoft.sqlserver.programming.
>
> Please ask your questions in one group only. And if you really feel that
> it is necessary to post to two groups, use a cross-post, not two
> seperate messages.


My apologies. My impression from other newsgroups was that cross-posting is
discouraged (if not shunned). Some newsgroup participants consider
cross-posting akin to newsgroup spamming. That was the reason I posted my
question separately. In any case, I appreciate your response in the
public.microsoft.sqlserver.programming newsgroup, and will reply on topic
there.

Regards,
Michael

On Mon, 30 Jan 2006 18:05:31 -0800, DeepDiver wrote:

>"Hugo Kornelis" <hugo@perFact.REMOVETHIS.info> wrote in message
> news:p07tt11n3h6huub
 163im1i4bmdg2qcp3v2@
4ax.com... 
>
>
>My apologies. My impression from other newsgroups was that cross-posting is
>discouraged (if not shunned).

Hi Michael,

It is. That's why my first advise was to post to one single group.

But the exception to this rule is if a question really "falls between"
two groups (for instance, you get unexpected results when querying SQL
Server from your .NET application and don't even know where to begin
looking - that kind of question can be posted to a SQL Server group and
a .NET group). In those cases, crossposting is allowed.

My comment, though, was about multiposting (posting independent copies
of a message to different groups). Your own post is a great example of
why: some comments are posted in .programming by Patrik, Brian and me;
Joe Celko, probably unaware of this, posts his thoughts here. Et voila,
we now have two discussions about the same subject; some participants
won't know what is already advised in the other discussion; others have
to go through more trouble to follow both tracks of the discussion.

> Some newsgroup participants consider
>cross-posting akin to newsgroup spamming.

I reserve the word "spamming" for EXCESSIVE cross-posting.

Posting to two groups is sometimes valid. Posting to three is very rare.
Posting to four or more is either a complete Usenet newbie, or a troll
or spammer.

> That was the reason I posted my
>question separately. In any case, I appreciate your response in the
>public.microsoft.sqlserver.programming newsgroup, and will reply on topic
>there.

I'm looking forward for your reply there!

--
Hugo Kornelis, SQL Server MVP