Dagoosh!

Randomsauce

Building a Tag Cloud With SQL and ASP.NET

Posted Tue, Mar 17, 2009

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.

Tagged tag cloud asp.net sql

More from Dagoosh!
Shakira

Midway's The GRID: What You Get

Ode to the McGriddle

Pears Before Twitter

Google: Press Enter to Search

© 1999 - 2024 Dagoosh! LLC