Ok I have been beating my head against the wall for several weeks on this. I have limited database design skills. I have been assigned the task of creating a manufacturing inventory database for the manufacturing of additives and other chemical products using MS SQLServer.

I need to track the ordering of new raw material (from PO)

I need to track the delivery of raw materials, in liquid or solid form, delivered via tanker trucks, or freight if drum based. Each delivery comes with a lot # used to track where it came from. (have to track amount delivered, date, time delivered, location, etc)

I then need to track how much of each raw is in several holding tanks.

I need to track the amount used (and remaining) of each raw after a blend of various raws together to form our final "product".

I need to track how many containers are created in this blending phase, drums, gallons, pints, etc.

I need to track when an order comes in for some final product (PO again)

I need to track the amount shipped for each order (and decrease the final product stock)

I am stuck on how to track the raws coming in (need a history of delivery), then whether to track the same raws being used in the same table or a different table. I just separated the raw_delivery table and the lot_num table (they used to be one table 'lot_num'). I haven't even gotten to the point of dealing with changing raws to finished product.

I question how I am going to track the current inventory of raw materials. Do I store that info in the database somewhere or calculate it someone? I have read that people do one or the other.

I have attached my relationship diagram so far, some things are just sitting there not related to anything yet.

Any help would be appreciated. Thanks very much