Building a Tag Cloud With SQL and ASP.NET

Categories and tags are an extremely valuable addition to any website.
They are an excellent opportunity to include relevant keywords on pages
within your site.  How else can you legally create that kind of keyword rich internal hyperlink
magic?  SEO benefits aside, your users gain simplified access to the
content that they want to see which promotes longer, deeper visits (pageviews!).

Building the Database Structure
We
already have an Articles table in place.  The only reference relevant
here will be the unique ID.  We will have to add a list of the tags and
or categories and a table to link the two.  I will list all the tags
into a table called Tags, and will create a table called ArticleTags to
link the two tables together.

Articles ArticleTags Tags
  • ID
  • Title
  • ID
  • TagID
  • ArticleID
  • ID
  • Tag

The Queries

  • Get Tags For an Article
    • SELECT t.Tag FROM Tags t
      INNER JOIN ArticleTags at ON t.ID = at.TagID
      WHERE at.ArticleID = @articleID
  • Get Articles By Tag
    • SELECT a.ID From Articles a
      INNER JOIN ArticleTags at ON at.ArticleID = a.ID
      WHERE at.TagID = @tagID
  • Get Top Tags (for tag cloud or a simple tag list)
    • SELECT
      TOP 30 t.Tag FROM Tags t
      INNER JOIN (SELECT COUNT(at.ID) as TagCount,
      at.ID FROM ArticleTags at GROUP BY at.ID) as tt
      ON tt.ID = t.ID ORDER
      BY tt.TagCount DESC

      • This creates a list of the top 30 tags
        used.  If you want to create a tag cloud from this result set you will
        have to append another select into this data set in order to put it in
        to alphabetical order.  Changing the sort from tt.TagCoung to t.Tag
        will not put the top 30 tags in to alphabetical order but return the
        first 30 tags.  You can also do this in your business layer using a
        DataTable’s DefaultView.Sort = “Tag ASC”;

You
can use this to get started displaying the top tags around your site.
Simply replace tags with categories and you have a category system in
place as well.  The benefits are plentiful reason enough to implement
these ideas both for you and your users.  The next step is displaying
the Tag Cloud at the proper size which I will cover very shortly.

33 comments » Write a comment

  1. Regarding the database structure, I don’t see the need to have an ID field on the ArticleTags table. Together, the TagID and ArticleID should represent the primary key for that table. And separately, they are both foreign keys to their respective counterparts.

    Below is what I would do, do you see any reason (technical or otherwise) that this wouldn’t be valid?

    CREATE TABLE ArticleTags (
    TagID INT NOT NULL
    , ArticleID INT NOT NULL
    )
    GO
    ALTER TABLE ArticleTags ADD CONSTRAINT PK_ArticleTags PRIMARY KEY CLUSTERED (
    TagID
    , ArticleID
    )

    ALTER TABLE ArticleTags ADD CONSTRAINT FK_ArticleTags_Tags
    FOREIGN KEY (TagID) REFERENCES Tags (TagID)

    ALTER TABLE ArticleTags ADD CONSTRAINT FK_ArticleTags_Articles
    FOREIGN KEY (ArticleID) REFERENCES Tags (ArticleID)

  2. Correct. The version of the ArticleTags table that I have suggested allows for a many to many relationship… It just prevents you from having duplicate Tags on the same Article.

  3. dgxshiny’s version with ID’s will be faster in many cases. A tag hyperlink will show the name of the tag, but the lookups can use the TagID – a lookup by int will be faster than by string.

  4. I was very pleased to find this site.I wanted to thank you for this great read!! I definitely enjoying every little bit of it and I have you bookmarked to check out new stuff you post.

  5. I agree with TAB that there is no need to create ID for the table "ArticleTags". both TagID and ArticleID can be a primary key for the table.

  6. Remember you will not always win. Some days, the most resourceful individual will taste defeat. But there is, in this case, always tomorrow – after you have done your best to achieve success today.

  7. I was very glad to find out this internet site on google.I wished to say quite a few thanks to you with regard to this fantastic publish!! I definitelyliked every little bit of it and I’ve you bookmarked to possess a seem at new stuff you article.

  8. Great website…and cool article man…thanx for the great post…keep on posting such articles… Resources like the one you mentioned here will be very useful to me! I will post a link to this page on my blog. I am sure my visitors will find that very useful.

  9. We’ve got find out a few with the content material posts in your internet web page now, and I genuinely like your kind of blogging. I extra it to my favorites weblog internet site listing and can be checking again swiftly. Please seem into my internet web site as really nicely and let me know what you think about. Numerous thank you for posting this. I really had superb time learning this.

Leave a Reply

Required fields are marked *.