CODEWITHSUNDEEP
mysqldatabaseenums
Alexis
Alexis

Reputation: 1162

Which is the best way to store a gender column in MySQL for a web project

I've built a JavaEE project and need to store the gender of a user in MySQL. And I want to show this property when adding a new user account. For example

<select name="gender">
    <option value="0">Male</option>
    <option value="1">Female</option>
</select>

Now I've found 2 approaches to store this property in MySQL.

  1. use integer/char to store gender column:

    gender tinyint(1) not null;

    in this case, I have to write these in my Java code:

    public class StaticData {
    public static final short MALE = 0;
    public static final short FEMALE = 1;
}

    If so, my database will depend on my Java code to limit the value range and explain what 0/1 represents (low level depends on high level). I think that's not a good idea.

  2. use an enum to store gender column:

    gender enum("MALE", "FEMALE") not null

    and in this case, my Java code will depend on the enum's value. There are 2 ways to get these values:

    1) public enum Gender { MALE, FEMALE }, it's nearly the same problem as with method 1.

    2) retrieve these values from MySQL, such as "SHOW COLUMNS FROM user LIKE \"gender\"" and then use a regular expression to split the string. But this is a verbose method. And have to query from db everytime I load a JSP. I think it would be expensive.

Is there a common idiom for this? If not, what factors do I need to consider when choosing the appropriate solution for my project?

Upvotes: 3

Views: 8850

Answers (2)

Evert
Evert

Reputation: 99687

It's probably not needed for most applications to ask for gender, but if you are absolutely required, I would suggest you give the option for 'Other' and 'Unknown' as well, the former for people who don't exactly fit in the binary gender model, and another for if the person chose not to disclose it.

For those 4 values, the easiest might be to store this in your database as a CHAR(1), or an ENUM("F", "M", "O", "U") (note that I chose these alphabetically instead of the surprisingly typical 'male first').

Upvotes: 3

Salazaar
Salazaar

Reputation: 384

I would make a Table "Gender" with an id as key and the Text wich should be shown to the user:

  • ID - Text
  • 1 - Male
  • 2 - Female

In the Person you save the gender-ID as foreign key.

When you want to show the person you join can join the gender to the person and print the text out. For the select control you select all rows in the gender table and generate the html with the rows.

If there is a new gender to support, you just add it in the database, no java code must be edited...

Upvotes: 1

Related Questions