Rails - Sep 2005 - Division of Accounts/Instances in Basecamp et al.

I have a design/logistics question that will have a huge impact on the
direction of development for my first real rails app.

The product is web application add-on to a specific installed package
I wrote years ago.  I have a handful of existing clients.  All of them
want some degree of improved functionality relative to web.  Being
able to send pertinent confirmation related emails from within the
system, etc.

Ok, enough for the background, here is the question:

Company ABC and Company XYZ both decide to purchase my new web add-on
application.  I would like to set them up as abc.mydomain.com and
xyz.mydomain.com, similar to how basecamp operates.  From that point
forward, from the users'' perspective, they are in there own isolated
application space.  What does basecamp do in this case?   Is there one
big honkin'' database for all clients/accounts?  If so, what is the
rough model?  My gut instinct tells me this could be hell from a
coding standpoint.  Every call to a model would need to be made with
conditions set to this client.

Do they instead create a database for each client?

Anyone have any thoughts or suggestions?

Is it simply a new instance of the singular rails app?  (this seems
most likely -- scripting the creation of a new rails app and
associated databases would be easy enough)

I get the sense though that basecamp does some form of the big ass
database for all clients.  I see project and comment ids in some of
the url''s that are just too large for a single instance.

If you go the big db route, what is the best way of minimizing the
impact of maintaining data segregation between clients?

Thought anyone?

Ken

I would be very surprised to learn that each Basecamp customer has their own
database.

I would think there is a single set of tables with (lots) of rows.

The values of ''id'' that seem to be in the URLs seems to
validate this as I
don''t have hundreds of thousands of messages yet the ids certainly go
that
high.

Cheers,
Hunter
> From: Ken Barker <ken.barker-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> Reply-To: <ken.barker-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>,
<rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org>
> Date: Fri, 9 Sep 2005 22:40:28 -0400
> To: <rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org>
> Subject: [Rails] Division of Accounts/Instances in Basecamp et al.
> 
> I have a design/logistics question that will have a huge impact on the
> direction of development for my first real rails app.
> 
> The product is web application add-on to a specific installed package
> I wrote years ago.  I have a handful of existing clients.  All of them
> want some degree of improved functionality relative to web.  Being
> able to send pertinent confirmation related emails from within the
> system, etc.
> 
> Ok, enough for the background, here is the question:
> 
> Company ABC and Company XYZ both decide to purchase my new web add-on
> application.  I would like to set them up as abc.mydomain.com and
> xyz.mydomain.com, similar to how basecamp operates.  From that point
> forward, from the users'' perspective, they are in there own
isolated
> application space.  What does basecamp do in this case?   Is there one
> big honkin'' database for all clients/accounts?  If so, what is the
> rough model?  My gut instinct tells me this could be hell from a
> coding standpoint.  Every call to a model would need to be made with
> conditions set to this client.
> 
> Do they instead create a database for each client?
> 
> Anyone have any thoughts or suggestions?
> 
> Is it simply a new instance of the singular rails app?  (this seems
> most likely -- scripting the creation of a new rails app and
> associated databases would be easy enough)
> 
> I get the sense though that basecamp does some form of the big ass
> database for all clients.  I see project and comment ids in some of
> the url''s that are just too large for a single instance.
> 
> If you go the big db route, what is the best way of minimizing the
> impact of maintaining data segregation between clients?
> 
> Thought anyone?
> 
> Ken
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails

Heya :)
> -----Original Message-----
> From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
[mailto:rails-
> bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Ken
Barker
> Sent: Friday, September 09, 2005 10:40 PM
> To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> Subject: [Rails] Division of Accounts/Instances in Basecamp et al.
> 
 
> Anyone have any thoughts or suggestions?
We spawn new databases for most of the apps we build that do this sort of
thing.

* It is easy to do in code

* It is no problem to maintain (schema changes by script after testing)

* It makes for a ROCK solid security model between clients (no intermixed
data)

* It allows us to selectively move clients to a new version of the schema

* It allows us to load balance across multiple DB servers easily

* It eases locking clashes

* It allows us to tune the DB to each client''s usage if needed

* IT allows for simplified backup and restoration of a single clients data

Lots and lots of goodness.

Soulhuntre
----------
 
http://www.girl2.com  - my girls
http://www.the-estate.com  - my legacy
http://wiki.thegreybook.com - my project
http://weblog.soulhuntre.com - my thoughts

On Sep 10, 2005, at 4:40, Ken Barker wrote:
> I get the sense though that basecamp does some form of the big ass
> database for all clients.  I see project and comment ids in some of
> the url''s that are just too large for a single instance.
I''m pretty sure your assessment is correct, and it''s fairly
simple to
do in Rails. You just make sure the account-owned parts all have an  
account_id or whatever, then check for that account_id whenever you  
retrieve data. And set it when you save data.

For a short example check the last part of http://tinyurl.com/a8sg8.

--
Jakob L. Skjerning - http://mentalized.net

On 9/10/05, Soulhuntre
<soulhuntre-xtZVmrYH4z1ZroRs9YW3xA@public.gmane.org>
wrote:
> 
> Heya :)
> 
> > -----Original Message-----
> > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
[mailto:rails-
> > bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf
Of Ken Barker
> > Sent: Friday, September 09, 2005 10:40 PM
> > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > Subject: [Rails] Division of Accounts/Instances in Basecamp et al.
> >
> 
> > Anyone have any thoughts or suggestions?
> 
> We spawn new databases for most of the apps we build that do this sort of
> thing.
> 
> * It is easy to do in code
> 
> * It is no problem to maintain (schema changes by script after testing)
> 
> * It makes for a ROCK solid security model between clients (no intermixed
> data)
> 
> * It allows us to selectively move clients to a new version of the schema
> 
> * It allows us to load balance across multiple DB servers easily
> 
> * It eases locking clashes
> 
> * It allows us to tune the DB to each client''s usage if needed
> 
> * IT allows for simplified backup and restoration of a single clients data
> 
> Lots and lots of goodness.

It also makes certain things harder to manage, in addition to not being 
considered the ''proper way'' by many. That said we also do it
for our
databases that are subject to CISP compliance (credit card data), although 
we use postgresql schema''s in conjunction with ''set
search_path'' instead of
separate databases.

The biggest challenge though that you usually run into with this setup is 
that you need one database connection per client. Depending on your 
application that might not be workable, especially if you are using 
connection pooling. The other challenge is when you make a schema change and 
need to push it out to several thousand clients. If the schema change goes 
hand in hand with a code change in the application, you could be looking at 
more downtime then you want while updating all the client schema''s.
There
are always ways to work around it, but it can be a pain.

Chris


_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails