Time for an open puzzle, answers on a postcard, or preferably in the comments box below
Imagine you are implementing a typical web 2.0 application. It has cool things, like video clips, and maps. Lets keep it simple for now, and say it only has status messages and video clips. The two are unrelated, sharing almost no common features (you can’t play a map, or add a way-point to a video). There is just one common feature that you have to have to concern yourself with, tags.
Both video clips and maps can be tagged, when you see a video clip tagged with “dog” you should be able to click on “dog” to see all the video clips tagged with “dog” as well as all the maps tagged with “dog”
The question is how would you store this in the database. Specifically I’m looking for a database schema, what tables would you use, and what columns? The question is intentionally stripped to its bare minimum, the extension would be to allow lots of other things to be tagged, and to allow tag based queries (such as retrieving all tags related to a given tag) to be executed as fast as possible.