Storing Sex (Gender) in Database

Storing sex (gender) in database

I'd call the column "gender".

Data Type   Bytes Taken          Number/Range of Values
------------------------------------------------
TinyINT     1                    255 (zero to 255)
INT         4            -       2,147,483,648 to 2,147,483,647
BIT         1 (2 if 9+ columns)  2 (0 and 1)
CHAR(1)     1                    26 if case insensitive, 52 otherwise

The BIT data type can be ruled out because it only supports two possible genders which is inadequate. While INT supports more than two options, it takes 4 bytes -- performance will be better with a smaller/more narrow data type.

CHAR(1) has the edge over TinyINT - both take the same number of bytes, but CHAR provides a more narrow number of values. Using CHAR(1) would make using "m", "f",etc natural keys, vs the use of numeric data which are referred to as surrogate/artificial keys. CHAR(1) is also supported on any database, should there be a need to port.

Conclusion

I would use Option 2: CHAR(1).

Addendum

An index on the gender column likely would not help because there's no value in an index on a low cardinality column. Meaning, there's not enough variety in the values for the index to provide any value.

Best technique to store gender in MySQL Database

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.

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

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...

Gender Storage and data types

We (EHR software) store as a 1 byte char, as it is concise and easier to understand while working with lots of demographic data.

The possible values map as follows:

• U - unknown or unspecified
• M - male
• F - female
• NULL - person hasn't been asked/no value on record.

For us, it is important to note if they have purposely decided to not provide their gender, or if it hasn't been captured yet (thus NULL vs U).

One consideration is mapping this as a more meaningful structure in the application (like, an enum in .NET or similar). It's maybe a little annoying as an application guy to have to use a switch or other approach to get the enum, whereas I can cast an enum directly from an numeric value.

It's a trivial concern, of course, but if you're curious how was solved it, we used a struct type that could be coerced from a string (explicit conversion from string) and static constants to act as possible enum values.

Is it bad practice to implement a separate table consisting of only two rows Female and Male?

I think the best approach in this case is a compromise:

1. Create a table called Gender with a single varchar column called 'Name' or 'Gender'. Gender is really a natural primary key. Put the values 'Male' and 'Female' in it.
2. Create foreign key to your Person table on a column named 'Gender'.

Now you only need to query from one table, but you're still protected from data inconsistencies by the foreign key, and you can pull the values for your dropdown from the Gender table if you want to. Best of both worlds.

Additionally, it makes life easier for someone working in the database, because they don't need to remember which arbitrary ids you've assigned to Male/Female.

store M if the gender type is 0 and store F if gender type is 1using php and mysql

Not sure if this is a joke or something, but anyway:

if( $_POST['sex'] == 0 ) {
    $gender = 'M';
}
else {
    $gender = 'F';
}

Or, you know, just use M and F as the form values in the first place.

Listing user gender options in a view

Try using helper and locale.

# app/helpers/application_helper.rb

def gender_lists 
  I18n.t(:gender_lists).map { |key, value| [ value, key ] } 
end


# config/locale/en.yml

en:
  gender_lists:
      "": Unspecified
      "true": Male
      "false": Female

and on your view, you can do this

<div>
  <%= f.label :gender %><br />
  <%= f.select :gender, gender_lists​ %>
</div>

===

Note :

Just suggestion, user string type data instead of boolean

Database Design Issue - Different Statuses for Different Sex

Question is too broad and many possible answer, but I tried to since I'm into database designing.

It's not perfect but I hope it can help you a little.

Calf Table (Calf records)

Relationships to Cattle 2x

Cattle Table (Cattle records)

Cattle Relationships

Insemination Table (Insem records)

Special Attribute Table

Special Attribute Record Table

Special Attribute Value Table

Calves View

Cattle Status View

---

Related Topics