Is there an agreed ideal schema for tagging

I've done this in a small system without very many users, but I've wondered before if there was an "accepted" way to manage tags. After reading through the links posted by insin and plenty of other blog posts on tagging, it seems that the accepted way is to store it fully normalized and cache certain things if your data set gets too big.

Since it's a many-many relationship (each tag can belong to any number of photos - each photo can have many tags), relational database theory has you create a photo table, a tag table and a cross-reference table to link them.

photos
  photoid
  caption
  filename
  date

tags
  tagid
  tagname

phototags
  photoid
  tagid

This has scaling problems selecting from really large datasets, but so do all the less-normalized schemas (sorting and filtering by a text field will probably always be slower than using an integer, for example). If you grow as large as delicious or maybe even StackOverflow you'll probably have to do some caching of your tag sets.

Another issue you'll have to face is the issue of tag normalization. This doesn't have anything to do with database normalization - it's just making sure that (for example) the "StackOverflow", "stackoverflow" and "stack overflow" tags are the same. Lots of places disallow whitespace or automatically strip it out. Sometimes you'll see the same thing for punctuation - making "StackOverflow" the same as "Stack-Overflow". Auto-lowercasing is pretty standard. You'll even see special case normalization - like making "c#" the same as "csharp".

Happy tagging!

There are various schemas which are effective, each with their own performance implications for the common queries you'll need as the number of tagged items grows:

• http://howto.philippkeller.com/2005/04/24/Tags-Database-schemas/
• http://howto.philippkeller.com/2005/06/19/Tagsystems-performance-tests/

Personally, I like having a tag table and a link table which associates tags with items, as it's denormalized (no duplication of tag names) and I can store additional information in the link table (such as when the item was tagged) when necessary.

You can also add some denormalised data if you're feeling frisky and want simple selects at the cost of the additional data maintenance required by storing usage counts in the tag table, or storing tag names which were used in the item table itself to avoid hitting the link table and tag table for each item, which is useful for displaying multiple items with all their tags and for simple tag versioning... if you're into that sort of thing ;)