Rails - Aug 2006 - Schema Design: ActiveRecord and Group-Oriented Data

I will soon begin developing a group-oriented web application in Rails 
(think along the lines of BaseCamp, or Yahoo! Groups).  So there will be 
plenty of data that will logically separate along group lines; as a 
concrete simple example, let''s suppose one feature is a message board
to
be modelled by Messages.  When a user logs in to the app, she will be 
able to choose a group she belongs to and view that group-specific 
message board.

NB: The actual app will have much more than a message board to it; I''m 
asking a question about a more general problem.

Is there any general wisdom or consensus for doing this in the Rails 
framework, particularly using ActiveRecord and migrations?  Three 
obvious, direct approaches are:

[1]  Use one database, with one master Messages table, with group 
indices as foreign keys

[2]  Use one database, with many smaller Messages tables, prefixed by 
group name (e.g. Foo-Group_Messages), and dynamically manage the table 
connetions

[3]  Use one database per group, each with its own Messages table, and 
dynamically manage the Db connections

And add meta-level data where needed.

The easiest Rails-friendly approach seems to be [1], since this would 
require no ActiveRecord-level modification to get going - just add the 
business logic of groups to the Controllers.  However, I worry about the 
ultimate performance of this approach, since the master table will 
eventually grow very large and select queries using group indices could 
slow appreciably.

Similarly, [2] and [3] have their individual merits and disadvantages in 
terms of general schema design and ease of integration into the 
ActiveRecord constraints.  The real challenge is dynamically managing 
such a system - allowing dynamic creation and deletion of groups (hence 
dynamically creating and destroying Dbs or tables), and dynamically 
connecting the ActiveRecord models to the right data (hence dynamically 
managing Db or table connections).

BaseCamp has obviously solved this problem, but its source is not 
available for inspection.  This leaves me with the questions:

-Are there clear Rails- and migration- friendly solutions to this 
general problem?

-Are my performance concerns regarding [1] (the master, group-indexed 
table) valid?

-Are there Rails facilities for (somewhat) easily accomplishing [2] or 
[3], in an extensible, non-Db-system-specific way?

Thanks for making it through that painfully long post.  I appreciate any 
advice.

-Leo

-- 
Posted via http://www.ruby-forum.com/.