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