The best technique for storing gender issues in a MySQL database

Question

What is the best way to store gender in a MY SQL database? I am a little confused about this problem because different people express differently. Some suggest saving it in INT , but others suggest TINYINT and Enum , but some others suggest saving it in CHAR (1) M for men and F for women.

In addition, he becomes more doubtful by listening to http://en.wikipedia.org/wiki/ISO_5218

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

A valuable expert suggestion is much appreciated.

Answer 1

Personally (because this is a somewhat subjective question), I would go with ENUM . MySQL does not support CHECK constraints, so ENUM is the only way to make sure the value is M or F (or M or F ). For me, this is the most important point.

In addition, ENUM needs only one byte of storage space (according to docs ), so it is also efficient in CHAR(1) or TINYINT .

I don’t understand the TINYINT approach at TINYINT because you are running out of queries like this:

 SELECT * FROM myTable WHERE gender = 1; 

Is 1 male or female? And if he is male, then female 0 ? Or is it 2 ? Or maybe 16 ? You already need to remember a bunch of things to write (and maintain) the application; no need to add to this heap.

---

Adding 2017-12-01 Ed Gibbs: Repeating my answer when I stumbled upon it in an unrelated Google search ...

The ENUM approach makes sense when used with a static one-dimensional domain of values ​​(for example, Y / N, To / Cc / Bcc), but it is not suitable for gender. My answer was in the context of nerd: "How do you restrict a column to M or F", and not in the wider context of sexing.

D The Mac solution is more reliable and enlightened, but still incomplete because it is one-dimensional, while the floor is multidimensional.

When classifying people in any subjective category (gender, race, class identity, religion, political affiliation, employment status, ethnicity, preferences for women, ammorization, etc.), consider the many ways in which they can identify themselves. There is not always a solution to check one window.

This goes beyond ideology. An attempt to categorize a multidimensional object into one dimension is inaccurate, and inaccuracy has value.

Answer 2

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

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

First you can download it with:

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

This way you can expand the table as new values ​​are needed for gender. In your USER table (or something else) you save static_gender_id and get the gender value using JOIN.