Every good database driven website needs a solid, scalable database backend. With bleeek.com I've decided to implement a tagging system for users to label their photos. Tagging is nothing new anymore and so the question is how are sites like del.icio.us pulling it off?
My research led me to an article by Philipp Keller who did a comparision of three database schemas for a tagging system.
* mysqlicious: One table. Tags are space separated in column “tags”; as introduced
* mysqlicious fulltext: Same schema but with mysql fulltext on the tag column; as introduced
* scuttle: Two tables: One for bookmarks, one for tags. Tag-table has foreign key to bookmark table; as introduced
* toxi: Three tables: One for bookmarks, one for tags, one for junction; as introduced
I personally prefer the toxi method, but with the removal of the trivial primary key in the tags mapping table. It is actually a better idea to do a primary key index on tag ID and the other ID that it maps to. Philipp ran some performance tests and found that the toxi schema does not scale as well as the mysqlicious fulltext schema. With the proper modification to the toxi schema and the improvements in the database software, it would be interesting to see some new data.