dynamic table names for Active Record models

Question

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

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

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

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.

EDIT: Note that changing the schema to be closer to what AR wants is not an option. Various legacy codebases rely on this schema and cannot realistically be modified.

Answer 1

Why not simply put all the wheels in one table and use a standard :has_many? You can do it in a migration:

1. create new wheels table
2. migrate data from other tables to the newly created table
3. delete the old tables

Answer 2

DB table partitioning is pretty common practice really. I'd be surprised if someone hasn't done this before. How about ActsAsPartitionable? http://revolutiononrails.blogspot.com/2007/04/plugin-release-actsaspartitionable.html

Another possibility: can your DBMS pretend that the partitions are one big table? I think MySQL supports this.

Answer 3

I would do this association with custom function in model:

has_one :cat_to_wheel_table_map

def wheels
  Wheel.find_by_sql("SELECT * FROM #{cat_to_wheel_table_map.wheel_table} WHERE car_id == #{id}")
end

Maybe you can do it using association with :finder_sql, but I'm not sure how to pass arguments to it. I used model Wheel which you have to define if you want your data to be mapped by ActiveRecord. Probably you can make this model from one of your existing tables with wheels.

And I didn't test it ;).

Answer 4

Here's a way you can do it. The basics (before the 70 lines of code) are:

• create a has_many for each car type
• define a method "wheels" that uses the table name in the association to get the right wheels

Let me know if you have any questions

#!/usr/bin/env ruby
%w|rubygems active_record irb|.each {|lib| require lib}
ActiveSupport::Inflector.inflections.singular("toyota", "toyota")
CAR_TYPES = %w|ford buick toyota|

ActiveRecord::Base.logger = Logger.new(STDOUT)
ActiveRecord::Base.establish_connection(
  :adapter => "sqlite3",
  :database => ":memory:"
)

ActiveRecord::Schema.define do
  create_table :cars do |t|
    t.string :name
  end

  create_table :car_to_wheel_table_map, :id => false do |t|
    t.integer :car_id
    t.string :wheel_table
  end

  CAR_TYPES.each do |car_type|
    create_table "wheels_for_#{car_type.pluralize}" do |t|
      t.integer :car_id
      t.string :color
    end
  end
end

CAR_TYPES.each do |car_type|
  eval <<-END
    class #{car_type.classify}Wheel < ActiveRecord::Base
      set_table_name "wheels_for_#{car_type.pluralize}"
      belongs_to :car
    end
  END
end

class Car < ActiveRecord::Base
  has_one :car_wheel_map

  CAR_TYPES.each do |car_type|
    has_many "#{car_type}_wheels"
  end

  delegate :wheel_table, :to => :car_wheel_map

  def wheels
    send("#{wheel_table}_wheels")
  end
end

class CarWheelMap < ActiveRecord::Base
  set_table_name "car_to_wheel_table_map"
  belongs_to :car
end


rav4 = Car.create(:name => "Rav4")
rav4.create_car_wheel_map(:wheel_table => "toyota")
rav4.wheels.create(:color => "red")

fiesta = Car.create(:name => "Fiesta")
fiesta.create_car_wheel_map(:wheel_table => "ford")
fiesta.wheels.create(:color => "green")

IRB.start if __FILE__ == $0

Answer 5

How about this instead? (here's the gist: http://gist.github.com/111041)

#!/usr/bin/env ruby
%w|rubygems active_record irb|.each {|lib| require lib}
ActiveSupport::Inflector.inflections.singular("toyota", "toyota")

ActiveRecord::Base.logger = Logger.new(STDOUT)
ActiveRecord::Base.establish_connection(
  :adapter => "sqlite3",
  :database => ":memory:"
)

ActiveRecord::Schema.define do
  create_table :cars do |t|
    t.string :name
  end

  create_table :car_to_wheel_table_map, :id => false do |t|
    t.integer :car_id
    t.string :wheel_table
  end

  create_table :wheels_for_fords do |t|
    t.integer :car_id
    t.string :color
  end

  create_table :wheels_for_toyotas do |t|
    t.integer :car_id
    t.string :color
  end
end

class Wheel < ActiveRecord::Base
  set_table_name nil
  belongs_to :car
end

class CarWheelMap < ActiveRecord::Base
  set_table_name "car_to_wheel_table_map"
  belongs_to :car
end

class Car < ActiveRecord::Base
  has_one :car_wheel_map
  delegate :wheel_table, :to => :car_wheel_map

  def wheels
    @wheels ||= begin
      the_klass = "#{wheel_table.classify}Wheel"
      eval <<-END
        class #{the_klass} < ActiveRecord::Base
          set_table_name "wheels_for_#{wheel_table.pluralize}"
          belongs_to :car
        end
      END

      self.class.send(:has_many, "#{wheel_table}_wheels")
      send "#{wheel_table}_wheels"
    end
  end
end

rav4 = Car.create(:name => "Rav4")
rav4.create_car_wheel_map(:wheel_table => "toyota")

fiesta = Car.create(:name => "Fiesta")
fiesta.create_car_wheel_map(:wheel_table => "ford")

rav4.wheels.create(:color => "red")
fiesta.wheels.create(:color => "green")

# IRB.start if __FILE__ == $0

Answer 6

Sadly, I know your troubles. Whoever thought to partition the tables in your database must have gotten fired by your boss and hired by mine ;-)

Anyway, the solution (via RailsForum): http://railsforum.com/viewtopic.php?id=674

is to use Dr. Nic's Magic Models.

Cheers