Database schema for social network members table

[eluser]simpleCoder[/eluser]
Hi Guys,
Am trying to create a database structure for a table(s) that stores members information and allow members to add friends to their profile. in short , how do you implement a db table(s) for people relationships like Facebook.
Anyone got any good ideas on how to create a schema for that.

[eluser]jedd[/eluser]
What have you got so far?

I'm guessing:

person
id
name (various fields)
email
join_date
etc

friend
id
person1_id // fk to person.id
person2_id // fk to person.id
created_when
pending // boolean

- for starters.

What are you getting stuck on?

[eluser]simpleCoder[/eluser]
Thank you soo much man. I have not had any problems. i just wanted to get some tips on how to architecture my database.

[eluser]alboyd[/eluser]
I've got a friends system in a site that I created a while back and at the time I had a table like this:

Code:

CREATE TABLE `tbl_friends` (
  `user_id` int(11) NOT NULL,
  `friend_id` int(11) NOT NULL,
  `approved` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`user_id`,`friend_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Coulda probably done the create_date, active etc but I think in my case if they are no longer friends i just delete the row.

[eluser]jedd[/eluser]
Did you have two rows for each friend relationship, or did you search on either user or friend field's when looking for relationships?

[eluser]alboyd[/eluser]
aaah lol good question!

I actually had two rows per relationship. I can't remember how I managed that - doesn't seem very nice way of doing it!

[eluser]jedd[/eluser]
Fairy nuff. On the table I described, you'd still (of course) need a WHERE clause that allowed for user id in person1 OR person2 .. but the distinction between originating versus target person was necessarily blurred. I haven't done this in a Real Live System before, but am doing similar things with other types of data in my current project.

The naming you used indicated an owner of the relationship - which might be of interest (who established the connection, I mean) though it's probably not a hugely important piece of data to retain for friendly social networks.

[eluser]alboyd[/eluser]
yeh i think in practise that's why I did it - able to know who requested and who is yet to approve...