Is normalizing the gender table going too far?

Question

I am not a database guy, but am trying to clean up another database. So my question is would normalizing the gender table be going too far?

User table:
userid int pk,
genderid char(1) fk
etc...

gender table:
genderid char(1) pk,
gender varchar(20)

Now at first it seemed silly to me, but then I considered it because i can then have a constant data source to populate from or bind from. I will be using WPF. If it was another framework I would probably avoid it, but what do you think?

Answer 1

Just use a bit. 0 for male, 1 for female. Normalizing this to a lookup table only makes sense if you plan on adding "other. "

See also this post.

Answer 2

Yes. I think that You can use enum in code and bind eventuatly to it.

null - unknow ; 0 - male ; 1 - female;

or you can use bool type to define this

null - unknow; true - male; false - female

Answer 3

I'm also not a database guy but I do it. It gives me the possibility to assure that only the genders are entered, that are valid (referencial integrity) and I can also use it to populate the selection control.

Answer 4

Well, your company might have a requirement that, if possible, everything be normalized.

Also, depending on the business & data, you might need to include transgenders as well which would create 3+ genders (I don't know how many there are, haven't checked)

Answer 5

Whether or not you choose to normalize your table structure to accomodate gender is going to depend on the requirements of your application and your business requirements.

I would normalize if:

• You want to be able to manage the "description" of a gender in the database, and not in code.
  • This allows you to quickly change the description from Man/Woman to Male/Female, for example.
• Your application currently must handle, or will possible handle in the future, localization requirements, i.e. being able to specify gender in different languages.
• Your business requires that everything be normalized.

I would not normalize if:

• You have a relatively simple application where you can easily manage the description of the gender in code rather than in the database.
• You have tight programmatic control of the data going in and out of the gender field such that you can ensure consistency of the data in that field.
• You only care about the gender field for information capture, meaning, you don't have a lot of programmatic need to update this field once it is set the first time.

Answer 6

I can think of applications where I'd use different columns for sex and gender, have three values for sex (male/female/decline to state) and six for gender (male/female/transgendered male/transgendered female/asexual/decline to state). Granted, I live in San Francisco, where there's an level of public discussion of transgender issues that much of the rest of the world is behind the curve on.

The point is: without a compelling reason to think otherwise, I'd assume that any simplifying assumption I made about demographics was limited and parochial. The cost of breaking sex out to its own table is small now and expensive later. I wouldn't avoid the small cost on the basis of an assumption.

Answer 7

I'll remark on another aspect: sorting. Normally, 'M' sorts after 'F'; in a project one time, a database table had a gender field with either of those two values. There was a desire to be able to sort results on the gender (census data) and a further preference to have 'M' appear before 'F'. My solution was to add a separate lookup table, assigning the Male value an ID of 0, and Female an ID of 1. So queries on the main table could easily be sorted on the new genderID field.