Allowing search through tags?

Question

There is an HTML form through which a user creates an item. Items are searched using usual sql query. Now a tag field will be supplied for each item on the HTML form. So while creating an item the user will enter a tag name for the item and the item will be created along with the tag name. User will then use this tag name to search for items.

What could be the best way to implement this? Is adding a tag column to every Item table a good way of doing it? What are the other possible options?

Answer 1

I believe you would want to minimize round trips to a database server. Tags would likely be an attribute of one of the items of interest and it is a many to many relationship. This is a well trodden path and you can find more information here.

Answer 2

If you want to be able to add multiple tags, then the best way would be to implement a table like this:

 item_tag
      item_id - Integer
      tag_name - Varchar

or two tables:

 item_tag
      item_id
      tag_id
 tags
      id
      name

Use the post_tag table to contain a list of which tags are associated with which item. If you want to search for items by tag, then you could do a

 SELECT * FROM `items` LEFT JOIN `item_tag` ON `items`.`id` = `item_tag`.`item_id` WHERE `tag_name` = 'value'

Here is a post discussing the best way to implement a tagging system, which suggests using three tables; e.g., items, tags, items_tags