SQL Server - 'Gender' column

What is the most efficient type for a ‘gender’ column with ‘M’ and ‘F’ options?

-Should the ‘gender’ column be an index into a another lookup table?
-Should it be a ‘char’ column with some sort of constraint?

Probably an inconsequental decision but I still want to do things correctly.

Thanks

well, if you are literally going to have ‘M’ and ‘F’ values, then CHAR is really the only option

perhaps you were thinking of a numeric alternative? i like this a lot less

as for whether it should be an “index” into a “lookup” table, you are perhaps thinking of the foreign key concept, referencing the primary key of a gender table, and the answer is yes, you should do that

in my opinion, ‘M’ and ‘F’ work best, because you don’t have to join to the referenced table to know what they are, while still allowing the FK to ensure that you get only the valid values

in my opinion, ‘M’ and ‘F’ work best, because you don’t have to join to the referenced table to know what they are, while still allowing the FK to ensure that you get only the valid values

I think I undersand. By using ‘M’ and ‘F’ you can tell what the mean without doing a JOIN, but add the foreign key so that you constrain the possible values.

Thanks!

You could just constrain the column itself, no need for a foreign key to do all that.

My vote on encoding gender is to use chromosones–XX and XY. It’s much cooler.

To clarify; do you mean that the FK would still be used but that it would be ‘m’ or ‘f’, and reference to the gender table which would be set out like this


CREATE TABLE IF NOT EXISTS gender
( gender char (1) NOT NULL
, PRIMARY KEY (gender)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

bazz

Why not use ENUM SET data type?

both ENUM and SET are evil, and to be avoided in favour of more standard constructs

yes, bazz, ‘M’ and ‘F’ would be foreign keys

the gender table would probably have a description column to spell it what the keys mean (it’s not always so obvious)

i like FKs ~way~ better than constraints

to state just one benefit: if you need to add an additional value, you can usually do so if you are the business owner and have been given control panel access to all your tables, you just insert a new value in the gender table, and bob’s your uncle

whereas with a constraint, you have to fill out the paperwork to get the IT department to prioritize and reserve a block of time for the DBA to schedule a change to the schema (ALTER TABLE)

:slight_smile:

p.s. XX and XY is indeed a cool scheme (should be VARCHAR(3) then, to cater for XXY)