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
centimeters 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:
SELECT english FROM height ORDER BY inches
, for instance.If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With