Writing RushHQ 2.0 (part I)July 27, 2009
First I moved a copy of the project code into a new git repository. This was easy (thank you git!). I just made a local copy of the project’s working tree, initialized a new git repository and added and committed everything. On the server I initialized an empty bare git repository and just pushed the local repository to the remote one. Done.
Switching Database Engines
When we wrote RushHQ originally we used the RoR default SQLite database engine. SQLite has the advantage of being very easy to work with because it’s file-based. There’s no need to setup and administer a database server so you can get up and running right away without any hassle. Thus it’s great for development work. I don’t think it’s that great in a production environment. Dealing with named files to store our databases made me nervous. What if I acidentally renamed one or moved one or deleted one while I was trying to back them up? If someone managed to gain access to our server he could just take the data. And there isn’t any way that I know of to set user-level permissions for SQL statements on SQLite databases, although admittedly I didn’t look too hard. So there isn’t the extra layer of security that a database server gives you.
So I decided to move our datastore over to MySQL. This required installing the mysql-client and mysql-server plus some development headers on our production system, installing the ruby-mysql bindings and a quick relearning of the mysqladmin commands to set up and secure the MySQL server.
Setting up a Staging Deployment
To test out all the nifty new features of RushHQ before pushing them to the main rushhq.com deployment I wanted to set up a staging deployment. I set up a subdomain and configured Apache to use Passenger rather than Mongrel, so all I had to do was point the document root in Apache to the /public directory of my deployed code. Ok I also had to fool around with permissions on that directory until I stopped seeing “403 Forbidden” from Apache every time.
All of the above tasks should have taken me less than an hour but of course extra issues crept in, like the fact that our ssl certificate is only good for rushhq.com and www.rushhq.com domains. So I had to disable it for the staging deployment.
The Real Work Begins
We made what I consider now to be a poor database design choice early-on while developing rushhq. We decided that each user would belong to one (and only one) fraternity. Users did not exist as individuals outside of their fraternity. This probably seems fine logically since you can’t really belong to more than one fraternity at once. But it led us to store each fraternity’s data in a separate database. That includes all the user data for each fraternity’s members, which would normally include authentication credentials like a username and password.
If you’re not shocked yet I’ll explain a little further. With user data spread over tons of databases, how do you look up a username and password when someone comes to rushhq.com and wants to log in? There’s no way to know which database to search for that information ahead of time. So you could just step through each database looking through all the user data until you find a match, but that’s gross and pretty inefficient. So we decided on something potentially more gross but more efficient: we set up another database to house all information that we might need to look up globally. And since we couldn’t (or at least didn’t want to try too hard to) do an SQL JOIN accross databases in order to return the rest of a user’s data after a successful authentication, we ended up storing some redundant information in the global database.
Now you should be sufficiently shocked. Yes I know that eliminating redundancy is crucial to data normalization to make maintaining data integrity easy. And to be honest I don’t remember all the justifications we had, but one was ease of data restoration. This design combined with SQLite meant we only had to drop in a backup of a fraternity’s database file to fully resotre its data if something went wrong. I also remember that we wanted to eliminate potential scope issues, like accidentally showing a fraternity recruits from a different chapter or allowing a fraternity to delete users from a competing chapter.
The price paid for those conveniences was large in retrospect. We had to write a lot of extra code to switch database connections on the fly in order to create and update information in more than one location. We sometimes had to perform two identical sets of database operations (one on the global database, one on a fraternity’s individual database) which is really bad. In general trying to maintain code like that was a nightmare. Things would break a lot of the time. So I would make a very different decision now to stick with the more traditional single database model.
And that’s what I’m doing; rewriting RushHQ on top of a single database model. It’s such a fundamental change that I’m going to be rewriting basically all of the RushHQ code. But not from scratch at least. A lot can be tweaked and reused rather than discarded.
My next post will get into a little more detail about specific data modeling choices now that RushHQ is free from the tyranny of multiple databases.
Follow me on Twitter: