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.

Antoinette

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

Suberin

This way provides scalability and allows for database optimisation with indices. Good answer. – Armpit

I landed on this page searching for recommendations for updating a tag list in such a scheme. How would you do it? DELETE FROM QuestionTags WHERE QuestionId = 123 and then INSERTing INTO QuestionTags all the remaining and new tags? It seems the easiest for the developer but might not be the best for the performance... – Reorder

@Reorder I'm not sure I understand what you mean. Can you ask a separate question? – Suberin

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.

Wye

Totally agree. Page load time optimization should be just as important as database normalization. – Interflow

Answer 3

You could use serialise and unserialise to store the keywords in one field. More info here http://php.net/manual/en/function.serialize.php

Something like this...

$keywords = array('apple', 'pear', 'banana', 'peach');
$keywords_serialized = serialize($keywords);
$sql = INSERT INTO dbtable (keywords) VALUES ($keywords_serialized);

Kila

It should be a very rare thing that a serialized string should be stored in a database. – Armpit

Nice way to bloat any database. – Serviette