how to save tags(keywords) in database?

Question

I want to create a simple tags system using php and mysql, so that users can add few tags via form. My question is that should i save the tags as an array in single database column? eg. "tag1, tag2, tag3".. or i should have separate columns in database table where i should save each tag in each column. i hope my question is clear. Thanks.

Answer 1

I would probably say neither. Use a many-to-many relationship between tags and the object being tagged. For instance, if the thing being tagged is a question, the tables could look like this:

Question:
    QuestionId
    Title
    Body

Tag:
    TagId
    Name

QuestionTags:
    QuestionId
    TagId

Answer 2

came across that issue today as well and gathered some ideas here, and although the question isn't very new, i'll leave my solution as well:

i think the answer Klaus Byskov Hoffmann gave wasn't bad, but I'd add on that and store the tag-list as many-to-many table like he said, but also as serialized string in some form (either via serialize like user466764 said, or just a comma separated thing like you said yourself, which could be handled with implode/explode) in the main table.

yea, i know: storing the same data twice is not very well received with many database perfectionists, as it bears the danger of getting inconsistencies, but i'd do it that way for performance and simplicity:

the many-to-many-table (tag-table) is for search only. to increase search performance, I'd limit access to that table ONLY to search (and of course we need to update it when tags are edited), but never query it just for viewing/listing the tags somewhere. and the serialized tag-list is for every place where you are viewing the article or item in question - when displaying that item, you have the table already, doing yet another query to the tag-table every time you want to display that page is unnecessary when you have the list in the main table already. make sure you are careful when updating the tags to always update them in both places, preferable via a single setter function that does both, and you shouldn't have problems with inconsistency.