Inventory Tracking Program - Best Practices 4

[JoeAtWork]

This isn't really a VB question, more of a programming techniques type question (i.e. could apply in any language).

To explain, I am in the early stages of doing a rewrite of an awful Access POS/Inventory program into .NET/SQL 2005.

The program keeps track of stock with updates like the following:

Product.Quantity = Product.Quantity + NewStock

Needless to say, it is often inaccurate, and I have no way of tracking how Quantity became some ridiculous value (even to negative sometimes).

I know I want to use transactions, I want to know how best to implement that, and other best practices.

Links to sites and book suggestions are welcome.

 

[SkipVought]

Hi,

Product.Quantity = Product.Quantity + NewStock

...is not a transaction.

After an audit, you may have inventory adjustment transactions to reconcile the data with the inventory count.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[harebrain]

>some ridiculous value

Sounds like a combination of bad programming and magic numbers.

When I worked for a distributor, the guy who owned the company always was coming up with ideas like, "Let's put all 9s in this field and that will mean..." Or negative numbers, or "starts with 8" or... Arrrgh! The only thing he obstinately resisted was creating new data fields to mean whatever he wanted. "Hey, the guys who wrote this thing were geniuses, and you're not going to mess-up their system." We're up against that kind of thinking.

Anyway, (1) no magic numbers, and (2) program defensively: when you can anticipate bad values, such as negative quantities-on-hand, throw an error and reject the transaction.

And I think you and Skip were using the word transaction with two different meanings.

 

[SkipVought]

Transactions: Puts and takes.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[MichaelRed]

on a POS system, to reject a "transaction" (regardless of the translation) is (may be?) the wtong thing. example: you are at the register with a customer who wants to buy the widget. The inventory system (POS machine) says thsy are not in stock. The question is, do you want to accept the sale - - - or refuse it to maintain the inventory integrity? ok, it is a rhetorical question.

The point being that all inventory systems have inaccuracies (lost strayed and stolen, not to mention the mysteriously 're-found' things from two yers ago). That is what manual inventory is all about. Permit the occassional manula correction(s) to put you back on track.

The 'better' systems allow the transfer to and from a dummy / holding / limbo accout so that you can reasonably keep track of the "shrinkage" for other purposes. The cheap ones have you simply re-start the process with the new inventory from the counting. This discards any possability of tracking the shrinkage and encourages the introduction of additional (and unrecoverable errors).



MichaelRed

 

[JoeAtWork]

Um, I must have phrased my question badly, because people are misinterpreting what I meant.

I know that the Quantity + NewStock is not a transaction. That was my example of the bad code I will be replacing.

I plan on changing it to transactions (INS and OUTS, or PUTS and TAKES as Skip says).

I know that every inventory system will be off from actual manual counts. However, with the transactional system at least I am able to show an audit trail as to how the amount got to be where it is.

The point about refusing the customer to buy at the counter is conceded, but for this particular application isn't an issue as the customer does not pick out the items himself. Rather, the items are fetched for him (these items are for home renovation and tend to be big and heavy). And because some of the items are special ordered, it may actually be an error to allow them to take something even it's on hand as it's already reserved for another customer.

Anyways, any links or books for best practices from the programming aspect? There's tons of stuff on the general topic of inventory management, but I can't find things like suggestions on how to set up database tables, or algorithms for searching, updating, etc.

 

[harebrain]

The question is, do you want to accept the sale - - - or refuse it to maintain the inventory integrity?

The point was to be able to correct inventory as-you-go, then proceed with the sale. (Don't continue to work with data that you know is bad.) When we did our annual manual inventory, we knew our inventory was accurate exactly once a year, until the beginning of the next business day.

 

[BobRodes]

Ok. First, answer as best you can the question "What do you want to do?" Then work on "How are you going to do it?"

Your first item should be a preliminary problem statement, which identifies the problem in detail and defines the solution to it in business terms. This will begin to answer the first question. Once you have that question nailed down pretty well (there will always be changes; answering the second question will give rise to business issues that need to be solved) you can begin working on the design. In particular, design out your database schema ("how are you going to store your data?") and your business objects. Work on presentation last, since you need to know what you're going to present before you present it.

Keep in mind that an iterative and incremental design process is the most natural. Take multiple passes at the entire application, adding more detail as you go. This allows for revision if you see that your original design ideas missed things.

That's an overview of my take on the SDLC. If you have specific concerns, perhaps you will voice them as such and I'll attempt to address them.

HTH

Bob

 

[JoeAtWork]

Well, I realize now my initial post should have simply stated "can anyone recommend links and books on the topic of best practices for creating an inventory management system".

I have the requirements defined, and I am in the design phase of SDLC, and therefore I'm researching best practices so that:
1. I don't reinvent the wheel
2. I use industry standard practices
3. I might find some good design patterns that are better than the "workable" solutions I would invent by myself

I've given out stars to all who took the time to respond. Next time I will try not to sound like such a Noob

 

[BobRodes]

Googling to "inventory management best practice" yields a great number of links and books, including one from Wiley Publishing called "Best Practice in Inventory Management" (

http://www.wiley.com/WileyCDA/WileyTitle/productCd-0471253413.html)

that looks like it might be good.

HTH

Bob