Dynamic table names for AR models

Rails
1

Hi All,

I have an interesting Active Record problem and I’m not quite sure what
the cleanest solution is. The legacy database that I am integrating with
has a strange wrinkle in its schema where one logical table has been
‘partitioned’ into several physical tables. Each table has the same
structure, but contains data about different items.

I’m not great at explaining this clearly (as you can tell!). Let me try
and explain with a concrete example. Let’s say we have a Car, which has
one or more Wheels. Normally we’d represent that with a Car table and a
Wheels table like so:

CREATE TABLE cars (
id int(11) NOT NULL auto_increment,
name varchar(255),
'etc
)

CREATE TABLE wheels (
id int(11) NOT NULL auto_increment,
car_id int(11) NOT NULL,
color varchar(255),
'etc
)

So far, so good. But with the ‘partioning’ strategy that is in my legacy
database it would look more like:

CREATE TABLE cars (
id int(11) NOT NULL auto_increment,
name varchar(255),
'etc
)

CREATE TABLE car_to_wheel_table_map (
car_id int(11) NOT NULL,
wheel_table varchar(255)
)

CREATE TABLE wheels_for_fords (
id int(11) NOT NULL auto_increment,
car_id int(11) NOT NULL,
color varchar(255),
'etc
)

CREATE TABLE wheels_for_buicks (
id int(11) NOT NULL auto_increment,
car_id int(11) NOT NULL,
color varchar(255),
'etc
)

CREATE TABLE wheels_for_toyotas (
id int(11) NOT NULL auto_increment,
car_id int(11) NOT NULL,
color varchar(255),
'etc
)

So here we have a set of wheels_for_x tables, and a
car_to_wheel_table_map table which contains a mapping from car_id to the
specific wheels_for_x which contains the wheels for a specific car. If I
want to find the set of wheels for a car I first have to find out which
wheels table to use via the car_to_wheel_table_map table, and then look
up records in the wheel table specified in the car_to_wheel_table_map.

Firstly, can someone enlighten me as to if there is a standard name for
this technique?

Secondly, does anyone have any pointers on how I can make this work in
Active Record in a nice clean way. The way I see it I can either have a
Wheel model where the table name can be defined per instance, or I can
dynamically create Model classes at runtime with the correct table name
as specified in the mapping table.

Any advice gratefully received!

Cheers,
Pete

2

Secondly, does anyone have any pointers on how I can make this work in
Active Record in a nice clean way.

In a domain i’m workin in, this type of thing also came up, and I chose
to split it in a somewhat similar way using abstract classes. Create an
abstract class, Wheels, put all common code in there. I chose to
include the equivalent of that mapping table as a constant, so in your
example a “CarToWheelsClassHash” Constant, and then create sub-classes
for each of the wheels table. Override the ActiveRecord Methods for the
Wheel class, and then re-override them in the subclasses to bring them
back.

Wheel < ActiveRecord::Base
self.abstract_class = true

CarToWheelsClassHash = {:ford => “FordWheel”}

def self.abstract_find(car_brand, *normalfindargs)
CarToWheelsClassHash[car_brand].constantize.find(*normalfindargs)
end

[other code common to all wheels models]
end

FordWheel < Wheel
code specific to the ford wheels model
end

hope that makes sense to you, i actually really like this solution so
far on my end.

3

Gabriel S. wrote:

Wheel < ActiveRecord::Base
self.abstract_class = true

CarToWheelsClassHash = {:ford => “FordWheel”}

def self.abstract_find(car_brand, *normalfindargs)
CarToWheelsClassHash[car_brand].constantize.find(*normalfindargs)
end

[other code common to all wheels models]
end

FordWheel < Wheel
code specific to the ford wheels model
end

hope that makes sense to you, i actually really like this solution so
far on my end.

Thanks Gabriel, but unfortunately this exact solution wouldn’t work in
our case. The names of the various Wheel tables are not known at design
time. In other words, there’s nothing to stop someone creating a new
table and adding it to the mapping table during runtime.

Given the wonderful dynamic nature of ruby I suppose I could get past
that fairly easily by creating the Wheel subclasses dynamically, but I’m
not sure that that is the best approach.

4

The names of the various Wheel tables are not known at design
time.

well…if this is the case, i don’t see how you’re really going to keep
from some form of dynamic creation/metaprogramming/code generation and
also keep the design clean…

maybe the option with the least dynamic change that I can think of is to
keep a similar setup: one AbstractWheel class, and one ConcreteWheel
class, but then have a method in the abstract class dynamically
overwrite the table name of the ConcreteWheel Class based upon the fetch
from the mapping table…of course, this means you have to reload the
ConcreteWheel class every time…

of course, this raises the question for me, will all wheel tables have
the exact same attributes and methods? If not, then this method won’t
work…

As far as whether this has a standard name, what you’re trying to do
sounds like a problem that is solved by one of either a factory or a
proxy, i’m not sure which…

-Gabe

5

Gabriel S. wrote:

The names of the various Wheel tables are not known at design
time.

well…if this is the case, i don’t see how you’re really going to keep
from some form of dynamic creation/metaprogramming/code generation and
also keep the design clean…

I guess the best I can come up with right now is to add a
fetch_wheel_model method somewhere that looks something like:

def get_wheel_class(table_name)
@cached_wheel_classes ||= {}
unless @cached_wheel_classes.has_key? table_name
@cached_wheel_classes[table_name] = Class.new(AbstractWheel) do
set_table_name table_name
end
end

@cached_wheel_classes[table_name]
end

but for some reason that doesn’t quite feel like a good solution.

6

but for some reason that doesn’t quite feel like a good solution.

that, overall, makes sense, and is very little code…combine it with a
before callback or method_missing?

what about it seems like a poor solution?

I come back to asking, will all wheel classes have the same attributes
and methods?

7

Pete H. wrote:

Hi All,

I have an interesting Active Record problem and I’m not quite sure what
the cleanest solution is. The legacy database that I am integrating with
has a strange wrinkle in its schema where one logical table has been
‘partitioned’ into several physical tables. Each table has the same
structure, but contains data about different items.
[…]

Why not create a “wheels” view which combines the data from all the
“wheels_for_*” tables, then tie your Wheel model to that?

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

8

Gabriel S. wrote:

but for some reason that doesn’t quite feel like a good solution.

that, overall, makes sense, and is very little code…combine it with a
before callback or method_missing?

what about it seems like a poor solution?

I come back to asking, will all wheel classes have the same attributes
and methods?

Sorry, I neglected to answer your question. Yes, all the wheel models
should have the same attributes and methods. So there would be an
AbstractWheel class something like this which each dynamically created
model would inherit from:

class AbstractWheel < ActiveRecord:Base
abstract_class = true

common functionality here

end

9

Also, that view would not be updatable (updateable?)
Not sure if that is a requirement but something to be aware of.

On May 13, 11:46 am, Pete H. [email protected]

10

Marnen Laibow-Koser wrote:

Why not create a “wheels” view which combines the data from all the
“wheels_for_*” tables, then tie your Wheel model to that?

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

I think that would have some performance issues. If there were 20
tables, but only 1 of them contained wheels for the car I was working on
then all 20 tables would have to be checked during a join, no?