Algorithm resources for Inventory DB

I'm rewriting our inventory db (Access, ASP & VBScript) using SQL Server, ASP.NET & VB.NET. I'm currently defining the specs for it (we have a really good idea of changes we want to make), but I've never done an inventory setup. I'm looking for resources (books, magazine articles, algorithms, etc.) to avoid stupid mistakes, bad decisions, etc. (as much as possible).
Could anyone recommend such resources?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I guess my first question would be - 'What's wrong with the current inventory db setup'.  Remember, this forum is intended to help with specific problems in specific applications.  If you want a design...
SCHaleAuthor Commented:
What's wrong?

1) No documentation. None, nada, zip.

2) No attempt at normalization. The part number and description columns occur, in their entirety, in 11 different tables, even though there is a parts table with an identity, part number and description, and is kept current.

3) In one table, I have the column names Quantity, qty1, qty2, qty3, qty4. After 2 days of analyzing thousands of lines of VBscript, VBA, Access queries, etc., I discovered that qty3 and qty4 were not used. Ever.

4) No error checking. If a transaction fails (we use barcode scanners) due to a network error, no notification is given to the user. It simply redirects to a default screen, deceiving the user into believing the operation was successful.

5) Even though the program records user transactions, no database transactions (BeginTrans, etc.) are used, even though we are modifying multiple tables.

6) An empty table is referenced in a query in one place, but NEVER modified, so it is always empty.

7) Code is not self-documenting. I have statements of this quality:
'Add 1 to X
which tells me squat.

I could go on and on about lesser things, but the bottom line is that if I were both drunk and stoned out of my mind. I could do better job than this.
 Thank you for letting me vent.

I may not have chosen the proper forum for this *soft* question (I'm not expecting a hard, definitive answer). However, I would expect someone in the database area to have some experience with inventory or a warehouse management system.
I'm not asking for someone to design the thing for me. That's my job. All I'm asking for is assistance getting started in the right direction.

Please follow this link. You seem to have some potential in the "venting" area of expersise... http:/Q_22934098.html

This being said, you obviously have a problem. I have met this before and the solution is always: rewrite.

Tough, but that's life. From the little information you provide, I could probably infer about six dozen other characteristics of the database you are dealing with. It's really no use. Don't even bother to study the database structure or the code, analyze only the interface and the input/output model. Tracking the life and fate of field "qty4" or figuring out the true meaning of "X = X + 1" has been a waste of time, as you well know.

Let's talk about inventories.

An "inventory DB" is almost always really a stock management program. The inventory itself is both an input and an output of the application. You will normally track changes in quantities, with occasional additions to the catalog of tracked items. Basically, you will start with an initial inventory (actual counts at a given start date) and then track changes.

This is very much like an account management. Each change in the data is in fact a record adding or subtracting a quantity in one category. This allows you to generate new up to date inventories on demand.

At some regular intervals, you will have the users print out the inventory and check it against the real quantities. Any errors will then be recorded (and perhaps managed in real life as well: suing for theft comes to mind) yielding a new base inventory. This will replace any previous calculated inventory and serve as base for the next life cycle.

Does that help?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I could go on and on about lesser things, but the bottom line is that if I were both drunk and stoned out of my mind. I could do better job than this.
 Thank you for letting me vent.>>

  What a mess to clean up.  I would strongly second harfanq's recommendation and do a total rewrite.  I'd also like to add that the most flexiable system I've found over the years for inventories is one that uses a "movement" table.  Basically a log of all the in and out transactions and what gives you your stock on hand qty.  So you'd have:

tblItems - One record per item

tblTransactions - One record per in/out of inventory.
CreatedOn - D/T
CreatedBy - User ID
EffectiveDate - D/T
ProgramID - Program/module that generated the transaction.
DocumentRefNumber - ie. Packslip or Traveler if a receipt, cycle count number, work order number, etc - whatever was the cause for the transaction to occur.
LotID  - Lot to or from

tblWarehouses - One record per warehouse

tblLocations - One record per allowed location

tblLots - One record per lot
LotID - PK
DateCreated - D/T
LastReceipt - D/T
LastIssue - D/T

  That's pretty rough, but in general outline is how most inventory systems are written.  There are of course a wide amount of variations based around this theme.  Some depends on what your inventorying.  For example, if the items your dealing with have a shelf life, then there'd be a few more fields, but it would still be the same in general outline.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Yes, the core idea is one Items table and one Transactions table. In addition to Jim's suggestions, I've always had an Inventory table as well. Technically, the base inventory could be recorded in Transactions as special "initial quantities" records, but that's an implementation issue. Conceptually, the base inventory exists as a separate object.

Let's take a wine cellar, for example.

You will start by creating your current inventory: creating items in the Items table and initial counts in the Inventory table, by physically walking through the cellar. That would be your current stock at 2007-11-02. You then track changes: quantities on order, deliveries arriving at and bottles leaving the cellar. To get a current inventory at any time, you will use the base inventory and totals computed from the transactions table.

Next summer, you'll notice that the system believes you have 24 bottles of "Pinot Gris de Dardagny 2001", but you find only 19. Perhaps it's time to redo the inventory.

You will then have the application calculate an inventory, freeze all transactions over a week-end, and check it against the true content of the cellar. That's how you discover three bottles "Château Margaux 1984": they had been sold, but something else must have been delivered instead... Once corrected, this becomes Inventory 2008-06-01, and transactions before that date are archived or deleted.

Depending on the complexity of the stock (several locations, etc) you might need several partial inventories, perhaps with different base dates as well.

> ... to avoid stupid mistakes, bad decisions, etc.

An inventory tracking system is fundamentally an "append only" application. The first mistake would be to allow anyone to edit the current base inventory or past transactions. If a wrong transaction is entered or if an error is discovered, the user or a manager will have to enter an opposite movement as correction. This is exactly how your bank account is managed, by the way.

I should add that we are perhaps overdoing it here... For example most IT equipment tracking inventories do not record movements at all. Instead the "inventory" is edited directly in order to always reflect the current location and state of each piece of equipment. That's the classical difference between dynamic and historical data management.

The second mistake is to overdo it. If you order 24 bottles of "Petite Arvine de Chamoson 2003" and are delivered 12 bottles of 2003 and 12 of 2004, you should be able to record that on the fly: create the new item 2004 and split the order into two deliveries. I've seen a system where the order had to be canceled, the new "item" created and validated by Accounting, and two new orders created (four business days later). In effect, the procedural model did not allow for many real life situations.

In a similar vein, it should be simple and quick to record a broken bottle, a gift to a good customer, a bottle opened for "degustation". Again, I've seen a system where (transposing to this example), one would have to walk up to the restaurant, turn on the cash register, order the bottle and then cancel the bill in order to track the change in the stock. Although it makes sense in a way, the end result was that more often than not, the user would think "I'll do that later today" and ended up forgetting... It's better to see "one bottle gone, cheers!" than no record at all.

I see Markus is no stranger to good wine, and the methods used to 'track the flow' thereof;-).  In the end, it boils down to tracking transactions, and the hoops you have to jump thru to get each transaction 'just right' will be inversely proportional to its utility (by your users) as an inventory mgt system.  Spend some time on figuring out what 'just right' means.
SCHaleAuthor Commented:
Thanks to everyone. Yes, this situation screams "Rewrite me!!! PleasePleasePleasePleasePlease!!!"

 FYI, our department is a distribution system for machine tool parts. We have about 20,000 part numbers (not the item count) in stock and Corporate wants to increase stock levels. By Christmas, I'm guessing at 30,000 p/n's. I'll patch problems as they occur, but this is the limit of my involvement with the old code. I agree with all your suggestions and comments. This has been extremely beneficial.

