# Dynamically changing table names

**URL:** <https://www.ruby-forum.com/t/dynamically-changing-table-names/70324>\
**Category:** Rails\
**Created:** [September 28, 2006, 9:25am UTC](https://www.ruby-forum.com/t/dynamically-changing-table-names/70324 "2006-09-28T09:25:16Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![JermWorm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/a9adbd/32.png) [@JermWorm](https://www.ruby-forum.com/u/JermWorm)\
**Post date:** [September 28, 2006, 9:25am UTC](https://www.ruby-forum.com/t/dynamically-changing-table-names/70324/1 "2006-09-28T09:25:16Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![JermWorm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/a9adbd/32.png) [@JermWorm](https://www.ruby-forum.com/u/JermWorm)\
**Post date:** [September 28, 2006, 10:29am UTC](https://www.ruby-forum.com/t/dynamically-changing-table-names/70324/2 "2006-09-28T10:29:21Z")

</div>

JermWorm wrote:

> 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.

---

<div class="post-metadata">

**Author:** ![JermWorm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/a9adbd/32.png) [@JermWorm](https://www.ruby-forum.com/u/JermWorm)\
**Post date:** [September 28, 2006, 10:29pm UTC](https://www.ruby-forum.com/t/dynamically-changing-table-names/70324/3 "2006-09-28T22:29:46Z")

</div>

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.