Best way to store tags in a sql server table?

Question

What's the best way to store tags for a record? Just use a varchar field? What about when selecting rows that contains tag x? Use the like operator?

thanks!

Answer 1

Depends on two things:
1) The amount of tags/tagged records
2) Whether or not you have a religious opinion on normalization :-)

Unless dealing with very large volumes of data, I'd suggest having a 'Tags' table mapping varchar values to integer identifiers then second table mapping tagged records to their tag ids. I'd suggest using implementing this first, then check if it doesn't meet your performance needs. In which case keep a single table with a id for the tagged row and the actually text of the tag, but in this I'd suggest you use a char column as it will kill your query if the optimizer does a full table scan against a large table with a varchar column.

Answer 2

No, it is generally a bad idea to put multiple pieces of data in a single field. Instead, use a separate Tags table (perhaps with just a TagID and TagName) and then, for each record, indicate the TagID associated with it. If a record is associated with multiple tags, you will have duplicate records with the only difference being TagID.

The advantage here is that you can easily query by tag, by record, and maintain the Tags table separately (i.e. what if a tag name changes?).

Answer 3

Some ideas and tests just for you: http://www.pui.ch/phred/archives/2005/06/tagsystems-performance-tests.html

Answer 4

Use a tags table with the smallest allowable primary key. If there are less than 255 tags use a byte (tinyint) or else a word (smallint). The smaller the key the smaller and faster the index on the foreign key in the main table.