Saving user's height and weight

How should I store a user's height and weight in a MySQL database such that I can use the information to find users within a certain height or weight? Also, I will need to be able to display this information in either English or metric system.

My idea is to store the information for height in centimeters and weight in kilograms (I prefer metric over English). I can even let the user enter their information and English system, but do the conversion to metric before saving. I think converting kilograms to pounds might be easy to do in SQL, but I'm not sure how easy it would be to convert 178 c开发者_开发知识库entimeters to 5'10" (rounded slightly down).

Should I be saving English and metric values in the database so that I don't need to do conversions when I do my queries? Sounds like a bad idea to store derived/computed values.

---

There are several ways... one is to just have two numeric columns, one for height, one for weight, then do the conversions (if necessary) at display time. Another is to create a "height" table and a "weight" table, each with a primary key that is linked from another table. Then you can store both English and metric values in these tables (along with any other meta info you want):

CREATE TABLE height (
    id          SERIAL PRIMARY KEY,
    english     VARCHAR,
    inches      INT,
    cm          INT,
    hands       INT  // As in, the height of a horse
);

INSERT INTO height VALUES
    (1,'4 feet',           48, 122, 12),
    (2,'4 feet, 1 inch',   49, 124, 12),
    (3,'4 feet, 2 inches', 50, 127, 12),
    (3,'4 feet, 3 inches', 51, 130, 12),
    ....

You get the idea...

Then your users table will reference the height and weight tables--and possibly many other dimension tables--astrological sign, marital status, etc.

CREATE TABLE users (
    uid         SERIAL PRIMARY KEY,
    height      INT REFERENCES height(id),
    weight      INT references weight(id),
    sign        INT references sign(id),
    ...
);

Then to do a search for users between 4 and 5 feet:

SELECT *
FROM users
JOIN height ON users.height = height.id
WHERE height.inches >= 48 AND height.inches <= 60;

Several advantages to this method:

• You don't have to duplicate the "effort" (as if it were any real work) to do the conversion on display--just select the format you wish to display!
• It makes populating drop-down boxes in an HTML select super easy--just SELECT english FROM height ORDER BY inches, for instance.
• It makes your logic for various dimensions--including non-numerical ones (like astrological signs) obviously similar--you don't have special case code all over the place for each data type.
• It scales really well
• It makes it easy to add new representations of your data (for instance, to add the 'hands' column to the height table)

---

I would do it the way that you have said you would like to do it, but on the converting part, you would not convert 178 centimeters to 5'10", you would convert it to 70", then if need be, convert that into 5'10".

---

Think of 5'10" as either 70" or 5.8333333'. In that case, converting betwen 70" or 5.83333 is just a multiplication, so its easy to store in the db as centimeters if you so choose.

The issue of what the user sees is a presentation issue and nothing to do with the database.

---

I agree that storing computed values in this case is not ok. Your choices are perfect.

However, I would do the computations at the application level and query the DB with those values - depending on the language your application is written in , I am sure there are plenty o libraries/modules that are made that can compute those transformations.

Edit - to address the issue of storing computed values in DB:

While this is considered to be a bad practice in working with DBs, I usually am not 100% against this practice - just 90%.

I tend to store computed values in DB only when the computations are complex and would take enormous resources to get to the result wanted - this is clearly not the case.

If you would store computed values here you would have only the disadvantages of this technique - when modifying a record, you would have to modify the data in multiple places to keep the consistency of your DB