Prepending table names on a per user or sub domain basis


#1

Here is a sample of the database structure I want to have:

someclientname-websites
someclientname-canned_responses
someclientname-canned_responses_websites
anotherclientname-websites
anotherclientname-canned_responses
anotherclientname-canned_responses_websites

As you can see I want to prepend the name of my future clients to the
table name so that they use completely different sets of data.

I know I can do the actual prepend using the Rails config files but how
do I set it on a per user basis (or on a per sub-domain basis)?


#2

Per,

Some coding for this is required, to dynamically come to the value of
the
variable (if possible because I’m a newbie), but there’s something
called
“set_table_name” that let’s you differ the table name from the default
naming
convention. It may look somewhat abusive but it should work. See:

http://wiki.rubyonrails.com/rails/pages/HowToUseLegacySchemas

And auto pluralize can be turned of (to get more grip on the naming)
with:

ActiveRecord::Base.pluralize_table_names = false

Hope it helps.

Regards,

Gerard.

On Saturday 21 January 2006 01:33, Per Djurner tried to type something
like:

table name so that they use completely different sets of data.

I know I can do the actual prepend using the Rails config files but how
do I set it on a per user basis (or on a per sub-domain basis)?


“Who cares if it doesn’t do anything? It was made with our new
Triple-Iso-Bifurcated-Krypton-Gate-MOS process …”

My $Grtz =~ Gerard;
~
:wq!


#3

Some coding for this is required, to dynamically come to the value of
the
variable (if possible because I’m a newbie), but there’s something
called
“set_table_name” that let’s you differ the table name from the default
naming
convention.

Thanks for responding.

The problem is (or at least one of the problems) is that the request
variables are not available in models. So this does not work:

set_table_name “#{@request.subdomains.first}_websites”

Any more ideas anyone?

if no one else has managed to do this or if it’s impossible I guess I
will have to go back to having the data in the same tables for all
acounts and just using account_ids as foreign keys in all other tables,
would like to avoid this if possible though.


#4

Per,

Any more ideas anyone?
Are the tables created during runtime (so if a user creates an account),
because then executing sql with the correct table names might be an
idea.

if no one else has managed to do this or if it’s impossible I guess I
will have to go back to having the data in the same tables for all
acounts and just using account_ids as foreign keys in all other tables,
would like to avoid this if possible though.
Why is that? From a DBA’s point of view (not that I am one … :wink: Aren’t
a few
tables easier to manage?

Gerard


“Who cares if it doesn’t do anything? It was made with our new
Triple-Iso-Bifurcated-Krypton-Gate-MOS process …”

My $Grtz =~ Gerard;
~
:wq!


#5

Why is that? From a DBA’s point of view (not that I am one … :wink: Aren’t
a few
tables easier to manage?

I knew that question was gonna come up sooner or later :wink:

2 reasons:

  1. I want to have that extra security added so that no matter how stupid
    I code the application no user of one account will ever see anyone elses
    data (in case I do a find instead of a find_by_account_id somewhere for
    example).

  2. the app itself will be much easier to code. For example given my db
    structure of (Account has many Websites which in turn has many Website
    Domains) I can retrieve all website domains for one account simply by
    doing WebsiteDomains.find_all instead of a more complex query with
    joins/where clauses etc that would otherwise be needed to get all the
    relations right from account down to website domain.

/ Per