DB：节省用户的身高和体重 [英] DB: 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),
    ....

你的想法...

然后，您的用户表格将引用高度和重量表格 - 以及可能的许多其他维度表格 - 星座，婚姻状况等。 / p>

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 。


• 它使你的各种维度的逻辑 - 包括非数字（如星座符号）显然是相似的 - 你没有在每个数据类型的地方的特殊情况代码。




• 它可以很容易地添加数据的新表示（例如，添加手列到高度表） / li>
  

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

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