Is there an agreed ideal schema for tagging

Question

I have a photo website and i want to support tags as my original category bucketing is starting to fail (some pictures are family and vacations, or school and friends). Is there an agreed tagging db schema?

I still want to support having photos as part of an album.

Right now i have a few tables:

Photos

• PhotoID
• PhotoAlbumID
• Caption
• Date

Photo Album

• AlbumID
• AlbumName
• AlbumDate

Answer 1

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 ;)

Answer 2

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!

Answer 3

Something like this comes to my mind: add those two tables

Tags

• TagID
• TagName
• TagDescription

PhotoTags

• PhotoID
• TagID

You can extend this to albums too, having an intersection table between Photo Albums and Tags.

Answer 4

I suggest looking to see how established open-source software does it. For example, Gallery stores its meta-data in a database like you do, and is pretty rich.

I don't think you'll find a "standard" schema, though. The closest thing I can think of is the EXIF meta-data format, which is embedded within image files themselves (by cameras, etc).