How do you model a table for unit conversion?

Question

I'm looking to create a db model of various units and their relation to each other. For instance, 36 inches = 3 feet = 1 yard = .9144 meters etc. This table would also store cups in ounces, pounds, kg, grams, cm and all sorts of measurements.

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

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

But frankly, this seems like a terrible idea. Trying to figure out how many feet in a yard would be very convoluted, and I don't think I could ever store all the conversions I need.

What other ideas are there? I know this is a solved problem. Thanks!

Answer 1

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