Tags Problem

same table or different table

[kkonline]

In the article manager i am working, i am currently storing all the tags (separated by a delimiter) in the article table only.
Means For article id 3 if the tags are life,success,emotion then i am storing them as it is in the article table field "tags"

Then as discussed earlier i am separating the words in the array and then using a for loop i an making a query to show all the related articles having same tags.

OPTION2
My second option is to use another table called article_tags and store the corresponding article id and only one tag associated with it.

example: id, articleid, tag will have data like 1, 2, life in 1st row
1, 2, success in 2nd row and 1, 2, emotion in 3rd row . Assuming tags are life,success,emotion for article 2.

Out of above two options which would be preferrable keeping in mind that the site will have tremendous amount of data shortly? Any other way of storing the tags which is efficient?

[borntobeweb]

Will you ever want to find all articles associated with a certain tag? With option 1, you'll have to do a query like

select * from article where tags like '%$search_tag%'

which does a full table scan. That's ok if you don't have many articles, slow otherwise. With option 2, your query will look like

select article.* from article, article_tags where tag = '$search_tag' and article.articleid = article_tags.articleid

much more efficient if you have an index on the tag field.

I'd take it one step further and normalize the article_tags table, i.e. create a separate tag table with tagid & tagname, and in the article_tags table have id, articleid, tagid (not sure what id is for). I think that'll save you space in the long run too.