asked on

Building a social network. What's a good DB structure and query for finding how two people are connected?

I'm building a social network where friends can add friends, and so on.

Now Bob wants to know how he's connected to Susan, through mutual friends of theirs.

Two questions:
1) What's a good data structure to store friend connections?
2) What's a good query to find out how any 2 people are connected (assuming a max of 4 degrees-of-separation or so)?

chapmandew

Wow...that is more than a little question. First things first is to determine the DB structure....I am thinking some sort of structure like this (by no means an exhaustive schema, just a simple idea)

Contacts
ContactID
Firstname
Lastname
Other Details

ContactFriends
PrimaryConactID
FriendContactID
OtherDetails...

One thing to consider is that ContactA is ContactB's friend, and vice versa....so, do you create two records for that relationship or just one.

Does this make sense?

pudjam666

ASKER

Thanks chapmandew.

So far so good. I think you're basically saying a "Users" table and a "ParentChild" table. Sounds good to me.

Now I need to know a good query for finding how user A is connected to user B, through which mutual friends.

I realize this isn't a simple question, but I figure I can't be the first person to ask it -- and it's gotta be a common enough question that someone here has a great answer.

SOLUTION

ozo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pudjam666

ASKER

Ozo - Thanks, that is helpful. I'm no mathematician but I am a decently clever programmer. Perhaps I can find some SQL examples of a graph somewhere.

Anyone else have any ideas? Perhaps something simpler? :)

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ozo

http://willets.org/sqlgraphs.html

pudjam666

ASKER

chapmandew - That's great. You mentioned it could be easier with SQL Server 2005 - which I am using. Are you referring to recursive queries? I found some stuff about that on the Microsoft site.

SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pudjam666

ASKER

Chapmandew - Thanks much. Unfortunately you confirmed my theory that it's not that easy of a thing to do, but at least I know I'm on the right track.