storing weight in a database table

Question

I am playing around with learning MVC and want to create a recipe recorder application to store my recipes.

I am using .net with Sql Server 2008 R2 however I don't think that really matters with what I am trying to do.

I want to be able to record all of the measures I use. In my country we use metric however I want people to be able to use imperial with my application.

How do I structure my table to cope with the differences, I was thinking of storing all of the measurements as ints and have a foreign key to store the kind of weight.

Ideally I would like to be able to share the recipes between people and display the measurements in their preferred way.

Is this the right kind of way

IngredientID PK
Weight int
TypeOfWeight int e.g. tsp=1,tbl=2,kilogram=3,pound=4,litre=5,ounce=6 etc
UserID int 

Or is this way off track? Any suggestions would be great!

Answer 1

I think you should store the weights (Kilo/Pound) etc as a single weight type (metric) and simply "display" them in the correct conversion using the user's preference. If the user has there weight settings set to Imperial, values entered into the system would need to be converted as well. This should simplify your data anyway.

Similar to Dates, you could store every date and what timezone it is from, or otherwise store all dates as the same (or no timezone) and then display them in the application using offsets according to the user's preference

Answer 2

If you are storing weights (a non-discrete value) I would strongly suggest using numeric or decimal for this data. You have the right idea with the typeofweight column. Store a reference table somewhere showing what the conversion ratio is for each (to a certain standard).

This gets quite tricky when you want to show ounces as TSP, because the conversion depends on the ingredient itself, so you need a 3rd table - ingredient: id, name, volume-to-weight ratio.

Example typeofweight table, where the standard unit is grams

type   | conversion
gram   | 1
ounce  | 28.35
kg     | 1000
tsp    | 5            // assuming that 1 tsp = 5 grams of water
pound  | 453.59

Example ingredient volume to weight conversion

type   | vol-to-weight
water  | 1
sugar  | 1.4          // i.e. 1 tsp holds 5g of water, but 7g of sugar

So to display 500 ounces of sugar in tsp, you would use the formula

  units x ounce.conversion x sugar.vol-to-weight
= 500   x 28.35            x 1.4

Another example with 2 weights

Ingredient is specified as 3 ounces of starch.  Show in grams
= 3 x 28.35    (straightforward isn't it)

or

Ingredient is specified as 3 ounces of starch.  Show in pounds
= 3 * 28.35 / 453.59