<p>I've heard of a few ways to implement tagging; using a mapping table between TagID and ItemID (makes sense to me, but does it scale?), adding a fixed number of possible TagID columns to ItemID (seems like a bad idea), Keeping tags in a text column that's comma separated (sounds crazy but could work). I've even heard someone recommend a sparse matrix, but then how do the tag names grow gracefully?</p>

<p>Am I missing a best practice for tags?</p>

[{'answer_id': 20871, 'author': 'Yaakov Ellis', 'author_id': 51, 'author_profile': 'https://Stackoverflow.com/users/51', 'pm_score': 10, 'selected': True, 'text': '<p>Three tables (one for storing all items, one for all tags, and one for the relation between the two), properly indexed, with foreign keys set running on a proper database, should work well and scale properly. </p>\n\n<pre><code>Table: Item\nColumns: ItemID, Title, Content\n\nTable: Tag\nColumns: TagID, Title\n\nTable: ItemTag\nColumns: ItemID, TagID\n</code></pre>\n'}, {'answer_id': 20873, 'author': 'Mark Biek', 'author_id': 305, 'author_profile': 'https://Stackoverflow.com/users/305', 'pm_score': 3, 'selected': False, 'text': '<p>I\'ve always kept the tags in a separate table and then had a mapping table.  Of course I\'ve never done anything on a really large scale either.</p>\n\n<p>Having a "tags" table and a map table makes it pretty trivial to generate tag clouds &amp; such since you can easily put together SQL to get a list of tags with counts of how often each tag is used.</p>\n'}, {'answer_id': 48348, 'author': 'David Schmitt', 'author_id': 4918, 'author_profile': 'https://Stackoverflow.com/users/4918', 'pm_score': 4, 'selected': False, 'text': '<p>Use a single formatted text column[1] for storing the tags and use a capable full text search engine to index this. Else you will run into scaling problems when trying to implement boolean queries.</p>\n\n<p>If you need details about the tags you have, you can either keep track of it in a incrementally maintained table or run a batch job to extract the information.</p>\n\n<p>[1] Some RDBMS even provide a native array type which might be even better suited for storage by not needing a parsing step, but might cause problems with the full text search.</p>\n'}, {'answer_id': 48714, 'author': 'Nick Retallack', 'author_id': 2653, 'author_profile': 'https://Stackoverflow.com/users/2653', 'pm_score': 5, 'selected': False, 'text': '<p>If you are using a database that supports map-reduce, like couchdb, storing tags in a plain text field or list field is indeed the best way.  Example:</p>\n\n<pre><code>tagcloud: {\n  map: function(doc){ \n    for(tag in doc.tags){ \n      emit(doc.tags[tag],1) \n    }\n  }\n  reduce: function(keys,values){\n    return values.length\n  }\n}\n</code></pre>\n\n<p>Running this with group=true will group the results by tag name, and even return a count of the number of times that tag was encountered.  It\'s very similar to <a href="http://web.archive.org/web/20090107022604/http://jchris.mfdz.com/posts/107" rel="noreferrer">counting the occurrences of a word in text</a>.</p>\n'}, {'answer_id': 18923641, 'author': 'Scheintod', 'author_id': 1455622, 'author_profile': 'https://Stackoverflow.com/users/1455622', 'pm_score': 7, 'selected': False, 'text': '<p>Normally I would agree with Yaakov Ellis but in this special case there is another viable solution:</p>\n\n<p>Use two tables:</p>\n\n<pre><code>Table: Item\nColumns: ItemID, Title, Content\nIndexes: ItemID\n\nTable: Tag\nColumns: ItemID, Title\nIndexes: ItemId, Title\n</code></pre>\n\n<p>This has some major advantages:</p>\n\n<p>First it makes development much simpler: in the three-table solution for insert and update of <code>item</code> you have to lookup the <code>Tag</code> table to see if there are already entries. Then you have to join them with new ones. This is no trivial task.</p>\n\n<p>Then it makes queries simpler (and perhaps faster). There are three major database queries which you will do: Output all <code>Tags</code> for one <code>Item</code>, draw a Tag-Cloud and select all items for one Tag Title.</p>\n\n<p><strong>All Tags for one Item:</strong></p>\n\n<p>3-Table:</p>\n\n<pre><code>SELECT Tag.Title \n  FROM Tag \n  JOIN ItemTag ON Tag.TagID = ItemTag.TagID\n WHERE ItemTag.ItemID = :id\n</code></pre>\n\n<p>2-Table:</p>\n\n<pre><code>SELECT Tag.Title\nFROM Tag\nWHERE Tag.ItemID = :id\n</code></pre>\n\n<p><strong>Tag-Cloud:</strong></p>\n\n<p>3-Table:</p>\n\n<pre><code>SELECT Tag.Title, count(*)\n  FROM Tag\n  JOIN ItemTag ON Tag.TagID = ItemTag.TagID\n GROUP BY Tag.Title\n</code></pre>\n\n<p>2-Table:</p>\n\n<pre><code>SELECT Tag.Title, count(*)\n  FROM Tag\n GROUP BY Tag.Title\n</code></pre>\n\n<p><strong>Items for one Tag:</strong></p>\n\n<p>3-Table:</p>\n\n<pre><code>SELECT Item.*\n  FROM Item\n  JOIN ItemTag ON Item.ItemID = ItemTag.ItemID\n  JOIN Tag ON ItemTag.TagID = Tag.TagID\n WHERE Tag.Title = :title\n</code></pre>\n\n<p>2-Table:</p>\n\n<pre><code>SELECT Item.*\n  FROM Item\n  JOIN Tag ON Item.ItemID = Tag.ItemID\n WHERE Tag.Title = :title\n</code></pre>\n\n<p>But there are some drawbacks, too: It could take more space in the database (which could lead to more disk operations which is slower) and it\'s not normalized which could lead to inconsistencies.</p>\n\n<p>The size argument is not that strong because the very nature of tags is that they are normally pretty small so the size increase is not a large one. One could argue that the query for the tag title is much faster in a small table which contains each tag only once and this certainly is true. But taking in regard the savings for not having to join and the fact that you can build a good index on them could easily compensate for this. This of course depends heavily on the size of the database you are using.</p>\n\n<p>The inconsistency argument is a little moot too. Tags are free text fields and there is no expected operation like \'rename all tags "foo" to "bar"\'.</p>\n\n<p>So tldr: I would go for the two-table solution. (In fact I\'m going to. I found this article to see if there are valid arguments against it.)</p>\n'}, {'answer_id': 33969961, 'author': 'user236575', 'author_id': 3279014, 'author_profile': 'https://Stackoverflow.com/users/3279014', 'pm_score': -1, 'selected': False, 'text': '<p>I would suggest following design : \nItem Table: \nItemid, taglist1, taglist2<br>\nthis will be fast and make easy saving and retrieving the data at item level. </p>\n\n<p>In parallel build another table: \nTags \ntag    \ndo not make tag unique identifier and if you run out of space in 2nd column which contains lets say 100 items create another row. </p>\n\n<p>Now while searching for items for a tag it will be super fast.</p>\n'}]
['https://Stackoverflow.com/questions/20856', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/459/']