PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Sun May 24, 2020 4:27 pm

All times are UTC - 5 hours




Post new topic Reply to topic  [ 4 posts ] 
Author Message
PostPosted: Tue Jan 27, 2015 2:58 am 
Offline
DevNet Master
User avatar

Joined: Sun Feb 15, 2009 12:08 pm
Posts: 2794
Location: .za
I'm writing search code and I'm conflicted about the process of getting the results back to the user.

At the moment I have 2 tables, food_tbl and food_tag_tbl. The first table will house information about a specific food while the second will be the table that is used for the search.

Columns for the food_tag_tbl are as follows:
1. id
2. food_name
3. food_tag

Entries into the tag table will be determined by the name of the food, i.e Beef, Fillet, Lean, Raw. In the tag table i would have the following entries:


Beef, Fillet, Lean, Raw | Beef
Beef, Fillet, Lean, Raw | Fillet
Beef, Fillet, Lean, Raw | Lean
Beef, Fillet, Lean, Raw | Raw


The conflict comes on how to retrieve the information that will be used to display the result. food_tbl has an id column, auto-increment and PK, that i will in the query string to retrieve more specific information from the database. My thoughts at the moment:

1. Do i put a foreign key column into the tag table, which ties together with the id key in the food table, and on finding a match return that id value to be used in a hyperlink?
2. Do i retrieve the name of the item from the tag table, and then retrieve the id from the food table to be used in a hyperlink?

I am leaning towards option 1 at this stage because I don't want there to be unnecessary code that goes back and forth between the two tables. If possible I'd like table 1 to be the "information table" while table 2 is strictly a "search table".

_________________
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering


Top
 Profile  
 
PostPosted: Tue Jan 27, 2015 3:55 am 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6617
Location: WA, USA
1. Your table should definitely be using the ID instead of the name of the food.
2. If you're searching for a particular tag then you can get the ID back, yes, but you could also JOIN in the food table and get its information immediately. At the very least you'll want to display the name of the food in the search results, right? So you kinda have to.


Top
 Profile  
 
PostPosted: Tue Jan 27, 2015 7:31 am 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6425
Location: Montreal, Canada
I definitely agree with the foreign key and possibly returning joined information from the food table. I say possibly because you already have the food name in the tag table and additional information may or may not be needed on the search results page. Another nice option would be to slugify the food names and return that rather than an ID. site.com/foods/almonds-roasted conveys more meaning than site.com/foods/678326

_________________


Top
 Profile  
 
PostPosted: Tue Jan 27, 2015 3:19 pm 
Offline
DevNet Master
User avatar

Joined: Sun Feb 15, 2009 12:08 pm
Posts: 2794
Location: .za

_________________
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 4 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 3 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group