Questions about a site search
Posted: Tue Jan 27, 2015 1:58 am
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".
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".