UM Conversion

[Rawb]

I have an issue with converting/calculating based off of different units of measurement.

I'm setting up a database that, among other things, tracks supply levels and usage. The problem? I need to be able to convert from the purchased UM to the usage UM.

Normally this wouldn't be much of a problem but you see, I have some really (really) weird conversions that need to be handled. For example: Weight (pounds) to surface area (square feet).

What I've tried is to set up a UMConversions Table as follows:

UMConversions

UMID|UMDesc|UMModLen|UMModArea|UMModVol|UMModWeight|UMModQty
1|FT|1|0|0|0|0
2|LB|0|100|0|1|0
3|GAL|0|0|1|0|0
4|BOX|0|0|0|0|1
5|SQ FT|0|1|0|0|0
6|IN|0.0833333|0|0|0|0
7|SQ IN|0|0.0069444|0|0|0
8|ROLL|1000|0|0|0|0

So, using this Table, you can see that 1 pound (LB) is equal to 1 Weight Unit (it's our base UM for Weight) and 100 Area Units. And, since SQ FT is our Base UM for Area, 1 LB = 100 SQ FT.

I guess my question is: Is this a good way to set up a conversion system like this or do any of you have a better way?

[TheShabz]

I'm going to be a downer here...
First, I dont think that's a good way of converting as you are creating an arbitrary comparison between two UoMs that dont exist. There is no way that you can reliably say xLBs = xFT^2. A 1x1x1 cube of titanium will weigh MUCH more than a 1x1x1 cube of water. You would have to take into consideration density of the object. In a case of single element or single compound items, it could work, but how do you measure density of a desk, or a computer?

I don't know of a better way because I don't think there IS a better way. Youre comparing apples and oranges here. It's like the height/weight charts people use to tell whether someone is overweight, when clearly its the BMI that should be taken into consideration. 6'1" and 220lbs according to a height/weight chart is obese. Take a look at pro athletes who have those specs and tell me that's obese.

[Rawb]

TheShabz,

You are absolutely 100% correct. The conversions (at least between measurement "types") in UMConversion are completely arbitrary.

I should have mentioned in my original post though that we continually measure usage in both UMs and have calculated a reasonably accurate "conversion factor." And, while I know there's no way to be completely accurate in this kind of situation, I'd still like a way of creating "cross type" conversions like this and having the system able to guesstimate usage and cost/price.

Surely someone else has run into a similar situation and had to come up with a Unit of Measurement conversion table: Even converting inches to feet (or meters to yards, etc.)?

P.S.
No, the actual conversion ratio is not 100:1 :P

[TheShabz]

I cant really think of a way to improve the design. Seems like you have it correct. You basically created a matrix where each UoM would have its equivalent conversion for every other UoM. The only thing I would change is to have your "1" value go with the smallest UoM for that category. For Example, You have Ft as 1 and In as 0.0833333. I would make In 1 and Ft 12. Further, you could even get rid of foot or inch as they have a constant relationship and can be calculated as needed.

[Rawb]

I like the idea of going with the smallest value, the problem there though is twofold:

First (and probably least importantly) is the fact that I'm currently using the "1" values as my base units of measurement: In otherwords, you can figure out what something is "supposed to be converted to" by simply Querying for whatever has a one in that field.

If I go with the smallest value, it would certainly help eliminate weird rounding errors (for example: 1 inch isn't exactly 0.0833333 feet), but I'd no longer have an easy way - programmatically - of discovering the base UM.

And second, the UMConversion table will be user-editable. Right now, we may have no need for a millimeter UM so inch would be entered as "1." What if, a year down the road, we suddenly need to use mm as a length for something and suddenly we have a single item that's 0.0393701?

Now granted, I'm pretty much just playing devil's advocate at this point. With the level of precision I have, I won't need to worry about rounding errors until I deal with an extremely large value, but still. . .

How appropriate/inappropriate would it be for me to include a "Base UM for Length" (etc.) type variable in my SystemVars Table (as a way of allowing me to use your suggestion instead of going with "Base UM always equals 1!")?

[TheShabz]

If, as you said, the rounding errors won't be a problem then why complicate things? Seems good to go to me.