Should I use multiple databases?

Question

I am about to create an application with Ruby on Rails and I would like to use multiple databases, basically is an accounting app that will have multiple companies for each user. I would like to create a database for each company

I found this post http://programmerassist.com/article/302 But I would like to read more thoughts about this issue. I have to decide between MySQL and PosgreSQL, which database might fit better my problem.

Answer 1

There are several options for handling a multi-tenant app.

Firstly, you can add a scope to your tables (as suggested by Chad Birch - using a company_id). For most use-cases this is fine. If you are handling data that is secure/private (such as accounting information) you need to be very careful about your testing to ensure data remains private.

You can run your system using multiple databases. You can have a single app that uses a database for each client, or you can have actually have a seperate app for each client. Running a database for each client cuts a little against the grain in rails, but it is doable. Depending on the number of clients you have, and the load expectations, I would actually suggest having a look at running individual apps. With some work on your deployment setup (capistrano, chef, puppet, etc) you can make this a very streamlined process. Each client runs in a completely unique environment, and if a particular client has high loads you can spin them out to their own server.

If using PostgreSQL, you can do something similar using schemas. PostgresQL schemas provide a very handy way of islolating your data from different clients. A database contains one or more named schemas, which in turn contain tables. You need to add some smarts to your migrations and deployments, but it works really well.

Inside your Rails application, you attach filters to the request that switch the current user's schema on or off.

Something like:

before_filter :set_app

def set_app
  current_app = App.find_by_subdomain(...)
  schema = current_app.schema

  set_schema_path(schema)
end 


def set_schema_path(schema)
  connection = ActiveRecord::Base.connection
  connection.execute("SET search_path TO #{schema}, #{connection.schema_search_path}")
end

def  reset_schema_path
  connection = ActiveRecord::Base.connection
  connection.execute("SET search_path TO #{connection.schema_search_path}")
end