Rails - Sep 2010 - Different databases for different sub domains

I asked on another thread about adding an "organisation_id" across the
whole database to effectively give a separate database for different
organisations who use the app.

The other way of doing it would be to have one app but different
databases depending on the subdomain (or some logon credential).

I''m not really sure how to do this.. probably something like:
ActiveRecord::Base.establish_connection(
           :adapter  => mysql,
           :host     => localhost,
           :username => xyz,
           :password => password,
           :database => "Different for each subdomain"
         )

in the application.rb but I''m a bit unsure of the details.

Not sure if there would be a lot of extra grief running migrations etc
with multiple databases...

Any ideas of pros and cons and recommended solutions welcome.

The app is mysql running on apache /passenger.

Cheers
George

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

giorgio wrote:
> I asked on another thread about adding an "organisation_id"
across the
> whole database to effectively give a separate database for different
> organisations who use the app.
> 
> The other way of doing it would be to have one app but different
> databases depending on the subdomain (or some logon credential).
Don''t do that.  I saw the other thread, and I think you were given bad 
advice.  You almost always want one big DB for the whole thing.  It''s 
usually easier to deal with.

Best,
-- 
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org

Sent from my iPhone
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Sep 19, 1:46 am, Marnen Laibow-Koser
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
wrote:
> Don''t do that.  I saw the other thread, and I think you were given
bad
> advice.  You almost always want one big DB for the whole thing.
 It''s
> usually easier to deal with.
Any reason why it is wrong to do that ? Why is it easier in your
opinion? My idea to split into multplie databases was just and idea.
Not an advice :-)

Robert Pankowecki

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Robert Pankowecki wrote:
> On Sep 19, 1:46�am, Marnen Laibow-Koser
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:
>> Don''t do that. �I saw the other thread, and I think you were
given bad
>> advice. �You almost always want one big DB for the whole thing.
�It''s
>> usually easier to deal with.
> 
> Any reason why it is wrong to do that ? Why is it easier in your
> opinion? 
A bunch of reasons.  Deployment is easier since you don''t have to 
maintain several DBs; Rails doesn''t have to choose a DB; it''s
easier to
analyze everyone''s data together (which may often be necessary) if
it''s
all in one DB; it may be more efficient.
> My idea to split into multplie databases was just and idea.
> Not an advice :-)
> 
> Robert Pankowecki
Best,
-- 
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org

Sent from my iPhone
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

For the developer is better to have one database but not for the clients,
some may want their data exported in the future, or you may need to create
extra id number in case some of then sequences for things like invoices, if
you have one db you can use the id field for invoices. But i havent give
much thought to the whole problem, so is just .... an opinion......  * runs
out and hides behind corner*

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

I can see some other advantages.

You can easily split your application into multiple servers. That
gives you the ability to upgrade clients to new application step by
step instead of migrating whole bunch of data at the same time. Some
clients want to upgrade today some in next week.

The code to write is easier because you do not have to think whole
time about the organization condition to add every to every sql query.
If some organization wants to quit then you just remove the db and
that''s all.

However I can surely agree that managing migrations, replication and
backups can become a nightmare...

So... as usually. There is no silver bullet. There are pros and cons
and everyone has to decide for himself.

Robert Pankowecki

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On 19 Sep 2010, at 19:26, radhames brito wrote:
> For the developer is better to have one database but not for the  
> clients, some may want their data exported in the future, or you may  
> need to create extra id number in case some of then sequences for  
> things like invoices, if you have one db you can use the id field  
> for invoices. But i havent give much thought to the whole problem,  
> so is just .... an opinion......  * runs out and hides behind corner*

Since our main application is CRM and ERP, I''d like to chime in here.

First of all, it''s generally a very bad idea to use IDs as document  
numbers, especially with invoices. You might have a few different  
scenarios that pop up: customer wants to delete a few invoices (faulty  
invoices generated) and turn back the document number, your customer  
might want to restart numbering every year, with a document number  
like 2010/001, 2010/002, 2011/001, etc. You would create these numbers  
in your model and would well be able to scope them properly, however...

That said, we do have a separate database for every customer, for  
several reasons, I''ll mention a few.

Although we could scope everything on the customer, but given the  
total amount of data involved across all accounts, it would have a  
dramatic impact on performance over time, even with proper indexes.

Also, since we are storing the data of direct competitors (our  
customers are in the same market segment in our already small country)  
we found that during presales the question about keeping data separate  
always arose. Again, a good test suite could and should avoid one  
customer seeing another one''s data, just like Blinksale does it, but  
prospects might not see it that way so easily.

There were a few more factors that made us decide to keep data  
separate, it''s up to you to decide the best choice depending on your  
application.

It''s a trivial task to make sure migrations run across all databases  
on deploy btw, you just have to tinker with the default Capistrano  
script a bit and you''re done.


Best regards

Peter De Berdt

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

radhames brito wrote:
> For the developer is better to have one database but not for the 
> clients,
> some may want their data exported in the future,
That''s easy enough to do with one big database.
> or you may need to 
> create
> extra id number in case some of then sequences for things like invoices, 
> if
> you have one db you can use the id field for invoices. But i havent give
> much thought to the whole problem, 
Then why post?

so is just .... an opinion......  *
> runs
> out and hides behind corner*
Best,
-- 
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org

Sent from my iPhone
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Robert Pankowecki wrote:
> I can see some other advantages.
> 
> You can easily split your application into multiple servers. 
Any decent DB server can do that with one DB.

