节省用户的身高和体重 [英] Saving user's height and weight

问题描述

我应该如何将用户的身高和体重存储在MySQL数据库中，以便可以使用该信息来查找某个身高或体重在一定范围内的用户?另外，我将需要能够以英语或公制显示此信息.

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.

我的想法是存储有关以厘米为单位的身高和以千克为单位的体重的信息(相对于英文，我更喜欢使用公制).我什至可以让用户输入他们的信息和英语系统，但是在保存之前进行到公制的转换.我认为在SQL中将千克转换为磅可能很容易，但是我不确定将178厘米转换为5'10"(略微向下舍入)有多么容易.

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),
    ....

您明白了...

然后，您的用户表将引用身高和体重表-可能还有许多其他尺寸表-占星术，婚姻状况等.

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),
    ...
);

然后搜索4到5英尺之间的用户:

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;

此方法的几个优点:

• 您无需复制努力"(就好像是真正的工作)就可以进行显示转换，只需选择要显示的格式即可.
• 它使填充HTML中的下拉框变得非常容易-例如，仅SELECT english FROM height ORDER BY inches.
• 这使您在各个维度上的逻辑(包括非数字维度(如占星符号)明显相似)对于每种数据类型，您并没有处处都有特殊情况代码.
• 扩展性非常好
• 通过它可以轻松添加数据的新表示形式(例如，将手"列添加到高度表中)

• 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)

这篇关于节省用户的身高和体重的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！