Can anyone help me with this algorithm?(FIFO based inventory)

[doxtor]

Hi all..I'm a little bit confuse with this problem.
I have 3 tables, stock_in, stock_level, and stock_out.
stock_in used for save the data product when there's new purchase.
stock_out used for save the data product out.
stock_level used for count how many stock in warehouse.

stock_in : in_id[PK], product_id, stock_in_unit, stock_in_price, unit_id, supplier_id
stock_out : out_id[PK], out_date, product_id, stock_out_unit, unit_id
stock_level : stock_level_id, in_date, stock_in_unit, stock_in_price, product_id, in_id[FK from stock_in]

When user input the product data, automatically its enter to stock_level. And, when there's stock out, automatically decrease the stock_in_unit on stock_level table with FIFO base.

for example (the column is just, date, stock_in_unit to make easy)
Product purchased (saved to stock_in and stock_level)

08/01/2008          2
08/02/2008          3
08/03/2008          5

Product Out

08/04/2008          7

Because I use FIFO, so, on stock_level table now fill with

08/01/2008          0
08/02/2008          0
08/03/2008          3

I have do until this step, but the problem is :
How about if, for example, the user make a mistake, the product out should be 3, and not 7, how can I do the edit?because, the stock_level table now should be

08/01/2008          0
08/02/2008          2
08/03/2008          5

I'm confuse how the algorithm to do that. Please give any suggestions. All appreciated.

Thank you so much

[doxtor]

can anyone help please?
I'm still stuck to think this problem :(

[pbmods]

Heya, doxtor.

In this case, you'd want the User to be able to manually alter the values. What database software are you using?

[doxtor]

Hi..
Sorry for late in answering. I use mysql database. For language I use php 5.

Thank you so much for replying

[pbmods]

The easiest solution would be to allow the User to add an adjustment (you could add this as a row to the `stock_in` or `stock_out` table with an additional flag [column] that denotes that it is an adjustment).

The other option would be to allow the User to directly edit the `stock_in` and/or `stock_out` tables (set up an interface similarly to how phpMyAdmin does it, for example), and the rebuild the `stock_level` table since that date.

E.g., if the User edits `stock_in` on 2008-06-01, then you would need to run through and recalculate the `stock_level` values where `in_date` >= '2008-06-01'.

This might get somewhat complicated because you have a `stock_in` and a `stock_out` table; you might want to consider combining them into a single `stock_activity ` table; simply add an additional column that you can use to specify the type of the change (shipment, sale, adjustment, return, etc.).

[doxtor]

The easiest solution would be to allow the User to add an adjustment (you could add this as a row to the `stock_in` or `stock_out` table with an additional flag [column] that denotes that it is an adjustment).

This might get somewhat complicated because you have a `stock_in` and a `stock_out` table; you might want to consider combining them into a single `stock_activity ` table; simply add an additional column that you can use to specify the type of the change (shipment, sale, adjustment, return, etc.).

Yes, this become complicated, because i have 3 tables to stock activity. Do you have any suggestion, e.g, how can i use just 1 table for stock in and out, and the adjustment?

E.g., if the User edits `stock_in` on 2008-06-01, then you would need to run through and recalculate the `stock_level` values where `in_date` >= '2008-06-01'.

For stock in adjustment, it's more easy than stock out adjustment. Like you said, just recalculate stock_level where date ....
The biggest problem is on 'stock_out' adjustment. Am I make mistake with 3 tables?

The other option would be to allow the User to directly edit the `stock_in` and/or `stock_out` tables (set up an interface similarly to how phpMyAdmin does it, for example), and the rebuild the `stock_level` table since that date.

Can you explain this option to combine with stock_out adjustment? I think the idea to rebuild the stock_level is good, but, how can adjust the stock_out activity?
sorry if I'm asking to much, because I'm confuse :(
thank you so much