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.

Solution 1

Personally (because this is a somewhat subjective question) I'd go with ENUM. MySQL doesn't support CHECK constraints, so the ENUM is the only way to really make sure the value is M or F (or m or f). To me, that's the most important point.

In addition, the ENUM should only need one byte of storage space (according to the docs), so it's just as efficient storage-wise as CHAR(1) or TINYINT.

I don't understand the TINYINT approach at all because you end up with queries like this:

SELECT * FROM myTable WHERE gender = 1;

Is 1 male or female? And if it's male, is female 0? Or is it 2? Or maybe 16? You already have to remember a pile of things to write (and maintain) an application; no need to add to that pile.

---

Addendum 2017-12-01 by Ed Gibbs: Revisiting my answer when I stumbled across it on an unrelated Google search...

The ENUM approach has merit in use cases with a static, single-dimensional domain of values (e.g., Y/N, To/Cc/Bcc), but it's not valid for gender. My answer was in the nerd context of "how do you limit a column to M or F" and not in the broader context of gender definition.

D Mac's solution is more robust and enlightened, but it's still incomplete because it too is single-dimensional whereas gender is multi-dimensional.

When classifying human beings in any subjective category (gender, race, class identity, religion, political affiliation, employment status, ethnic identity, sexual preference, amouressness, etc.), consider the multiple ways in which they may identify themselves. There isn't always a "check a single box" solution.

This goes beyond ideology. Trying to categorize a multi-dimensional entity into a single dimension is inaccurate, and inaccuracy has a cost.

Solution 2

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.

Comments

[t0m]

Could you please be more specific !!

[Ed Gibbs]

It means that a lot of your tags aren't related to the question. The two that really jump out are mysqli or mysqldump - your question really doesn't have anything to do with them.

[NDM]

I've retagged and updated the title ;)

[Raedwald]

Possible duplicate of Storing sex (gender) in database

[t0m]

@Thanks...Ed Some suggests using char is better for a million records than enum ?

[Ed Gibbs]

I know, and I can't say they're wrong. It's subjective. For me the most important issue is that an ENUM won't allow invalid values, so you can trust the value in all million rows. To do that any other way in MySQL you'd need a char (or whatever) column with a foreign-key reference to a master table of valid values. In this specific case, where the data set is so small (presumably M and F, possibly NULL for unknown) and static (no need to modify the table to add support for other genders, political/ideological questions aside) an ENUM isn't all that bad.

[Pathros]

In Laravel when rolling back a migration, enum type causes the following error: [Doctrine\DBAL\DBALException] Unknown database type enum requested, Doctrine\DBAL\Platforms\MySQL57Platform may not support it..