1

I have an existing table called exampletable1, now i have been given a new list of data called "medicines" it only have: male,female as its value. I'm having a problem whether to add another varchar column or to create another table and pass a foreign key to the exampletable1

1

The cardinality of 2 (male,female) is way too low.

Although you are using InnoDB and could have foreign keys, the parent table would have only two rows. It's not worth the processing power. Therefore, do not add another table. Indexing it is not worth it because a cardinality of 2 simply screams at the Query Optimizer "DO NOT USE THE INDEXES". Foreign Keys to a table with two values is not worth it.

Do not add a VARCHAR(1) because that is really two bytes.

There are four(4) options to use one byte

  • You could use CHAR(1) with 'M' and 'F' as the only values
  • You could use TINYINT with 0 for Male and 1 for Female
  • You could use TINYINT with 1 for Male and 0 for Female
  • You could use ENUM('M','F') (this might be two bytes)
  • Tnx for the insight!, i could also pass null values with it so maybe char(1) could work am i right? – zxc Aug 12 '14 at 2:05
  • You could use NULL with any of those options. It's your choice. – RolandoMySQLDBA Aug 12 '14 at 3:03
  • NULL adds a bit to NULL-able fields map, which is aligned to full byte(s) – akuzminsky Aug 12 '14 at 3:54
  • 1
    Rolando, when is ENUM('M','F') two bytes? You mean if it can be NULL? – akuzminsky Aug 12 '14 at 3:55
  • 1
    Also: ISO/IEC 5218 – ypercubeᵀᴹ Aug 12 '14 at 10:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.