CODEWITHSUNDEEP
database-designbusiness-logicbusiness-rules
Naoki
Naoki

Reputation: 1

How to deal fluctuation cost record for single product

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!

Upvotes: 0

Views: 75

Answers (1)

No&#39;am Newman
No&#39;am Newman

Reputation: 6477

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.

Upvotes: 0

Related Questions