Using GMail as a Cheap, Multi-User Relational Database

- - posted in databases, francisco,, gmail,, san

After many years of calling 849 Fell Street their home, my roommates Tom and Ted are moving out at the end of the month. Jeremy and I have been relying principally on Craigslist to find new roommates. Rather than direct incoming applications to either (or both) of our personal inboxes, we opted to create a new, special-use Gmail account (appropriately named 849fellstreet@gmail.com). That much isn’t novel, but we’ve also developed a quick and easy system for categorizing and responding to applicants that I want to share.

As a Rails app, it would be natural to break the data into a few tables: “roommates”, “applicants”, “ratings”, “appointments”, and “correspondences”. In our case there are only two rows in the “roommates” table: Jeremy and me. Every reply to our Craigslist ad creates rows in “applicants” and “correspondences”. Assuming Jeremy likes an application, it creates a row in the “ratings” table (e.g. roommate: 1, applicant: 37, rating: “approve”). One of us replies, creating a new row in “appointments”, etc. That’s a lot of work, which is why I’m happy to have found a way to do the same thing inside Gmail.

Because Craigslist correspondences happen over email anyway, every Gmail thread is a row in “applicants”, plus >1 row in “correspondences”. Instead of tables for “ratings” and “appointments”, labels can be used to track the status of applicants (e.g. JeremyLikes, JeremyPass, InterviewScheduled, RejectionSent).

Generous labeling affords other useful tricks. Add the “Quick Links” lab and save custom, frequently used queries. Here are some that we find useful:

  • Brian likes, Jeremy hasn’t decided: label:brianlikes AND -{label:jeremylikes label:jeremypass}
  • Jeremy likes, Brian hasn’t decided: label:jeremylikes AND -{label:brianlikes label:brianpass}
  • Needs “thanks but no thanks” note: label:brianpass AND label:jeremypass AND -{label:rejectionsent}
  • Let’s set up an interview: label:brianlikes label:jeremylikes -{label:interviewscheduled}
  • Needs attention: label:requiresfollowup OR -{label:brianlikes label:brianpass label:jeremylikes label:jeremypass}

I hope this trick comes in handy the next time you need to collaboratively categorize and search some data. One word of caution: as of this writing, there is a bug in Gmail involving labels containing dashes (I switched to camel case after many frustrating and fruitless attempts to fix my search queries). Good luck!