How to implement a subscription-based database like basecamp

Question

I developed a fully functional ruby-on-rails application that uses many mysql tables. I would like to turn this into a subscription-based service, but I have general, perhaps basic, conceptual questions:

• In settings such as Basecamp, does each user have access to their (as in unique) db tables or to tables shared by millions of users and identified by some variable?

• If so, how well does it scale? What would be the best db to use (mysql, oracle, etc.)?

• If each user is provided with their own unique db tables; How is this achieved? Is this with a rake challenge?

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

Thanks!

Answer 1

I believe this is achieved through a shared account. At the same time, resources in your current system will be covered by this account. those. your index actions are something like @projects = @ account.projects. Looking at basecamp, I would say that it scales very well! If you run into this problem then you have a good problem to solve, don't worry too much about it until then. I have to imagine that the database is a cluster, but I doubt very much that each user has his own set of tables, which will become a nightmare to manage!

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

There are probably new records, but I guess they will be a great start.

Good luck

Answer 2

• Tables are separated and identified as "parent" using the foreign key value. Separate tables for each user would be a nightmare. Most likely, a good database normalization fixes most of these problems. To-dos are associated with projects, projects are associated with an account, and then each account has many users.
• The best db to use will be entirely up to you. If you use rails and db transitions, you are limited only by what this interface can use. To get started, go to MySQL or PostgreSQL (my preference). They are free, and there is a lot of knowledge for a hobby.
• I personally would not create separate tables for each user
• Reading Wikipedia entries normalizing the database and developing the database will be a good start. After that, you should read as much as possible about good database design, perhaps even starting with common mistakes developers make when it comes to database design.