Dynamically changing table names

As part of a learning exercise, although I would like this to become a
real application in the long run, I am trying to set up an accounting
application, for this application I need the users to be able to create
and use different Companies (Sets of books).

For maintenance reasons I would rather keep the data for each company
seperate in some way i.e. seperate tables with prefixes or a seperate
database. Having read up a bit on using multiple database connections I
think I would rather stick with one database and prefix the table
names.

There are some 'application wide' tables for which I don't want the
prefix one of which is the table that holds the different companies
that have been added. I already have code which creates the set of
prefixed tables from a set of template tables by using execute(sql) I
hope to improve this later by using schema/migration but for now
executing the raw sql statements is working.

My best guess after trying a few things is to use a before_filter in
the application.rb and my simple tests with one table prove that this
could work. (I am using a session variable to define which 'company' is
the one currently used). I suppose I could set up a hash/array to
define which models the table names need to be set for but I would
prefer this to a bit more dynamic i.e. not relying on me to remember to
put the model/table in my array/hash in application.rb.

I would rather have this prefixing in the model but I can't figure out
how to get at the session variable for set_table_name in the model(s).

Any suggestions are welcome, I know I could just use tables with a
company column and filter all my finds appropiately but this would lead
to huge, more difficult to maintain, transaction tables in the long run.

JermWorm wrote:

As part of a learning exercise, although I would like this to become a
real application in the long run, I am trying to set up an accounting
application, for this application I need the users to be able to create
and use different Companies (Sets of books).

For maintenance reasons I would rather keep the data for each company
seperate in some way i.e. seperate tables with prefixes or a seperate
database. Having read up a bit on using multiple database connections I
think I would rather stick with one database and prefix the table
names.

There are some 'application wide' tables for which I don't want the
prefix one of which is the table that holds the different companies
that have been added. I already have code which creates the set of
prefixed tables from a set of template tables by using execute(sql) I
hope to improve this later by using schema/migration but for now
executing the raw sql statements is working.

My best guess after trying a few things is to use a before_filter in
the application.rb and my simple tests with one table prove that this
could work. (I am using a session variable to define which 'company' is
the one currently used). I suppose I could set up a hash/array to
define which models the table names need to be set for but I would
prefer this to a bit more dynamic i.e. not relying on me to remember to
put the model/table in my array/hash in application.rb.

I would rather have this prefixing in the model but I can't figure out
how to get at the session variable for set_table_name in the model(s).

Any suggestions are welcome, I know I could just use tables with a
company column and filter all my finds appropiately but this would lead
to huge, more difficult to maintain, transaction tables in the long run.

I think you should reconsider the "company column" alternative. A well
indexed database shouldn't be that hard to query/mantain.

Seeing as how the only answer so far isn't the one I wanted, I had
another look at using multiple databases and came up with a solution to
use a different database for each company.

Similar to the 'External' example in Rails Recipes It involves setting
up a 'connection' class for the other db connections and subclassing my
my models from there. When I choose the company to work with the
connection is (re)established.

#app/models/comp_conn.rb
class Comp_conn < ActiveRecord::Base
  # attr_accessible :dbname
  @@db=''
  self.abstract_class = true

  def Comp_conn.open(dbname)
    @@db=dbname
    establish_connection(:adapter => "mysql",
      :host => "localhost",
      :username => "ruby",
      :password => "itsahugesecretwooo",
      :database => @@db
    )
  end
end

#app/models/test.rb
class Test < Comp_conn
end

A snippet from a controller could look like this:
  def open_company
    id = params[:id]
    if @company = Accset.find(id)
      @session[:curraccset] = @company.code
      @session[:company_id] = @company.id
      Comp_conn::open(@session[:curraccset])
      redirect_to(:controller => 'company', :action => 'index')
    else
      flash[:notice]="Invalid Company"
      redirect_to(:action => 'choose_company')
    end
  end

I was watching mysql processes whilst testing this and it appears to be
closing and opening connections correctly as I choose different
datasets.

I don't know if those who aren't complete newbies like me can spot any
problems in using this method, one that immediately comes to mind but
doesn't seem to affect my simple testing is caching of data. It might
be worth mentioning that my 'main' database contains 'templates' of the
tables that would go into a new company once created.

Yes I'll have to write something to keep the table structures of my
companies in line with the development set but since I've done this
sort of thing in my foxpro application on which I'm basing my self
learning I don't foresee any huge difficulty. Something like check the
version of the tables when opening and offer to upgrade the tables
and/or refusing to open an unmatched version. Or an admin tool that can
be run when required.