Best way to store and search keywords for a record in PHP and MySQL?

Question

I haven't touched any code in a good 4-5 months so just getting back into it today, usually takes me a week or so to get all the info flowing through my brain again once I take months off like that. So my project I am about to start will be a PHP/MySQL backend bookmarks database.

I want to create a nice searchable database with all my favorite websites/bookmarks. Each record will have multiple keywords assigned to it so I can easily search all my bookmarks for the term "php" and all records with "php" in there keyword column or title or otherwise will come back in a result set.

Here is my idea for the database so far...

auto_id = /*Auto incremented ID number for database*/
name/title = /*Name/title of the Website*/
description = /*brief description of the site*/
URL = /*URL to open when I click a link*/
clicks = /*increments by 1 everytime I click the link*/
date_created = /*datetime that URL bookmark was added*/
date_accessed   = /*datetime field for when last clicked on*/
category = /*category name or number to create a folder like structure of bookmarks in groups*/
sub_category = /*some categories will have subcategories (ie programming->c##  programming->PHP )*/
keywords = /*Keywords used for searching*/

This is pretty straight forward for me on how to build this system all except I am looking for help/advice on the best way to store the keywords. Each website/record I add to the DB can have 1 up to multiple keywords per site. These keywords need to be able to help with the searching part of my app. So how should I store keywords for a site in my database? I know I could just have a "keywords" row in the table and store the keywords for each record like this "php, web, etc, keyword4" so all keywords for each site are saved in 1 column but this does not seem to be the best method when it comes to searching the database.

Please tell me how you would do this part? Thanks for any help

Answer 1

The best way to do this is to create a separate table to contain your keywords and then add an intersection (or join) table to join keywords with bookmarks.

CREATE TABLE bookmarks (
  id INT NOT NULL,
  ... etc.
)

CREATE TABLE keywords (
  id INT NOT NULL,
  ... etc.
)

CREATE TABLE bookmark_keywords (
  bookmark_id INT NOT NULL,
  keyword_id INT NOT NULL,
  PRIMARY KEY (bookmark_id, keyword_id),
  FOREIGN KEY bookmark_id REFERENCES bookmarks (id),
  FOREIGN KEY keyword_id REFERENCES keywords (id)
)

When you insert a bookmark, you'd also insert any keywords that are being used and aren't already in the keywords table, as well as a row in bookmark_keywords in order to join the keyword with the bookmark.

Then, when you want to query for what keywords a bookmark has:

SELECT k.*
FROM keywords AS k
LEFT JOIN bookmark_keywords AS kb
  ON kb.keyword_id = k.id
WHERE kb.bookmark_id = [ID of the bookmark]

And to query for what bookmarks share a particular keyword:

SELECT b.*
FROM bookmarks AS b
LEFT JOIN bookmark_keywords AS kb
  ON kb.bookmark_id = b.id
WHERE kb.keyword_id = [ID of the keyword]