Keywords, what would be the wisest setup.

[fortnox007]

Hi all,

 

I have a small question about which way would be the best to  set-up the following. Say i have a Job board and one is allowed to give a few keywords related to the post with for instance a maximum of 5 keywords.

what would be the best way to store these words in a database.

 

1) should i add an extra column tot the table comments and put in the keywords for instance comma separated and explode() them when needed. ie:

#table comments:| comment_id |   comment   | title   | author| category_id | posted  |  keywords

 

2) should I make an extra table with keywords and maybe even an extra table to link keywords to comments?

ie:

#table keywords:| keyword_id | keyword

extra table:

#table keys_per_comment: | kpc_id | keyword_id | comment_id

 

The first method is easiest for me i think, but I am not sure which one would be fastest or better for scalability reason. if anyone has tips or ideas, or a better way i would love to hear it since i never really worked with these kind of relationships.

ty

[joel24]

I would definitely go with the 2nd option (an association table).

That way you would be able to pull the applicable rows with one single mysql query (job board table JOIN keyword table) rather than querying the job board table, pulling all rows and then reading keywords in PHP.

[fortnox007]

Okay cool i am going to try and set that up. Hope someone wants to audit  it when it's finished

thanks m8 for the advice!