Toxi Method

Toxi came up with a three-table structure. Via the table “tagmap” the bookmarks and the tags are n-to-m related. Each tag can be used together with different bookmarks and vice versa. This DB-schema is also used by wordpress.

The queries are quite the same as in the “scuttle” solution. Intersection (AND)

Query for bookmark+webservice+semweb

SELECT b.* FROM tagmap bt, bookmark b, tag t WHERE bt.tag_id = t.tag_id AND (t.name IN ('bookmark', 'webservice', 'semweb')) AND b.id = bt.bookmark_id GROUP BY b.id HAVING COUNT( b.id )=3

Union (OR)

Query for bookmark|webservice|semweb

SELECT b.* FROM tagmap bt, bookmark b, tag t WHERE bt.tag_id = t.tag_id AND (t.name IN ('bookmark', 'webservice', 'semweb')) AND b.id = bt.bookmark_id GROUP BY b.id

Minus (Exclusion)

Query for bookmark+webservice-semweb, that is: bookmark AND webservice AND NOT semweb.

SELECT b. * FROM bookmark b, tagmap bt, tag t WHERE b.id = bt.bookmark_id AND bt.tag_id = t.tag_id AND (t.name IN ('Programming', 'Algorithms')) AND b.id NOT IN (SELECT b.id FROM bookmark b, tagmap bt, tag t WHERE b.id = bt.bookmark_id AND bt.tag_id = t.tag_id AND t.name = 'Python') GROUP BY b.id HAVING COUNT( b.id ) =2

Leaving out the HAVING COUNT leads to the Query for bookmark|webservice-semweb. Credits go to Rhomboid for helping me out with this query. Conclusion

The advantages of this solution:

You can save extra information on each tag (description, tag hierarchy, …)
This is the most normalized solution (that is, if you go for 3NF: take this one :-)

Disadvantages:

When altering or deleting bookmarks you can end up with tag-orphans.