Best technique to store gender in MySQL Database

Question

Which is the best method to store gender in MY SQL Database? I am bit confused about this issue because different people express in different way. Some suggests storing it in INT is better, but other suggests TINYINT and Enum , But some others suggests store it in a CHAR(1) M for Male and F For Female.

Moreover it gets more doubtful while hearing http://en.wikipedia.org/wiki/ISO_5218

But in my point of view storing it in CHAR is a good idea, because it provides more robustness than ENUM ?Also I am concerned about scalability, want to know a better solution for storing millions of records.

A valuable suggestion from a expert is highly appreciated.

Answer 1

If you might ever have to deal with more complex gender issues (in-process gender changes or trans-gender), the best way is to use a reference table of possible values:

CREATE TABLE static_gender (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    Name varchar(10),
    Description varchar(100)
) ENGINE=INNODB;

Initially, you can load it up with:

INSERT INTO static_gender VALUES
(DEFAULT, 'F', 'female'),
(DEFAULT, 'M', 'male');

That way you can expand the table as new values for gender become necessary. In your USER (or whatever) table, you store static_gender_id and get the value for the gender with a JOIN.