> That
> gives you the ability to upgrade clients to new application step by
> step instead of migrating whole bunch of data at the same time. Some
> clients want to upgrade today some in next week.
That''s sort of a separate issue, at least in some cases.
> 
> The code to write is easier because you do not have to think whole
> time about the organization condition to add every to every sql query.
That''s what scopes are for.
> If some organization wants to quit then you just remove the db and
> that''s all.
It''s no harder to delete all records for an organization in one big
DB.
> 
> However I can surely agree that managing migrations, replication and
> backups can become a nightmare...
Yeah.
> 
> So... as usually. There is no silver bullet. There are pros and cons
> and everyone has to decide for himself.
> 
> Robert Pankowecki
Best,
-- 
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org

Sent from my iPhone
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Peter De Berdt wrote:
[...]
> Since our main application is CRM and ERP, I''d like to chime in
here.
> 
[...]
> 
> That said, we do have a separate database for every customer, for
> several reasons, I''ll mention a few.
> 
> Although we could scope everything on the customer, but given the
> total amount of data involved across all accounts, it would have a
> dramatic impact on performance over time, even with proper indexes.
What makes you think so?  A good DB should be able to handle this with 
minimal performance impact.
> 
> Also, since we are storing the data of direct competitors (our
> customers are in the same market segment in our already small country)
> we found that during presales the question about keeping data separate
> always arose. 
And you just need to tell your customers that you do so.  How you do it 
is none of their business.

> Again, a good test suite could and should avoid one
> customer seeing another one''s data, just like Blinksale does it,
but
> prospects might not see it that way so easily.
Are you saying you don''t have a good test suite, then?
> 
> There were a few more factors that made us decide to keep data
> separate, it''s up to you to decide the best choice depending on
your
> application.
> 
> It''s a trivial task to make sure migrations run across all
databases
> on deploy btw, you just have to tinker with the default Capistrano
> script a bit and you''re done.
Good to know.
> 
> 
> Best regards
> 
> Peter De Berdt
Best,
-- 
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org

Sent from my iPhone
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On 19 Sep 2010, at 21:03, Marnen Laibow-Koser wrote:
>> Although we could scope everything on the customer, but given the
>> total amount of data involved across all accounts, it would have a
>> dramatic impact on performance over time, even with proper indexes.
>
> What makes you think so?  A good DB should be able to handle this with
> minimal performance impact.
It''s called performance and stress tests.
>> Also, since we are storing the data of direct competitors (our
>> customers are in the same market segment in our already small  
>> country)
>> we found that during presales the question about keeping data  
>> separate
>> always arose.
>
> And you just need to tell your customers that you do so.  How you do  
> it
> is none of their business.
Once you start working with the budgets and contacts they work with,  
it becomes their business, or it''s no business for us. I''m
guessing
you don''t work with Microsoft SQL Server for fun either.
>> Again, a good test suite could and should avoid one
>> customer seeing another one''s data, just like Blinksale does
it, but
>> prospects might not see it that way so easily.
>
> Are you saying you don''t have a good test suite, then?
As insulting as I find your question, I think we''re pretty well  
covered, thank you.


Best regards

Peter De Berdt

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

> Then why post?
>
> uff good thing i ran
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Sep 19, 8:59 pm, Marnen Laibow-Koser
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
wrote:
> Robert Pankowecki wrote:
> > I can see some other advantages.
> > You can easily split your application into multiple servers.
> Any decent DB server can do that with one DB.
I know. I meant the situation when there are so many data that you
want to split them into multiple databases. When you already have
multiple database then you just move them to a different server.
Otherwise a little more job is required. Not related to topic.
> > The code to write is easier because you do not have to think whole
> > time about the organization condition to add every to every sql query.
>
> That''s what scopes are for.
You mean default_scope ?

How do you make a default_scope that applies to dynamic situation like
ex. query only things from the organization that currently logged user
belongs to ?

Robert Pankowecki

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Peter De Berdt wrote:
> On 19 Sep 2010, at 21:03, Marnen Laibow-Koser wrote:
> 
>>> Although we could scope everything on the customer, but given the
>>> total amount of data involved across all accounts, it would have a
>>> dramatic impact on performance over time, even with proper indexes.
>>
>> What makes you think so?  A good DB should be able to handle this with
>> minimal performance impact.
> 
> It''s called performance and stress tests.
Would you mind sharing some of what you found?  I''d be very curious.
> 
>>> Also, since we are storing the data of direct competitors (our
>>> customers are in the same market segment in our already small  
>>> country)
>>> we found that during presales the question about keeping data  
>>> separate
>>> always arose.
>>
>> And you just need to tell your customers that you do so.  How you do  
>> it
>> is none of their business.
> 
> Once you start working with the budgets and contacts they work with,
> it becomes their business, or it''s no business for us.
The SaaS client does not get to dictate implementation.  Period.  The 
custom software client *might* -- at a higher price tag.
> I''m guessing
> you don''t work with Microsoft SQL Server for fun either.
Different situation.  I came inboard a development team where MS SQL 
server was already in use.  I love the job otherwise, so I''m willing to
put up with one point of difficulty.
> 
>>> Again, a good test suite could and should avoid one
>>> customer seeing another one''s data, just like Blinksale
does it, but
>>> prospects might not see it that way so easily.
>>
>> Are you saying you don''t have a good test suite, then?
> 
> As insulting as I find your question, I think we''re pretty well
> covered, thank you.
Why do you find that insulting?  You seemed to be implying that you had 
to take certain measures because you didn''t have a good test suite.  I 
simply asked if that''s what you meant.  Apparently it wasn''t.
> 
> 
> Best regards
> 
> Peter De Berdt
Best,
-- 
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org

Sent from my iPhone
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.