How would you do a multitable cross reference select box in Rails?

I've done this in other languages, but how would you handle the following issue in Rails?

Assume database tables a, b and c.
Each table has id,name,text fields
(and yes, there are reasons why they are separate tables -
I'm simplifying here)

Any record in any table can cross-reference one or more records
in that table or another table. The cross reference tables are
a_a, a_b, a_c, b_b, b_c, c_c [fields are as expected a_id,b_id, etc)

So assume record 1 in table a is referenced by:
record 22 in table a
record 347 in table b and
record 19 in table c.

When a record is displayed, I have a dropdown select box that shows
the name of any cross references. The contents are generated by a
database view created by multiple selects combined with union.

The obvious hurdle in a select box is returning not just the id of the
selected item, but also the table in which that particular item is located.
In my current (non-ruby, non-rails) implementation, this is solved by
concatenating the table name with a separator and the record id

The select box thus returns the combined value which is then exploded
into its parts so that the webapp can display the desired item. This
isn't difficult when done manually, but what would be the appropriate
way of handling the issue in the "Rails" way?

Are you free to modify the database?
I'd probably try making a new model CrossReference with it's own table
This table can hold a_id, b_id and c_id, and your A, B and C each
has_many :cross_references.
If it makes sense for your models, A has_many :bs, :through =>
:cross_references etc.
I don't know exactly what the details are for referencing the same
table (what you have in a_a, b_b and c_c), but I imagine it's doable.

You'd have to add columns (d_id) to the cross_references table when
you add a table (d) to be cross referenced. But on the other hand, in
your current setup, you'd have to add an increasing number of new
reference-tables a_d, b_d, c_d and d_d.

hmm.. hope that helps
merry x-mas
- Martin

I could modify the database to add a multitable linking table - I
can't reduce the current number of linking tables because it would
break existing apps that I don't control. The three real data tables
have a lot of fields that are not in common and serve different
purposes, so they can't really be combined.

Even if I have a single multi-table linking table, I'm not sure that
solves the problem of how the select box passes the correct info.

A has_many :bs, :through => :cross_references
A has_many :cs, :through => :cross_references

I'm still puzzled at what the return from the single select box will
look like.


Martin Svalin wrote:

Why can't you use this method in Rails with options_for_select? You
can pass in an array of 2-element arrays, each of which contains the
text to display and the value to return. You can make the values just
like you did before.


It almost sounds like polymorphic table inheritance? (from the agile
I'm having a hard time visualizing what the use-case would be (ie:
Employee < Person; Manager < Person; Mentor < Person subclassing?)
and how that would look in the select box itself.

Think of the use case this way (again it is somewhat simplified)
Table A is text of laws relevant to our company
Table B is external commentary on laws (think articles)
Table C is internal commentary on laws (think internal notes).

Any law can be flagged with one or more references to another law, or
commentaries from either commentary table.
Any commentary (external or internal) can be flagged with one or more
references to one or more laws, or commentary from either commentary

According to our lawyers, we need to keep the external commentary
separate from our internal commentary in order to avoid fights with
our external suppliers.


chovy wrote:

Perhaps a commentaries table with 'type' attribute (externel or
internal), and an foreign key that references the laws table and its

Would look something like:

create_table commentaries:
   id => :integer,
   type => :string, #=> (internal or external)
   comment => :text
   law_id => :integer #=> references

create_table laws:
  id => :integer,
  name => :string,
  text => :text