How do you model a table to convert units?

Question

I am looking to create a db model of various units and their relationship to each other. For example, 36 inches = 3 feet = 1 yard = .9144 meters, etc. This table also stores cups in ounces, pounds, kg, grams, cm and all kinds of measurements.

How do you do this? I was thinking of something like this:

 Amount |  Units |  ConversionFactor |  ConversionUnits
 1 |  foot |  12 |  inches
 1 |  yard |  36 |  inches

But honestly, this seems like a terrible idea. Trying to figure out how many feet in the yard will be very confusing, and I don't think I can keep all the necessary conversions.

What other ideas exist? I know this is a problem. Thanks!

Answer 1

Store conversions in SI units , not other non-metric units. Then you can convert between blocks without requiring an explicit conversion.

Unit | Class | Base Unit Multiplier ------------------------------------------------------ foot | length | 0.304800610 yard | length | 0.914401830 square foot | area | 0.092903040 ... 

So 14 feet in the yard:

 14 feet * 0.304800610 = 4.26720854 meters 4.26720854 meters * 0.914401830⁻¹ = 4.66666667 yards 

Answer 2

Choose a base unit for each dimension that interests you (read this wiki page, this will be useful). For example, if most of your data is in SI units, you have chosen a kilogram for mass, a second for time, a meter for distance, etc. If most of your data is in US units, select units of measure from ordinary US units , such as pound for mass, foot for length, second for time.

Then save for each actual device that you want to process, the conversion factor in the corresponding size block. Therefore, if you select the foot as the base unit of distance, store

 Unit Dimension Factor Foot Distance 1 Metre Distance 3.28084 Mile Distance 5280 

To actually do the conversion, once you have checked the fit, just multiply by the Factor source block and divide it by the Factor target block. For example, to get from meters to miles, multiply by 3.28084, then divide them by 5280.

Answer 3

I think the original proposed layout is fine, except that it doesn't include a class (as in Seth's answer) - you don't want to try to convert between pints and inches.

A conversion between two units in which neither of them is a conversion unit is simply achieved by extracting the conversion records of both blocks and dividing one factor by another (for example, 36/12 = 3 feet in the yard).

If you are especially worried about accuracy, you can make sure that all units for a given class have entries for all other units in the same class - this, however, seems to me superfluous.

Answer 4

 CREATE TABLE UnitConversion ( [FromUnit] NVARCHAR(100), [ToUnit] NVARCHAR(100), [FromOffset] DECIMAL(29,10), [Multiplicand] DECIMAL(29,10), [Denominator] DECIMAL(29,10), [ToOffset] DECIMAL(29,10) ) 

ToUnit = (FromUnit + FromOffset) * Multiplicand / Denominator + ToOffset