关于属性选项id的小事情的数据库或php代码?

User profile. I have some attributes that have only 2 or 3 options. For example I store gender in table as tinyint (male=1, female=2). So in table I have tinyint but in frontend I need to display string. I have about 4 attributes that have just about 2 options.

用户配置文件。我有一些属性只有2或3个选项。例如，我在表中存储性别为tinyint(男性=1，女性=2)。在表格中我有tinyint但是在前端我需要显示字符串。我有大约4个属性，只有2个选项。

So I have two options how to display string from option:

所以我有两个选项可以选择如何从选项中显示字符串:

Create extra table where all attribute options will be stored. But in this case I need to create for such a tiny thing everytime extra join.

创建额外的表，所有属性选项将被存储在其中。但在这种情况下，我需要为这样的小事情创建一个额外的连接。

Or I can put in profile_helper.php all functions for this kind of attributes. For example

或者输入profile_helper。php所有函数都适用于这类属性。例如

function getGender($optionId){
  $gender = $optionId == 1 ? "male" : "female";
  return $gender;
}

Speaking about performance, is it worth to make extra join for such a small thing?

说到性能，是否值得为这么小的事情做额外的加入?

5 个解决方案

#1

You could make your life easier and ENUM all of the possibilities, like so:

你可以让你的生活更轻松，所有的可能性都有可能，比如:

`gender` ENUM( 'MALE', 'FEMALE')

That way, you literally insert the values 'MALE' or 'FEMALE' into the database, and when you retrieve the values from the database, you will get back one of those strings, and the only helper functions you may need is ucfirst() or strtolower().

这样，您就可以将值'MALE'或'FEMALE'插入到数据库中，当您从数据库中检索值时，您将得到其中的一个字符串，您可能需要的惟一帮助函数是ucfirst()或strtolower()。

I would only recommend this approach for things that are highly unlikely to be changing (of which gender is a perfect example).

我只推荐这种方法用于那些不太可能改变的事情(性别就是一个很好的例子)。

#2

I would defintely go the SQL route. More scalable in the future. Obviously not an issue with Gender but depending on what the other options are it may save you some time down the road. So something like:

我肯定会走SQL路线。未来可扩展性更强。很明显，这不是性别问题，但取决于其他选项，这可能会为你节省一些时间。所以类似:

SELECT tblGender.gender FROM tblGender, tblUser WHERE tblUser.genderID = tblGender.genderID AND tblUser.userID = "x"

#3

I'm not a fan of attribute tables. The SQL doesn't optimize as well. Some things should be normalized to another table. Some things should not. It really depends on your structure and what you're trying to do.

我不喜欢属性表。SQL也没有进行优化。有些东西应该规范化到另一个表。有些事情不应该。这取决于你的结构和你想做什么。

In the case of gender if you're building a multi-lingual app that's something that needs to map to your language tools anyway so a function or constants or ENUMs in the database are really all fine for any data that isn't likely to change.

就性别而言，如果你正在构建一个多语言的应用它需要映射到你的语言工具所以数据库中的函数或常量或枚举对于任何不太可能改变的数据来说都是可以的。

#4

Well, it depends. If it's gender and it definitively will not grow - a constants class is something I would do.

这得视情况而定。如果它是性别，而且它肯定不会增长——我就会创建一个常量类。

class Constants {

    public static $gender = array(0 => 'male', 1 => 'female');

    public static function getGender( $value ) {

        if ( !empty(self::$gender[$value])) {
            return self::$gender[$value];
        }
    }
}

Mighty useful, if you want to encapsulate and do, i.e.:

非常有用，如果你想封装和做，例如:

echo Constants::getGender($user->gender);

If however you want values you will change often, please go the SQL way, since it is not good to hardcode stuff unless it is a "genderlike" issue.

如果您想要经常更改的值，请使用SQL方法，因为硬编码是不好的，除非这是一个“性别”问题。

IMHO, no need to do joins for this, since gender will never be more than male and female.

IMHO，没有必要为此加入，因为性别永远不会超过男性和女性。

Cheers

干杯

#5

I would have a second table. It's flexible, scalable, easy to change or add genders (ie, not specified)

我要第二张桌子。它灵活、可伸缩、易于更改或添加性别(即不指定性别)

SELECT person.id, gender.name
FROM person
INNER JOIN gender
ON person.genderid = gender.id

It's still very efficient performance wise. It just seems silly because there's only two values right now, but that doesn't mean you shouldn't follow normalization rules.

这仍然是非常高效的性能。这看起来很傻，因为现在只有两个值，但这并不意味着不应该遵循规范化规则。

#1