Saving data in SQL Server, the right approach

Question

We have internal application used to publish articles, using SQL Server 2005.

For the task I need to save list of tags when a user publishes a post.

Should I make a separate table For Tags and update the ID column for a TAG with the id of the post that lists it, using XML column like

TABLE_TAGS
TAG_NAME varchar, ARTICLE_IDS XML

OR

make a column in ARTICLE table itself like

TABLE_ARTICLE 
COLUMN_TAGS XML //  and store tag, the post is associated with.

When a user clicks particular tag in the "TAG cloud", we need to bring up the posts listed with that tag, just like any blog. We will need nested SQL statements as well to bring up the posts with more hits or discussions etc, like

SELECT ARTICLE_TITLE, ARTICLE_URL FROM
TABLE_ARTICLE 
WHERE
ARTICLE_ID = 
(SELECT ARTICLE_IDS FROM TABLE_TAGS
WHERE TAG_NAME = @TAGTOFIND)

I am not sure how and what is the best approach in terms of adding, update or efficiency.

Any other hint ?

Thanks

Answer 1

The usual way to handle many-to-many relationships is to use a join table (aka junction table, bridge table, through table, etc....). Thus, you would have a table of Tags, a table of Articles, and a third table of "tag used on article" which just had TagId and ArticleId foreign keys. To find out which tags were associated with any given article, or which articles were associated with any given tag, you would perform a join query on the target table (Tags or Articles) and the join table.

Answer 2

If it were me - and this may not be right...

I'd go with two tables in addition to your article table:

• One table for Tags: ID, Tag
• One table to link Articles to tags: ArticleID, TagID

This will involve a chunk of work in maintaining an article's tags (parsing the tags list, does the tag exist, if not create, etc) but then the other side (search - both by tag and for tags, display, counts for your tag cloud) should be both easier and, more importantly, faster as you can index things. Since you should be doing read far more than write speed when searching is the key performance target.

Answer 3

Storing XML in a database is not naturally good for performance. Consider storing the tags to article relation in a normalized manner, by having a many-to-many table that implements the relation:

Table: ArticleTag

ArticleId int
TagId int

This allows you to query tags and articles without resorting to XML support in the database.

Answer 4

SELECT  ArticleTitle
       ,ArticleURL
FROM    Article AS a
        JOIN Article_Tag AS x ON x.ArticleID = a.ArticleID
        JOIN Tag AS t ON t.TagID = x.TagID
WHERE   t.TagName = @SomeTag