Inventory System

[JZS]

Hello everyone,
I have always wanted to know how developers go about solving certain issues that do not have one specific or standard solution, so I wanted to know how better it could have been done, differently than the way I did.

Now I have one issue about inventory and items added to storage in different shipments (Transactions In) by indicating the cost of each batch.

When items are accumulated from multiple shipments/batches the cost is not necessarily the same. So let's say we have ItemA, adding batch-I of 150 items at the cost $1800 ($12 each), then later, batch-II of 250 items at the cost $3,625 (14.5 each), then batch-III of 100 items at the cost $1300 (13 each).

I know there are inventory methods (FIFO/LIFO/Average), but that is not the issue.

My question is, considering FIFO method is used, how do I record and calculate the Transactions Out?

For instance, when all the items of Batch-I fully consumed and then Batch-II is used, how does the cost get calculated programmatically:

Transactions In:
                                  Qty  |  Unit Cost $
Batch-I                      150  |     12
Batch-II                      250 |     14.5
Batch-III                     100 |     13

Transactions Out:
                                   Qty  | Total Cost $
Operation-I                 200 | 2,525 (150X12 + 50X14.5)
Operation-II                100 | 1,450 (remaining 100 items of Batch-II)
and so on...

What I am currently doing is recalculating Operations out of the Batches every time I need to show the balance, by looping through Operations and subtract from the batches until the most recent operation, but there is something that does not feel right.

Then I start thinking may be I need to improve some how, MAY BE, by record the remaining of item balance as static value and start calculating from that point, but this creates problem when editing the amounts/Qty of old Batches/Operations?

So has anyone got any experience in a way that better works on such systems?

Thanks in advance.

[taazz]

As far as I know those are the only two methods. There are of course a number of different implementations. You are going to have multiple points to address and those depend on your specific implementation for example do you save the batch ID(s) with each voucher? when a voucher spreads in two batches and the customer returns part but not all the products he purchased which batch do you debit? what happens when a return is made for batch 1 or 2 when the inventory is already empty? do you use a double ledger logic or direct updates? Those are most of the points you have to address during the design of the product. In sort both methods are acceptable and which one you choose depends on the number of records you have. the dynamic method might serve you well if the number of records is in the 5 digit area but it might get you in trouble if it crosses over to 6 digits or more.

[JZS]

Thank you Taazz.

when a voucher spreads in two batches and the customer returns part but not all the products he purchased which batch do you debit? what happens when a return is made for batch 1 or 2 when the inventory is already empty?

I did it once, based on the client preference, by giving the user the option to select the suitable batch to debit.

double ledger logic or direct updates?

For medium size company I go with direct updates. Do you suggest otherwise?

the dynamic method might serve you well if the number of records is in the 5 digit area but it might get you in trouble if it crosses over to 6 digits or more.

This is what I am afraid of. Getting a phone call after couple of months, to discover the process has become huge to manage dynamically.

[taazz]

Thank you Taazz.

when a voucher spreads in two batches and the customer returns part but not all the products he purchased which batch do you debit? what happens when a return is made for batch 1 or 2 when the inventory is already empty?

I did it once, based on the client preference, by giving the user the option to select the suitable batch to debit.

So when a customer returns a quantity back, where it is credited to?

double ledger logic or direct updates?

For medium size company I go with direct updates. Do you suggest otherwise?

The double ledger methodology is a good fit for the dynamic method, since there are no direct updates allowed, each change in the database is a new record. This makes it easier to run aggregated functions  (sum, avg etc) in the table and produces faster and easier results. I guess I would use it in most inventory applications but I can't say that I prefer it or recommend it.

the dynamic method might serve you well if the number of records is in the 5 digit area but it might get you in trouble if it crosses over to 6 digits or more.

This is what I am afraid of. Getting a phone call after couple of months, to discover the process has become huge to manage dynamically.

There are at least 2 methods to overcome any speed/efficiency problems
   1) running total. You add an extra column in the table as running total and update it with each insert/delete/update.
   2) Period total (quarter,year etc). Once/twice ( or more times) a year you calculate the total (usually in a different table) to be used from that date on as the previous total. You need to make sure that the updates to previous dates are as minimal as possible yet the records after that date are not too many.

In any case try to avoid client side aggregated functions as much as possible this will speed things considerably both in development and application responsiveness.

[sky_khan]

In addition to what taazz said, this is where you may need stored procedures on server otherwise you may need to fetch all relevant historical records to client in order to calculate. Even using running total or periodic total there may be lots of records depending on client.

[JZS]

...
So when a customer returns a quantity back, where it is credited to?

Either to an existing batch (if still active) or create a new batch (flagged as items returned) with same cost of the parent batch.

But that would be subjected to the client preference.

In addition to what taazz said, this is where you may need stored procedures on server otherwise you may need to fetch all relevant historical records to client in order to calculate. Even using running total or periodic total there may be lots of records depending on client.

Stored procedure sounds good.