dev|sushi

I’ve worked with databases on average about four days a week for the last two years. My experience with databases includes: taking over maintenance, creating from scratch, incrementally improving and even completely redesigning a few. The experience has given me a lot of practice when it comes to design choices but not enough to truly be called a “Database Manager”. It takes a lot of knowledge and experience to know exactly where to put an index to maximize performance or how best to structure your tables to minimize the need for join queries. I recently read some great articles about how best to implement “tags” structurally and I thought I would share them.

I decided to add tagging to a web app that I’m working on in my spare time and wondered after the best way to do it in the database. I was curious if current services that use tags (flickr, del.icio.us, etc) did it using a normalized or denormalized schema. The first article I came across was “Tags: Database schemas” by Philipp Keller which gave three example schemas and expounded on thier different pros and cons. I quickly realized as I was reading that the answer depends greatly on the question (as is usually the case) and how you want to implement tagging is the first thing to figure out. Putting the how aside I continued reading the article and the great comments. One of the commenter’s was Nitin Borwankar who mentioned his blog focused solely on tagging schemas for the new breed of “folksonomy” based tools. I immediately read the archive from start to finish and added his RSS feed to my GReader. The main thing that I got out of his blog was that if your going to do tagging you might as well do it right and treat tags as full-fledged schema elements.

In designing the schema for my new project I decided to try using the “rules” set out by Ruby On Rails for table name, private and foreign key, association tables, etc. The gist of it is that if you have a table that will contain employees you call it “employees” (ie use the plural form) and inside use “id” (lowercase) for the primary key. So departments would be “departments” with primary key “id” and the “employees” table would contain a foreign key called “department_id” (non-plural form beside _id), simple right? Associations are equally simple for those many-to-many relationships. We create the table “projects” with primary key “id” (guess what it holds) and the association table “employees_projects” with the foreign keys “employee_id” and “project_id”. A trinary relationship would be “departments_employees_projects” (note the alphabetical order of tables) although I don’t think that relationship makes sense for traditional corporations. If anyone can find me a url to reference for the “rules” I just described drop it in the comments. I’ll have more information about my spare time project in the future but for now I hope you this database stuff useful.