Multiple foreign keys to the same type of table


#1

I’m not sure how to have two foreign keys of the same type.

e.g.

class CreateFriends < ActiveRecord::Migration
def self.up
create_table :friends do |t|
t.column :user_id, :integer
t.column :user_id, :integer
t.column :ready, :boolean
end
end

def self.down
drop_table :friends
end
end

Now I can’t have the same name twice, but if i change one of them to
user2_id won’t this break the naming convention.

Any ideas?
Is this a bad idea anyway?


#2

On 5/23/07, Bob removed_email_address@domain.invalid wrote:

t.column :ready, :boolean

Any ideas?

The api docs for ActiveRecord::Associations::ClassMethods describe how
you specify associations between your models, including overriding the
default foreign key and class name when you don’t follow rails’ naming
conventions.

Is this a bad idea anyway?

There are lots of sound reasons for referring to the same table
multiple times from a single row.

It could also mean that your FK is in the wrong table, if you’re
just trying to model a one-to-many relationship.

Isak


#3

Just guessing from your model names but it appears that you are
building a “reflexive many-to-many relationship.” Reflexive meaning
that you are creating a join table between two rows of a single table:

User <------->> Friend <<-------> User

An excerpt from “Rails Recipies” shows this type of Self Referential
Many-to-Many Releationship:

class AddPeopleAndTheirFriendsRelationship < ActiveRecord::Migration
def self.up
create_table :people do |t|
t.column “name”, :string
end
create_table :friends_people, :id => false do |t|
t.column “person_id”, :integer
t.column “friend_id”, :integer
end
end

def self.down
drop_table :people
drop_table :friends_people
end
end

and the Person model looks like:

class Person < ActiveRecord::Base
has_and_belongs_to_many :friends,
:class_name => “Person”,
:join_table => “friends_people”,
:association_foreign_key => “friend_id”,
:foreign_key => “person_id”
end


#4

By the way, I would likely model this with the join table as a
separate resource using has_many_through. I would call the joining
resource Friend. This way you can store information about the
association in the Friend model. Such as in your case with :ready =>
boolean. Or things like friendship_established_on and other such
details.