CODEWITHSUNDEEP
phpmysql
user1324762
user1324762

Reputation: 805

Database or php code for small things regarding attribute option ids?

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.

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

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?

Upvotes: 3

Views: 197

Answers (5)

Steve Robbins
Steve Robbins

Reputation: 13812

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.

Upvotes: 0

Nemanja
Nemanja

Reputation: 1535

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.

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

Cheers

Upvotes: 0

Cfreak
Cfreak

Reputation: 19309

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.

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.

Upvotes: 0

nickb
nickb

Reputation: 59699

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

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

Upvotes: 3

Mark K
Mark K

Reputation: 591

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:

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

Upvotes: 0

Related Questions