How to implement a subscription based DB, such as basecamp

Question

I have developed a fully functional ruby-on-rails application which utilizes numerous mysql tables. I would like to turn this into a subscription based service but I have some general, probably basic, conceptual questions:

1. In a setup such as Basecamp does each user have access to her/his own (as in unique) db tables or are the tables shared with millions of users and Identified by some variable?

2. If this is the case how well does it scale? What would be the best db to use (mysql, oracle etc)?

3. If each user is given his/her own unique db tables; how is this accomplished? Is it through a rake task?

4. Are there any resources you would suggest (books, media, etc) that explain how to accomplish either of these methods?

Thanks!

Answer 1

1. The tables are shared and identified to a "parent" using a foreign key value. Having separate tables per-user would be a nightmare. It's more likely that good database normalisation fixes most of these issues. To-dos are related to projects, projects are related to an account and then each account has many users.
2. The best db to use would be entirely up to you. If you're using rails and db migrations, you're only limited to what that interface can utilise. To begin with, go with either MySQL or PostgreSQL (my preference). They're free and there's a wealth of knowledge available for hobby projects.
3. I personally would not create separate tables per user
4. Reading the wikipedia entries on database normalisation and database design would be a good start. Following that you should read up as much as you can on good database design, perhaps even starting with the common mistakes developers make when it comes to database design.

Answer 2

I believe it is achieved using an overall account. Whereby the resources in your current system will be scoped by that account. i.e in your index actions something like @projects = @account.projects. Looking at basecamp I would say it scales very well! If you hit this problem then you have a good problem to solve, don't worry about it too much until then. I should image the database is a cluster but very much doubt each user has their own set of tables, that would become a nightmare to manage!

A quick google and I've found a this: http://www.robbyonrails.com/articles/2009/01/11/subdomain-accounts-with-ruby-on-rails-explained which also links to a post by DHH which looks like it explains how they did it.

There are probably newer write ups but I'm guessing they would be a great place start.

Good luck!