In case you are curious:

Everything will be written as VB.Net objects, such as:

Receiving - Recording data such as manifest number, part number, description, quantity and date of receipt when parts are received from an external source, including adding new part numbers.

Stocking - Printing a stocking list (from Receiving) and physically placing parts into a marked container or area, and recording the location for later retrieval, both physical & informational.

Delivering - Recording data regarding items to be picked and shipped, such as the delivery ticket number, part number, quantity, etc...

Picking - Printing a pick list (from Delivery) and physically removing parts from inventory to be packed and shipped.

Relocation - Selecting items to be physically moved from its current location to another and recording the new location for later retrieval, both physical & informational.

Physical Adjustment - Updating the actual inventory quantity, regardless of recorded quantities, to account for shipping errors, loss & damage to inventory.

Returns - To record return items and control their reintroduction into the inventory.

Daily achievements - Items shipped, airfreights received, etc. for MBO analysis.

Reports/Analyses - As needed.

I will record every activity that modifies any data. I expect (at this point) to use separate tables for receipts, picks, relocations, etc., even though they will all be essentially the same layout. I can always use a query to pull these together.

harfang, I like the analogy to a wine inventory. It would have been more relevant to use something with pulltabs, but wine works just fine.

I would love to keep you folks talking (I've learned a lot from this little exchange), but to be fair, I need to put this question to rest, as you've all answered the question, in one way or another.

So here's how I want to divvy up the points:

GRayL: 50 points for asking a very important question: "What's wrong with the current inventory db setup". I could actually feel my blood pressure drop as I typed my response.

I'll split the remaining points evenly between harfang and JDettman: 225 points each, both for such excellent suggestions.
SCHaleAuthor Commented:
P.S.: I'll keep an eye on this question, just in case anyone wants to comment. I'm an information slut and I'll use any help I can get.
In line with my earlier suggestion, add this to your list:

Archival. Removing old transaction (after one year, five years, etc.). This poses the requisite: transactions should become obsolete after a while and no longer needed to produce a current inventory. Even if it's not a table per se, the concept of a "base inventory" should exist in your object model.

I like the "Relocation" item. I analyzed the database needs for a museum of archaeology and history, and so we had additional tasks such as "Exhibits", "Restorations", "Study", all connected to the "Relocation" business. Fun stuff!

Thanks and good luck with your project!
SCHaleAuthor Commented:
harfang: Yep, an Archival process is definitely needed. The current method uses transactions to serve as a secondary inventory count (add receipts, subtract picks, add/subtract Physical Adj., etc.). We have transactions that are 4 years old, are "zeroed" out, and only serve to take up space. If I were to rebuild the current db, I would find a way to move these out to a different database altogether. This would certainly improve performance.

Again, Thanks.
Just a different table not a different db, so you can 're-build' if necessary??
SCHaleAuthor Commented:
No, I think a different database altogether. Rebuilding, at this point, doesn't seem to be an issue. A separate db would primarily be for simplified backups: Say, run an archive query once a month, back it up and forget about until next month. Meanwhile, the primary db would shrink, making weekly full backups and daily incremental backups quicker. Speed is an issue because we have an ISN (Incredibly Slow Network). The wireless barcode scanners, for whatever reason, won't work on our fast wireless network, so we have to use a slower speed to keep things running.
However, I haven't ruled out anything just yet, because I haven't finished the sys. analysis. When that's done, I'll have a clearer picture of my options and exactly what I need to do. I hate using slick phrases (my concept of a phrase may not be your concept of that phrase, so we don't really communicate, do we?), but "Plan your work, then work your plan" seems to be very apropos for programming.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.