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

Question

I haven’t touched any code in a good 4-5 months, so just returning to it today usually takes a week or so to get all the information flowing through my brain again as soon as I take months. So my project, which I am about to start, will be a PHP / MySQL bookmark database.

I want to create a beautiful searchable database with all my favorite websites / bookmarks. Each entry will have several keywords assigned to it, so I can easily find all my bookmarks for the term “php” and all entries with “php” in the column or header of the keyword or otherwise return to the result set.

Here is my database idea 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*/ 

It’s pretty straightforward for me how to build this system, except that I’m looking for help / advice on the best way to store keywords. Each site / record that I add to the database can contain from 1 to several keywords per site. These keywords should be able to help with the search part of my application. So, how do I store keywords for a site in my database? I know that I can only have the string "keywords" in the table and store keywords for each entry, such as "php, web, etc., Keyword 4", so all keywords for each site are stored in 1 column, but that doesn't seem to be the best way when it comes to database searches.

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

Answer 1

The best way to do this is to create a separate table to place your keywords, and then add an intersection table (or join) to join bookmarked keywords.

 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 also insert any keywords that are used and are not yet in the keywords table, as well as a row in bookmark_keywords , to join the bookmark_keywords keyword.

Then, when you want to query which keywords have bookmarks:

 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 which bookmarks have a specific 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] 

Answer 2

You are right, keeping a comma separated list in one column is not the best way to do this (this is called a repeating group, and this violates the first normal form of the relational database structure.

Using the LIKE predicate is not a good choice because it cannot benefit from the index. Searching for keywords in this way is hundreds or thousands of times slower than developing the appropriate database in normal form and adding indexes.

You need to save the second list of keywords in the table and the third many-to-many table to match the keywords with the corresponding bookmarks. This is a pretty standard construction for tagging in a relational database.

In non-relational databases, such as CouchDB or MongoDB, you can make a single field a set of keywords and index them so that queries can be efficient. But not in a relational database.

See also:

• As you recommend to embed tags or tagging
• Database Design for Tags

Also, when viewing these questions, check out the many related questions in the column on the right.

Answer 3

The easiest and fastest way to search is to use the MySQL LIKE statement. LIKE allows you to search columns for a specific row. Consider the following example ...

 auto_id name description 1 Cool PHP Site you know you love it 2 PLARP! its Ruby gems gems gems! 3 SqlWha sql for the masses 4 FuzzD00dle fun in the sun, with some fuzz 

You can find all lines containing the string "php" in the "name" or "description" field using the following query ...

 SELECT * FROM bookmarks WHERE name LIKE '%php%' OR description LIKE '%php%'; 

• '%' is a wildcard.

MySQL LIKE Link: http://www.tutorialspoint.com/mysql/mysql-like-clause.htm

You can also add a “keywords” column and save the keywords in comma-separated format (that is: plarp1, plarp2, plarp3), and then search for them.