Algorithm resources for Inventory DB

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
x=x+1
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.

LOL

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?
(°v°)

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.

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.

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!
(°v°)

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??

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.