Ok, i have a database where i'm storing information on plants and in one of the tables is a plants table:

here's the first few fields in the table plants:

MySQL Syntax (Toggle Plain Text)

plant_id, family_id, genus_id, species_id, auth1, auth2, ...

plant_id, family_id, genus_id, species_id, auth1, auth2, ...

plant_id is the pk, family_id, genus_id, species_id are foreign keys.

soon i will change it to

MySQL Syntax (Toggle Plain Text)

plant_id, family_name, genus_name, species_name, auth1, auth2, ...

plant_id, family_name, genus_name, species_name, auth1, auth2, ...

where family_name, genus_name, species_name are foreign references (the name fields are all varchar(50)) is this a good idea tho or should i stick to the ids?

the reason i might change it is there are a little over 2300 records i have to add from an excel file which just uses names id's so i think this would make it easier/faster. What i was wondering though is if the index length of varchar fields might restrict searches on any names at all. So what i'm wondering is:
1) better to use a numerical id/code as foreign key?
2) what is the average/default index length on varchar fields and
3) say for example a user searched for "odoratissimus" for species_name and the index length were say 10, what would MySQL do in this case?

PS:
If this question doesnt seem to make sense let me know and try to elaborate/explain further since i'm not sure i may have gotten my question across clearly


Thanks in advance...

hey thanks

i have another quick question tho, in this same table not every entry will have an "infraspecies_id" or "syn_genus_id" (two fields from the table) and i want to set them as NULL in these cases but MySQL won't let me saying it violates the foreign key relationship likely because in the "infraspecies" table the ID is set as NOT NULL, is it not possible to do this and how if possible can i correct this/get around this?

thanks