Handeling Multipule Units of Measure

[Waldenbound]

I am designing an inventory system that will track changes to product during production and labor hours. The product is a protein based biological reagent, and is measured in volume at some stages, mass in others, and both in others (concentration mg/ml). I need to be able to calculate a price per unit at any stage in production. I am having trouble developing a good table design.
I have a table listing my products by product ID number, Start Date, and catalog number. A table with labor that has my product ID, Hours worked and the process performed. A volume table with product ID, Volume Date, and Volume in ml. A mass table with product ID, Mass Date, and Mass in mg. I made volume and mass separate tables to avoid a ton of nulls.
The database will be medium (10,000 product ID), with many process occurring on the same product throughout the day (all dates are time stamps also). There will also be removal and addition of quantity to products frequently.
I feel like having the quantity dependent on the date is going to get me into trouble. I can foresee very complicated queries to meet 5 or 6 date criteria. If I denormalize, and put a quantity in all my tables I need a column for volume and one for mass, so that I can have both measures at one time, but most of the time one or the other will be null.



Sorry if my post is hard to follow, but has anybody come across this change of units problem? Or have a simple solution I am missing?

[rpeare]

Ok, let's assume you have ItemA

Once you make a solution out of ItemA are you able to put ItemA back into your inventory (i.e. remove it from it's solution either by evaporation or some other separation method)?

If, once an item is mixed it can not be unmixed that will save you a ton of headaches.

Let's say for the sake of argument you ItemA in inventory. You have 1000ml of it, there should be an atomic weight for the item or if there isn't one you can assign one (weigh 1000 ml of the stuff and figure out a weight per ml) I don't know how exact your measurements have to be because volume changes with temperature but unless it's out of the norm this should be accurate enough.

Once you have a weight per unit everything should be easier to handle. It won't matter if someone enters a volume because you'll have the weight, and it won't matter if someone enters a weight because you can figure the volume.

Then when you start mixing materials, again assuming you can't unmix them. if someone makes 1000ml of a 10% solution of ItemA you know that 100ml of that is Item A and you can figure how much of that solution weight is actually ItemA.

Once you can convert back and forth regardless of measurement it's not that hard to figure cost.