•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MySQL section within the Web Development category of DaniWeb, a massive community of 423,398 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 4,504 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MySQL advertiser: Programming Forums
Views: 1239 | Replies: 5 | Solved
 |
•
•
Join Date: Aug 2004
Posts: 18
Reputation: 
Rep Power: 5
Solved Threads: 0
Newbie Poster
Varchar field as foreign reference 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 is the pk, family_id, genus_id, species_id are foreign keys.
soon i will change it to
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...
here's the first few fields in the table plants:
plant_id, family_id, genus_id, species_id, auth1, auth2, ...
soon i will change it to
plant_id, family_name, genus_name, species_name, auth1, auth2, ...
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...
•
•
Join Date: Sep 2007
Location: North Bay Ontario
Posts: 176
Reputation:
Rep Power: 2
Solved Threads: 20
Junior Poster
•
•
•
•
Originally Posted by 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 is the pk, family_id, genus_id, species_id are foreign keys.plant_id, family_id, genus_id, species_id, auth1, auth2, ...
soon i will change it to
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?plant_id, family_name, genus_name, species_name, auth1, auth2, ...
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!"
"Others make web sites. We make web sites work!"
•
•
Join Date: Aug 2004
Posts: 18
Reputation:
Rep Power: 5
Solved Threads: 0
Newbie Poster
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
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
•
•
Join Date: Sep 2007
Location: North Bay Ontario
Posts: 176
Reputation:
Rep Power: 2
Solved Threads: 20
Junior Poster
•
•
•
•
Originally Posted by 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
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!"
"Others make web sites. We make web sites work!"
•
•
Join Date: Aug 2004
Posts: 18
Reputation:
Rep Power: 5
Solved Threads: 0
Newbie Poster
ok cool thanks i'll check them out and/or some other ones possibly
•
•
Join Date: Aug 2004
Posts: 18
Reputation:
Rep Power: 5
Solved Threads: 0
Newbie Poster
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
but thanks anyways hope this might help someone having similar problems
|
•
•
•
•
•
•
•
•
DaniWeb MySQL Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
Similar Threads
- Searching a Text Field in a JList (Java)
- unserialize an array! spitting out jibberish (PHP)
- mysql date ALWAYS = 0000-00-00 00:00:00 (PHP)
- <script language=\"javascript\"> Display problem with PHP & MYSQL (PHP)
- Nesting A Table (C#)
- Display field as link (PHP)
- figuring out PK/FK (MS SQL)
Other Threads in the MySQL Forum
- Previous Thread: PHP/MySQL - Need help figuring out if this will work
- Next Thread: how to point the path to IMAGE folder for MYSQL PHP database
