Which architecture should I select for a web based CRM app?

Question

I m planning a web based CRM app.

Target users will be small firms with few hundred clients. I am planning to use Rails framework and Rackspace Cloud Server/Amazon EC2 for hosting.

Since the data will be confidential I would prefer not to use a single database for all users. Does that mean that I will have to fire up a new instance of my rails app with separate database for each client with each app tied to a subdomain? Or is there a workaround?

Another question. If I have to create a new instance of rails app for every client, how many instances can a decent sized instance of EC2 [around 2GB RAM, 2GHz CPU] can support? [ 100 Read/Write operations per instance per day].

Answer 1

Firstly, as far as a framework goes, use whatever makes life easiest for you, for me Rails is the ticket.

I would not recomend creating seperate databases/rails instances per client. With good authorization, as I would expect an app like you describe to have, you can prevent all access to unorthorised items without too much trouble.

Also running many instances of Rails would "clog" up your system with mostly wasted resources.

A good sized EC2 should be able to support your app, however it really comes down to how expensive your code is to run (in cpu time), and how expensive your queries are. This is not something we can provide a really accurate estimate on.

Answer 2

This isn't specific to Rails, but you really should read up on multitenancy, which is the patten I'd recommend for your application. Instead of maintaining an instance of your code per client, you maintain a single instance that is aware that it supports multiple clients. I've not viewed it, but I was able to find a conference talk on writing multitenant apps in Ruby/Rails. You might find more information in the answers to this question: http://stackoverflow.com/questions/1382763/any-thoughts-on-multi-tenant-versus-multi-database-apps-in-rails

Answer 3

Most multi-tenant applications in the SAAS world seem to run on a single database using a unique identifier (client or account id) to keep data separate. It is pretty simple to code and it works. You can create tests and and auditing to your deployment process to ensure that data remains private.

However, I would also suggest having a look at Postgresql schemas. Using a schema lets you have a single database "split" into a number of unique schemas with controlled access.

More details here: http://stackoverflow.com/questions/2413439/should-i-use-multiple-databases.

As to the second part of your question regarding the number of instances - it is really going to depend on the app itself and how much RAM it is using. 100 read/writes operations a day is so small as to be irrelevant, so RAM is the operant factor. An "average" Rails app will use anything from 20-100mb per instance, so a 2GB server should be able to run quite a few. Of course, your deployment will become quite complicated as you will need to run within a virtual-hosting environment to get this setup effectively.

My vote would definitely be for a single app.