Inventory

ajj3085 posted on Tuesday, May 01, 2007

Hi,

I'm looking to the future right now, and one of the tasks I will need to handle is inventory.  Right now I'm still at a point where I'm thinking about how best to store changes to inventory.  I have several options.

1.  Just track the current inventory, and keep an audit record of past inventory values (and who changed it).

2.  Track the intial inventory level, and then log changes.  Current inventory would take the initial value and add in the logged changes (plus or minus) to get the current value.  I imagine that there could also be a month end inventory, which would update the inital value and remove the logs for the that month.

What other options are out there?  What's the 'best practice' for handling this?  I imagine there's a standard solution to this out there already, but I seem unable to locate it..

SonOfPirate replied on Tuesday, May 01, 2007

As usual, it all depends on your use-cases.  In my experience, the vast majority of the time it is the current inventory levels that are the most important.  As such, I typically design my apps to represent the current state of the objects (i.e. store the current count).  Actions corresponding to shipping and receiving activities will increment or decrement the count.  Having a log that records these transactions allows you to generate reports based on history, volumes over time, etc.

Using the current level also allows you to incorporate logic to flag when the count drops below a certain level possibly for re-ordering purposes, etc.  You wouldn't want to have to re-calculate the current level based on some initial value and all of the pluses and minues that have taken place since - especially if the app is in use for an extended period of time (like a couple of years!).

HTH

 

ajj3085 replied on Tuesday, May 01, 2007

Yes, most current values are most often used.  But I know historical values would be as well, and I'd like to start so that if I needed to use something like Analysis services on this data I have captured enough detail to do so. 

The low level threshold is something I am interested in looking at, because I am pretty sure I will be asked to impoement an automated alert when that happens (using Notification Services, of course).

The second option where I don't update the initial value each transaction would have a process that runs (likely once a month) that will update the inital value so that the current log never is older than a months time.. and I'm sure Sql Server can handle doing some addition and subtraction!

Andy

SonOfPirate replied on Tuesday, May 01, 2007

Again, I would lean towards maintaining the current level.  The easiest way to wire-in Notification Services would be to simply monitor this column and trigger the event when it drops below some threshold.  Going the other way, you have the aggregation logic in your application and in NS.

As for the Analysis Services, etc., that sounds like a specialized behavior and the additional overhead associated with aggregating the data would be reasonable.

And having a background process that updates the value... sounds like trying to cut a tree down with a hatchet!

Good luck. HTH

 

hurcane replied on Tuesday, May 01, 2007

I don't know the scope of your inventory application, but my company's product helps manage inventory for industrial distributors. We deal with inventory that is located in multiple locations with multiple "bin locations" within each location. Our average user has about 20000 SKUs with 3-5 warehouses.

I can definitely recommend keeping tabs on current values, but also maintaining a log of transactions. Day-to-day business needs are to see existing quantity values for on-hand, commitments to customers, releases for shipping, incoming vendor orders, transfers between locations, etc. These are frequent, and I would recommend maintaining "buckets" for these values.

Yes, buckets have the risk of not being updated properly. For us, that risk is worth it when compared to the scalability penalty you get from constantly aggregating open customer orders, purchase orders, picking tickets, etc. Our users typically execute tons of status queries when compared to the number of order transactions that are processed in a single day. This justifies the cost of updating the buckets with each transaction.

For historical data, most of our customers accept that the inventory aging report, which calculates numerous point-in-time values from the transaction table, should be run at night. Eventually, we're going to have a customer whose transaction volume is too high to generate analysis data on demand. In that case, an OLAP database is going to be absolutely necessary, and it will get its data from the log.

As with any recommendation in the business world, your mileage may vary. :-)

ajj3085 replied on Wednesday, May 02, 2007

Sounds like a combination of the two methods.

We only have a single warehouse, but we will need to track bin locations.  Fortunately I don't think we'll have the scale you need to deal with anytime soon, as we are a manufactoring company, so the inventory is the products we sell and the parts we used to make those products.

I would think with transactions the risk of a 'bucket' getting the wrong value would be low. 

Thanks for the feedback from everyone, I think I have an idea how to proceed.  Although I'm still curious what the 'standard practice' is for inventory and databases.

JoeFallon1 replied on Wednesday, May 02, 2007

I fully agree with the use of a bucket to maintain the current value.

The problems you will find with keeping that value accurate have absolutely nothing to do with your code. I am sure you will code all the transactions correctly. The problem is always in the users of the app. Or rather those that don't use it. Inventory disappears under the best of circumstances because people take things out of stock without entering the data in your app. This is why adjustments to inventory are a critical transaction that you must implement. e.g. Your app says there are 5 on the shelf but when they go to pick 1 the see there are only 3 on the shelf. They need to be able to adjust the current value down by 2 as an inventory loss.

Joe

 

ajj3085 replied on Wednesday, May 02, 2007

Joe,

Yes, that would be a problem.  I think the current tread though is that they want to deduct from inventory when the order is ready to be processed.  So current level in the system would read three when there may be in fact 10. 

The problem they hope to address is that right now an order is started by gathering the parts to make the order (much of our work is custom).  If the people gathering the parts discover they don't have enough of a particular part, they make a note and continue gathering parts.  Then they put all the parts in a bin and put the order aside and fill out a slip indicating how many parts they still need.  Another order comes along and they need some of the same parts, but fewer... and now those parts are sitting with another order.

The thought is that if the order creation deducts inventory, we can eliminate the on hold bins, because you could see from the system that we don't have enough parts to fill the order, and move on to the next one without gathering anything.

Bayu replied on Wednesday, May 02, 2007

[OFF-TOPIC]

A, the classical paradigm shift! Pull vs push.
Optimizing the number of orders for which parts are gathered vs optimizing the number of orders which can be completed and shipped.

Ever read Goldratt?

[/OFF-TOPIC]

[ON-TOPIC]

Sorry. Nothing much to say actually. :-D
I was just happy to see some clever innovation that is going to be of great value when implemented.

Apologies for wasting precious bytes on this. ;-p

[/ON-TOPIC]