For the folks I promised an article on how to implements tags in SQL. Sorry, I never finished it. I got sucked into some more esoteric problems, and never got back to writing down the basics. However, all is not lost. Peter has done a very nice job of writing about how he implemented tagging for his snippets code libraries.

  1. Matching on data intersection across a many-to-many join – aka, how to find an object (e.g. a post or a recipe) with two or more tags (i.e. show my all posts tagged with tagging and sql). For the lazy, skip to bottom for a solution. (but it’s worth reading through) This is what enables tag combos. (Also available from snippets)
  2. Find items with similar (or as many as possible) relationships – for a ‘related posts’ box etc – aka, find other recipes tagged with similar tags to the recipe I’m currently looking at I’m currently looking at. In Recipes on Rails, if I’m looking at my recipe for hot chocolate (tags: beverage, hot, chocolate), use this query to also show tea and coffee. (both tagged beverage and hot)
  3. Find all many-to-many relationships which are tied to an arbitrary number of other many-to-many relationships – I’m looking at all recipes with the tags easy, and hot, what are related tags I could use? How about carrot, and curry (my curry carrot soup is tagged easy, soup, carrot, and curry, not to mention winter and favorite), or maybe silly with which I’ve tagged my boiled water recipe.
    After some futzing I came up with the same query that Peter did, but I was really hoping there was another solution, as I’ve been seeing some of the same worrying numbers using MySQL 4.1.x’s subqueries that Kevin is.

There you go, you no longer have any excuse for building a sub-par tagging system.

fyi, Peter, from whom all those tips originate, on top of being the creator of the snippets site, is available for hire.