Is there an agreed ideal schema for tagging
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
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 ;)