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:
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
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?
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...