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
Add a comment
|
1 Answer
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)
answered Aug 12, 2014 at 2:01
-
Tnx for the insight!, i could also pass null values with it so maybe char(1) could work am i right?– zxcCommented Aug 12, 2014 at 2:05
-
You could use NULL with any of those options. It's your choice. Commented Aug 12, 2014 at 3:03
-
NULL adds a bit to NULL-able fields map, which is aligned to full byte(s) Commented Aug 12, 2014 at 3:54
-
1Rolando, when is ENUM('M','F') two bytes? You mean if it can be NULL? Commented Aug 12, 2014 at 3:55
-
1