Varchar field as foreign reference

Thread Solved

•
•
Join Date: Aug 2004
Posts: 24
Reputation: ray_broome is an unknown quantity at this point 
Solved Threads: 0
ray_broome ray_broome is offline Offline
Newbie Poster

Varchar field as foreign reference

 
0
  #1
Oct 8th, 2007
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) 
  1. 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) 
  1. 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...
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2007
Posts: 176
Reputation: trudge is an unknown quantity at this point 
Solved Threads: 20
trudge trudge is offline Offline
Junior Poster

Re: Varchar field as foreign reference

 
0
  #2
Oct 10th, 2007
•
•
•
•
Originally Posted by ray_broome View Post
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) 
  1. 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) 
  1. 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.
Amer Neely - Web Mechanic
"Others make web sites. We make web sites work!"
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2004
Posts: 24
Reputation: ray_broome is an unknown quantity at this point 
Solved Threads: 0
ray_broome ray_broome is offline Offline
Newbie Poster

Re: Varchar field as foreign reference

 
0
  #3
Oct 10th, 2007
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
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2007
Posts: 176
Reputation: trudge is an unknown quantity at this point 
Solved Threads: 20
trudge trudge is offline Offline
Junior Poster

Re: Varchar field as foreign reference

 
0
  #4
Oct 10th, 2007
•
•
•
•
Originally Posted by ray_broome View Post
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.
Amer Neely - Web Mechanic
"Others make web sites. We make web sites work!"
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2004
Posts: 24
Reputation: ray_broome is an unknown quantity at this point 
Solved Threads: 0
ray_broome ray_broome is offline Offline
Newbie Poster

Re: Varchar field as foreign reference

 
0
  #5
Oct 10th, 2007
ok cool thanks i'll check them out and/or some other ones possibly
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2004
Posts: 24
Reputation: ray_broome is an unknown quantity at this point 
Solved Threads: 0
ray_broome ray_broome is offline Offline
Newbie Poster

Re: Varchar field as foreign reference

 
0
  #6
Oct 11th, 2007
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
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread has been marked solved.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the MySQL Forum
Thread Tools Search this Thread



Tag cloud for MySQL
"use" 1 agplv3 alfresco amazon api artisticlicense aws bizspark breathalyzer camparingtocolumns changingprices cmg communityjournalism contentmanagement contractors copyright court crm data database design developer development distinct dui eliminate enter enterprise error eudora facebook form foss gartner gnu government gpl greenit groupware hiring hyperic images innerjoins insert ip joebrockmeier join keyword keywords kickfire laptop law legal license licensing linux maintenance managing mariadb matchingcolumns metron microsoft microsoftexchange mindtouch multiple music mysql mysqlcolumnupdating mysqldatetimeordermax() mysqlindex mysqlinternalqueries mysqlquery mysqlsearch news open-xchange opengovernment opensource operand oracle penelope php priceupdating query referencedesign reorderingcolumns saas search select sharepoint simpledb spotify statement sugarcrm syntax techsupport thunderbird transparency update virtualization
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC