Varchar field as foreign reference

[ray_broome]

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?

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

[trudge]

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?

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

Since you are already using an ID as a primary key, I assume you have a bridge table to relate the ID to a name. May I assume the ID is an AUTO INCREMENT field? If so, then when you import your data from the spreadsheet let MySQL generate the IDs for you.

In other words, I would stick with using an ID instead of a character-based key. But in your case I don't think it would matter either way.

Your other questions are likely answered in the docs.

[ray_broome]

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

[trudge]

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

Without seeing all your table schema or knowing what queries you will be making, it's hard to give you an answer. What comes to mind however is 'normalization'. Have you gone through at least 3NF? It sounds like you need to settle down and get yourself a good book on RDBMS design in general and MySQL specifically. 'MySQL' by Paul DuBois is an excellent start, as is 'Database Design for Mere Mortals' by Michael J. Hernandez. Designing relational databases properly and securely (SQL Injection attacks) is not a trivial matter and can't be learned in a few forum postings.

[ray_broome]

ok cool thanks i'll check them out and/or some other ones possibly

[ray_broome]

ok i got the data loaded. I was using phpmyadmin's import feature and i was selecting the 'CSV' radio button but i changed it to 'CSV using LOAD DATA' radio button and that worked. I figured something might have been up with the importing because i inserted a single record and left some foreign key fields blank (NULL) and it inserted successfully so my database design should not have been a problem i think seems something was bugging out during the import.

but thanks anyways hope this might help someone having similar problems