Multiple databases questions (newbie)

Hi all,

I am new to programming and am trying to create my first ROR
application from books and online posts. I have come to a challenge
that i don't know how to solve. Any help is really appreciated..

I need to have a login method that creates a new database for each
user that signs up. This database is used for storing user data.
Single master database will be used for login / authentication data.

Three methods that i can think of.

1. Using def signup to call an external script which creates the
database. (using CentOS + MySQL) (http://www.ruby-forum.com/topic/
106628)

2. Using a method that creates a database from within the rails code
(like the one by by Tomas Klapka http://pastie.org/113886)

3. Creating a set of unique database names and associating a database
to each new user. As soon as a predefined pool of databases is taken
up, running a script or a stored procedure to create some more..

Can anyone suggest a best practice for this kind of problem? The
intention of the application is to end up with lots and lots of small
databases ( sub 100 initially and growing to sub 1000 or more).

Thanks.
andy.

You know that creating a per-user database is a strange idea, don't
you? I've never heard of that. I think you need to explain this a bit
more if you want help. Why can't you just use a standard plugin like
acts_as_authenticated or something like that?

Hi Mark,

Thanks for responding.

sure, i understand its strange and here is my thinking:

In my application, my customer would be a sysadmin for an
organization. The sysadmin will be the only person logging in from a
company, but he or she will be tracking multiple objects in the
database and multiple properties for those objects. Therefore, my
thinking is that if each client has a dedicated database so then if a
database corruption occurs - it does so on only one customer. If i
write a SQL statement then it cannot by mistake include object from a
different customer (as there is that inherent isolation of a different
database name and connection)

Also - long term, I want to say to mr customer.. what extra fields do
you need in our system....

That way each and every client is able to add fields without affecting
anybody else.

I am also under the impression (and i have to rtfm a bit more) that if
I can separate client databases, then i can apply different security
levels to those. Eg if my client is in finance - they get db cluster
A which is configured for extra layers of security, and if one of my
clients becomes a small non-for profit charity who doesn't need the
extras i can put them on db cluster B..

What do you think?

Is there a better way to solve that?

andrewg77 wrote:

Also - long term, I want to say to mr customer.. what extra fields do
you need in our system....

That way each and every client is able to add fields without affecting
anybody else.

As soon as you do this, you are going to be maintaining multiple copies
of the application. Doesn't sound very fun to me.

I'm working on a project right now that sounds similar to yours. It also
holds the potential for custom data per "customer". To address part of
it, I came up with the idea of custom "data collections" that the
customer can use to store odds and ends. It's not perfect, but it should
address the bulk of my needs.

Peace,
Phillip