Create databases on demand

Sorry if this has been asked before:

I am developing a month-to-month service similar to http://www.basecamphq.com/
and planning to operate it on a hosting site. I plan to simply deploy
a copy of my application for each customer with their own database
instance.

It doesn't appear as if my provider allows dynamic creation of
databases, which leads to my question: Does it even make sense to
create a production database for each customer or should I modify my
models so that customers share the tables.

This would be a lot of work (24 models) - it would certainly be much
DRYer though. I can imagine it would be a nightmare updating table
elements on 100s of databases...

What do you think? Should I look for a provider that will give me root
access to MySQL or bite the bullet and do the work now?

Thanks in advance.
-Dave

I am developing a month-to-month service similar to
http://www.basecamphq.com/ and planning to operate it on a
hosting site. I plan to simply deploy a copy of my
application for each customer with their own database instance.

It doesn't appear as if my provider allows dynamic creation
of databases, which leads to my question: Does it even make
sense to create a production database for each customer or
should I modify my models so that customers share the tables.

This would be a lot of work (24 models) - it would certainly
be much DRYer though. I can imagine it would be a nightmare
updating table elements on 100s of databases...

What do you think? Should I look for a provider that will
give me root access to MySQL or bite the bullet and do the work now?

I always try to include a root object in my schemas to which everything
belongs, either directly or, more often, indirectly via its ancestors.
That tends to make it easy to scale horizontally by adding new root
objects. I would suggest that's probably easier than maintaining N
instances of your database. The only real downside is that when you
upgrade your schema, all of your customers have to upgrade at once, but
that's probably a feature rather than a bug for a hosted service like
you describe.

- donald

Thanks for your reply, Donald. So then it might make sense to
introduce an Account object to own all my other objects.
I'm just thinking about the task list to implement:

- migration to handle creating Account and adding the foreign keys to
all the other tables.
- Account Model with has_many relationships
- add belongs_to account relationships to existing models
- introduce find_by_account clause to each controller that creates an
instance of the system models.
- other items...

So how would this work in practice with creating accounts and users?
For example I'm creating a CRM system where a user can manage customer
contacts and I want to log Fred in so he can manage his customers:

accounts has_many users, customers
user belongs_to account
customer belongs_to account

users has_many customers
customer belongs_to user

I'll have to give this some more thought. How have you tackled this?

Regards,
Dave

Hi Dave,

I'm creating something similar at the moment - not a CRM, I hasten to
add.

In my application, an account has many users and has many contracts.
Any user can amend a contract belonging to that user's account. On
that basis, it is very similar to the schema you have outlined above.
By having a customer belonging to a user, it should be easy enough to
restrict editing the customer's details to that one user.

I'm using the Account Location plugin on my app so that I can use urls
such as accountname.myapp.com pretty much in the same vain as
Basecamp.

Best Regards

Robin

Excellent tip, Robin!
I hadn't heard of the Account Location plugin. It sounds like just the
thing I need. Thanks and good luck with your application!

This book was recommended to me today and it looks very good, so I'll
pass the link on to you. Thanks again!

http://gettingreal.37signals.com/

Dave