11

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!

Nathan DeWitt
  • 6,511
  • 8
  • 46
  • 66
  • Why do you need to store conversion units in a database? They're not going to change. – In silico Oct 18 '10 at 01:17
  • 2
    Ahh, how simple this would be if you guys took up the metric system. But that's another discussion. – steinar Oct 18 '10 at 01:42
  • Because I want to store foods & their nutritional information. I want to enter a recipe in grams and switch it to ounces whenever I feel like it. – Nathan DeWitt Oct 18 '10 at 01:48

4 Answers4

10

Store conversions to SI units, not to other non-metric units. Then you can convert between units in without needing to know the explicit conversion.

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

So 14 feet in yards is: 

14 feet * 0.304800610 = 4.26720854 meters
4.26720854 meters * 0.914401830⁻¹ = 4.66666667 yards
Seth
  • 45,033
  • 10
  • 85
  • 120
  • 1
    -1 for SI units - this solution is only likely to be useful in situations where SI units are the norm, otherwise it is likely to introduce rounding errors (if converting between feet and yards, for example). Counter-balanced by +1 for including Class. –  Oct 18 '10 at 10:32
  • 6
    @Mark - SI units *are* the norm (for 6 billion of us, anyway :) Besides, you may get rounding errors with imperial units too, e.g.: `1 foot = 0.000189393939394 miles`. – Seth Oct 18 '10 at 16:59
  • which Portland are you in? :) I would normally suggest picking the smallest possible conversion unit as the base unit. –  Oct 18 '10 at 17:23
  • 1
    Is there any way to extend this approach to include conversions such as temperature (F-C, and vice versa), which are not related by a single multiplier? (See follow-up question [here](http://stackoverflow.com/questions/11143863/unit-conversion-table-in-sql).) – kmote Jun 21 '12 at 17:51
4

Pick a base unit for each dimension you are interested in (read that wiki page, it'll be useful). For example, if most of your data is in SI units, you would pick kilogram for mass, second for time, metre for distance, and so on. If most of your data is in US units, pick units from the US customary units, for example pound for mass, foot for length, second for time.

Then store, for each actual unit you want to be able to handle, the conversion factor to the dimensionally-appropriate base unit. So if you choose foot as your base unit of distance, store

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

To actually do a conversion, once you've checked that the dimensions match, simply multiply by the Factor of the source unit, and divide by the Factor of the destination unit. For example, to get from metres to miles, multiply by 3.28084, then divide by 5280.

AakashM
  • 62,551
  • 17
  • 151
  • 186
3
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

Weifeng
  • 709
  • 1
  • 6
  • 18
  • Could you explain what the offset fields do? Perhaps an example or two would be beneficial. – karns Dec 09 '22 at 15:52
2

I think the original post's proposed schema is fine, apart from not including Class (as in Seth's answer) - you don't want to try to convert between pints and inches.

Converting between two units where neither of them is the conversion unit is simply achieved by retrieving both units' conversion records and dividing one factor by the other (eg. 36/12 = 3 feet in a yard).

If you are particularly concerned about accuracy, you could ensure that all units for a given class have entries for all other units in the same class - this strikes me as overkill, though.