Hi all,

I need some advice

I want to implement a tagging system to an article script instead of the traditional category-based system.

For example, user writes their article then enters one or more tags "php, database, zend" for it. These tags are then used when searching to bring up relavent articles.

I have come up with three ideas for the design of the tagging system:

Idea 1

The articles table holds an additional field called tags where the comma-seperated list of tags are stored.

When saving tags we just update that articles tags column.

When searching for tags, a simple WHERE tags LIKE %$tag% query is used to get matching articles

When generating tag clouds, a query is run to fetch everything in the tags column then PHP tags over by splitting/merging/removing dupes from the tags array until we have our final list of tags.

Idea 2

Our articles table remains un-touched and we add a second table called tags This table has a structure similar to the following:

tag_id - An auto_increment id for this tag
tag - The tag itself (eg, "php")
articles - a space-seperated list of article_id's that use this tag (eg, "1 4 15 18 19 76")

When saving tags we would have to fetch the article_ids column and add/remove numbers as needed then do an SQL update on it.

When searching for tags we just do a simple search of the tags table to return article_id's - we then do another query to fetch those articles.

When generating tag clouds we just select the tags column and a count(article_ids) to generate the weighting, etc.

Idea 3

This one has three tables:

articles
tags
article_tags

Articles is just the regular un-touched articles table.

Tags would have the following structure:

tag_id - An auto_incrment id for this tag
tag - the tag itself

article_tags would have the following structure:

tag_id - links to the tag in the tags table
article_id - links to the article in the [inline]articlesp/inline] table

When saving tags, you just add a new tag to the tags/article_tags tables. When removing a tag you would just remove the row from the articles_tags table.

When searching for tags you would do a simple join query since all fields have a single value it would be simple.

When generating tag clouds you would just do a join on the tags table with a count(tag_id) for weighting

--------

Those are the options so far and I am leaning towards number 3 as it seems to be the most logical.

What do you guys thing? Anything I'm missing? Any other options you can think of?

Thanks,
Alan