Database architecture for a multi-organization application


#1

Hi All,

I am a newbie to RoR and to be honest web development in general,
although I do have some experience of creating PHP and mySQL driven
applications in the past.

Anyway at this stage I am still following relatively basic RoR
tutorials but at the same time I am trying to envision how what I am
learning relates to the eventual app that I want to create.

An area that I am struggling to envision right now is my eventual
database architecture, and I was hoping that some of you could nudge
my learning in the right direction.

So my app will be similar to basecamp I think in the sense that it
will (hopefully!) be deployed to many organizations who will each have
their own users, products, calendars, blogs and various other objects
that are hosted in my database.

Now as I am reading these RoRs tutorials I can see how I would set
this up where I have one big database for each of these objects and
each one has some sort of organization key.

However even at this stage I am thinking that this will ultimately
result in very large db tables where I could end up with presumably
millions of rows and multiple users in different organizations all
trying to read/write from the same table at the same time.

In short I am worried that architecting my app in this fashion could
lead to a performance crash at some point (although this could be an
unfounded worry on my part of course).

The other approach I could foresee is that for each organization
entity I create individual db tables for their own users, products,
etc.
In other words this alternate approach would result in a very large
number of smaller tables as opposed to a very small number of large
tables.

One of the benefits I would see of this multi-table approach is that
the number of users requiring simultaneous access to each table will
be very small.

However my limited experience of RoR suggests to me that it is
designed to work with the former architecture where I have large table
behind each object type which has an ‘organization’ attribute.

Is this a valid conclusion and if so are my performance concerns
unfounded?

Alternatively can you give me any pointers as to where I should take
my RoRs learning to work out how to approach setting up a unique-
database-per-organization architecture?

Many thanks in advance for your insight!