By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,278 Members | 1,359 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,278 IT Pros & Developers. It's quick & easy.

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

P: 8
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.

Expand|Select|Wrap|Line Numbers
  1. stock_in : in_id[PK], product_id, stock_in_unit, stock_in_price, unit_id, supplier_id
  2. stock_out : out_id[PK], out_date, product_id, stock_out_unit, unit_id
  3. 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)
Expand|Select|Wrap|Line Numbers
  1. 08/01/2008          2
  2. 08/02/2008          3
  3. 08/03/2008          5
Product Out
Expand|Select|Wrap|Line Numbers
  1. 08/04/2008          7
Because I use FIFO, so, on stock_level table now fill with
Expand|Select|Wrap|Line Numbers
  1. 08/01/2008          0
  2. 08/02/2008          0
  3. 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
Expand|Select|Wrap|Line Numbers
  1. 08/01/2008          0
  2. 08/02/2008          2
  3. 08/03/2008          5
I'm confuse how the algorithm to do that. Please give any suggestions. All appreciated.

Thank you so much
Aug 18 '08 #1
Share this Question
Share on Google+
5 Replies


P: 8
can anyone help please?
I'm still stuck to think this problem :(
Aug 19 '08 #2

pbmods
Expert 5K+
P: 5,821
Heya, doxtor.

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

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

Thank you so much for replying
Aug 24 '08 #4

pbmods
Expert 5K+
P: 5,821
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.).
Aug 24 '08 #5

P: 8
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
Aug 25 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.