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.
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;
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)
p.s. XX and XY is indeed a cool scheme (should be VARCHAR(3) then, to cater for XXY)