Inventory System Help Needed

[fknaut]

I am creating a MySQL Database with PHP front-end to keep track of an inventory. This is how it works:

When the supplies come in, it is weighed and dated and goes into the Raw Materials bin. Now for the tricky part and where I need help to figure out. Next it is processed and the available weight is calculated. This processing can occur several different ways (i.e. type 1 - one weight, type 2 - one weight, etc. How and where would I do this calculation and have the available weight calculated each time it is processed. Then when it is finished, it goes to deliverables and any excess goes to the excess weight bin. How would it be best to do this? 😕

Should I create different tables for the inventory steps or one table? Should I use a primary key or not?

The supplies are bulk goods that will be recycled. They come in by truckload and are weighed. For example, steel, comes in with a weight of 21450 pounds on 9/8/08 with a shipment record of abc123 from ussteel with a type of cans- that is the raw weight, date received, reference number, origin and type is how it is received. Next it is processed and the status would change from raw to processed and then there would be a processed weight and a processed date. The processing can occur multiple times while the incoming raw materials is once per material reference. Also, the available weight should decrease after each processed weight occurs. When it is done processing, you are left with out processed weight, out date and waste weight.

[Piranha]

The last question first. Yes, you should use a primary key. If you don't have a really good reason not to use it you should use a primary key.

About the inventory steps: I don't understand the steps. And I don't understand if it is something that have to be stored as parts (say, for example whole hams that can't be cut into half) or something that can easily be delivered in any weight (say a fluid stored in big tanks). Please give a better description in order to get help.

[fknaut]

The supplies are bulk goods that will be recycled. They come in by truckload and are weighed. For example, steel, comes in with a weight of 21450 pounds on 9/8/08 with a shipment record of abc123 from ussteel with a type of cans- that is the raw weight, date received, reference number, origin and type is how it is received. Next it is processed and the status would change from raw to processed and then there would be a processed weight and a processed date. The processing can occur multiple times while the incoming raw materials is once per material reference. Also, the available weight should decrease after each processed weight occurs. When it is done processing, you are left with out processed weight, out date and waste weight.

[NogDog]

I think the general approach I'd take is to have a shipment table and a status table (or whatever it should be called). The shipment table would have the info about a given shipment that never changes, e.g. its origin, type, invoice number, etc. The status table would have a foreign key to the shipment table, and each row would represent any recording of that shipment's status, weight, or whatever else might change, along with a timestamp field. (The first entry would be when the shipment is received, with its initial weight and status of "raw".) This way you have a complete history of a shipment should you need that, and if all you want is the current status, then you would query the status table for the latest row for that shipment's ID.

There could be other tables for things like a list of valid shipment types, or one for processing status categories, etc., whose primary keys would then be used as foreign keys in the two main tables as needed.

[fknaut]

How would I subtract the processed weights to show the availability? In PHP or MySQL?