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

Question

I created a JavaEE project and you need to save the user gender in MySQL. And I want to show this property when adding a new user account. for instance

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

Now I have found 2 approaches for storing this property in MySQL.

• use integer / char to store the gender column:

   gender tinyint(1) not null; 

  in this case, I have to write them 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 range of values ​​and explain what 0/1 represents (low level depends on high level). I think this is not a good idea.

• use the enumeration to store the gender column:

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

  and in this case my Java code will depend on the value of the enumeration. There are two ways to get these values:

  1) public enum Gender {MALE, FEMALE}, this is almost the same problem as in method 1.

  2) extract these values ​​from MySQL, for example, "SHOW COLUMNS FROM user LIKE \" gender \ "", and then use the regular expression to split the string. But this is a complicated method. And I need to query db every time I load the JSP. I think it will be expensive.

Is there a common idiom for this? If not, what factors should be considered when choosing the right solution for my project?

Answer 1

I would make a table "Sex" with the id key, and the text that should be shown to the user:

• ID - text
• 1 - Man
• 2 - Female

In Person, you store the gender identifier as a foreign key.

If you want to show that the person you have joined can join the floor for the person and print the text. For the select control, you select all the rows in the gender table and generate html with the rows.

If you need a new floor, you just add it to the database, no Java code should be edited ...

Answer 2

Most applications probably don’t need to specify gender, but if it is absolutely necessary, I would also advise you to specify the option “Other” and “Unknown”, the first for people who do not quite fit into the binary file. gender model and another, if a person decided not to disclose it.

For these 4 values, the easiest way would be to save them in your database as CHAR(1) or ENUM("F", "M", "O", "U") (note that I selected them in alphabetical order instead surprisingly typical "first men").