Saving "Tags" - Quickest Way

Question

I want to save tags related to a discussion post. The same as the 'tags' that Stack Overflow uses. What is the best way to do this?

I was thinking of saving them in the 'discussion' table as a single comma separated string, but I'm not sure if this is the 'fastest' way assuming there will be lots and lots of rows in the discussion table.

Answer 1

In general you shouldn't store tags as a single comma separated field. You would not be able to define an index on the separate tags. In this case, a query for a tag would require a full table scan, which will be slow, and will become slower as you add more rows to the table.

You may be interested in checking out earlier Stack Overflow questions, which discuss this topic further:

• What tag schema(s) are the most efficient/effective?
• Database design for tagging
• How to model tags in the database?
• What is the most efficient way to store tags in a database
• How to design a database schema to support tagging with categories
• Storing Tags in Database. Store tag once or many times?
• Is there an ideal schema for tagging?
• Scalable Database Tagging Schema
• How do you recommend implementing tags or tagging
• Explain db tag storage method

Answer 2

Storing tags in a single field is definitely a poor design decision since you won't be able to index the field. Depending on the expected size of the tables, a simple relational design such as the one below could be all you need.

Tags:

id | tag
------------
 1 | mysql
 2 | postgres
 3 | mssql

Discussions:

id | something | something else
-------------------------------
 1 | abcde     | asdfg
 2 | fghij     | hkijk
 3 | klmno     | lmnop

Tags_2_discussions:

id | tag | discussion
----------------------
 1 | 1   | 1
 2 | 2   | 4
 3 | 2   | 4

Answer 3

I use a very simple structure.

Basically, you have a tag table with the tag name and an RID in it, then a tag_link that has a tag_rid in it and a owner_post that is a foreign key of your discussion post table.

I find it works well... though its bad that you must join 2 tables to get the list of tags..

Answer 4

How about thinking out of the RDBMS box and applying a Semantic Web/RDF based approach?

You could use something like 4Store with the SPARQL frontend and simple triplets (turtle notation) for the tags.