0

Currently I'm being requested to develop and improve php & mysql web-based system. The systems get used to manage qty in hand, keep all record inventories, and sales of consumption foods. I had been lent some authorisation ID as admnistrator keepers which allow us to add and update existing data.

Every packages come to warehouse, administator keepers should keep them recorded (item id, datetime, cost, qty, suppliers details etc)

The system had implanted some functionality to track quantities of item whenever qty in stock below / less than benchmark qty, its automatically tell administator to create purchase order for specific item and send to its suppliers. The system should ensure the business keep various items available to be sell.

There's some example of based record I manipulated to make it more simplify,

ITEM_ID |       DATETIME      |COST ($)| QTY_IN_STOCK | BENCHMARK_QTY |  SUPPLIER_ID  |
 20041  | 2012-04-12 10:49:20 |   12   |     120      |     140       |      40       |

Because QTY_IN_STOCK < BENCHMARK_QTY, the system automatically tell keeper to make some purchase order form. Today we had been informed by SUPPLIER_ID (40), that they accept some form contain 'Candy Bar' ordered 100 qty which quoted about $10/ each.

This cases seem to be usual among business activities as economy growth factors and cost fluctuation over time to time. As packages we ordered reach to warehouse, keepers necessary to update records (DATETIME(AUTO UPDATE), COST, QTY_IN_STOCK)

How to resolve this situation by prevent inconsistency data (COST), as the business had remaining QTY_IN_STOCK (120 pcs purchased with $12 /each). It impossible to add new records for this, because we wont to keep data redundancy with similar SUPPLIER_ID.

Any ideas, comment or thoughts will be helpful!

Naoki
  • 1
  • 2

1 Answers1

0

Basically, you've got data in one table which should be spread over three different tables. Table 1 (PARTS) should hold standard data about each part: part number, part name, minimum inventory, supplier. Table 2 (MOVEMENTS) should hold movement data about each part: date, quantity, type of movement, cost. Parts can either enter inventory, in which case 'type of movement' will be 'purchase' and 'cost' will be the unit cost from the supplier, or they can leave inventory, in which case 'type of movement' will be 'usage' or 'sales'; 'cost' is irrelevant. Table 3 (BALANCES) should hold the inventory of each part - this will be the same as the total from the MOVEMENTS table. You can either sum the movements each time there is a new movement or else update the total.

This way you should be able to record purchases of the same part at different prices. Your inventory value can be calculated in a variety of ways - FIFO, LIFO or average; each will require a different algorithm for traversing the MOVEMENTS table.

No'am Newman
  • 6,395
  • 5
  • 38
  • 50
  • Thanks for your big responses! I figure out what are designed table 2 you had thought back, so table 2 have able track incoming & outcoming qty of items over cost (look alike history/log activities). The values of cost I retrieved should refer this table too. Depends on business's stock control management(I assume business used FIFO), I keep refer 'cost' from first incoming item on this tables until incoming qty equally to qty sold. I can refer 'new cost' from second incoming afterwards. – Naoki Oct 01 '14 at 16:21
  • I think by applying this ideas would lead some drawback (this situation possibly happens). Consider, item packaged to 30 qty into order-box. First incoming item left remaining qty (10 qty with cost $12), second incoming item have 100 qty with cost $10. I took all 10 qty + 20 qty from second one. I still didn't get it what cost should i used for the invoices? About idea on table 3 can you tell me exactly what should i do? – Naoki Oct 01 '14 at 16:22
  • @Naoki: as I wrote before, you have to decide if you are valuing your inventory by FIFO (first in, first out), LIFO (last in, first out) or by average. Invoices are not concerned with *cost*, they display your selling price - I assume that you want to sell at a profit. If the item normally costs you $10 - but can cost $12 - and you want a 50% markup, then your selling price will be around $16. I suggest that you read about accounting and managing inventory before asking more questions. Comments should only be about your original question. – No'am Newman Oct 02 '14 at 03:14