Different databases for different sub domains

Rails
1

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

2

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
[email protected]

Sent from my iPhone

3

On Sep 19, 1:46 am, Marnen Laibow-Koser [email protected] 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 :slight_smile:

Robert Pankowecki

4

Robert Pankowecki wrote:

On Sep 19, 1:46�am, Marnen Laibow-Koser [email protected] 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 :slight_smile:

Robert Pankowecki

Best,
–Â
Marnen Laibow-Koser
http://www.marnen.org
[email protected]

Sent from my iPhone

5

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

6

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*

7

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
[email protected]

Sent from my iPhone

8

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

9

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
[email protected]

Sent from my iPhone

10

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
[email protected]

Sent from my iPhone

11

Then why post?

12

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

13

On Sep 19, 8:59 pm, Marnen Laibow-Koser [email protected] 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

14

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
[email protected]

Sent from my iPhone