Search code examples
sql-serverxmldatabase-designtagging

Saving data in SQL Server, the right approach

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

Solution

  • articletags_model_01

    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