Some advice on DB modeling


#1

I need some help designing my database. i am trying to create a
community
site very similar to MySpace, Freindster, etc. I want each of my users
to be
able to add other users as friends. I’m just not sure how to set up my
tables.

I have a User object. I’m guessing I need some other table/model to
handle
the associations between the users. I created an Association model and
said
that User has_many :associations and that Association belongs_to User.
But
that doesn’t seem right.

Any help would be greatly appreciated.


#2

Ramin wrote:

Any help would be greatly appreciated.
Try this.

CREATE TABLE friends (
user_id int(11) NOT NULL default ‘0’,
friend_id int(11) NOT NULL default ‘0’,
KEY user_id (user_id),
KEY friend_id (friend_id),
CONSTRAINT friends_ibfk_1 FOREIGN KEY (user_id) REFERENCES users
(id),
CONSTRAINT friends_ibfk_2 FOREIGN KEY (friend_id) REFERENCES
users (id)
)

then

class User < ActiveRecord::Base
has_many :friends, :join_table => ‘friends’, :class_name => ‘User’,
:foreign_key => ‘friend_id’

def add_friend(friend)
friends << friend
end
end


#3

Hi, thanks for your suggestions… however, I’m getting the following
error:

Unknown key(s): join_table

I can’t find the join_table option in the has_many documentation.


#4

Sorry about that. It should be:

has_and_belongs_to_many :friends, :join_table => ‘friends’, :class_name
=> ‘User’, :foreign_key => ‘friend_id’

You can also access the inverse association with:

has_and_belongs_to_many :friends_of, :join_table => ‘friends’,
:class_name => ‘User’, :foreign_key => ‘user_id’,
:association_foreign_key => ‘friend_id’