Page 1 of 1

social network data model

Posted: Wed Jun 14, 2006 1:53 am
by howardr
I was just curious to what a good database table structure would look like for simple user relationship functionality. I have currenlty come up with 1 model with 2 ways to store data. im not sure which way is better, or if either one is good at all

table: users

Code: Select all

id
name
table: friends

Code: Select all

id
user1
user2
This is where i get confused how to store info

Way 1

table: friends

Code: Select all

id     user1 user2
--------------------------
1      1      2
SQL to acces friends if you were user id=1 in the users table

Code: Select all

SELECT users.*
FROM users, friends
WHERE 
      ((friends.user1=1 AND friends.user2=users.id) OR (friends.user2=1 AND friends.user1=users.id))
Way 2

this way uses a reciprocal link thing

table: friends

Code: Select all

id     user1 user2
--------------------------
1      1      2
2      2      1
SQL to acces friends if you were user id=1 in the users table

Code: Select all

SELECT users.*
FROM users, friends
WHERE friends.user1=1 AND friends.user2=users.id

Posted: Wed Jun 14, 2006 2:39 am
by RobertGonzalez
Logically, any user can have any number of unique friends. So what you will end up with is a series of relationships where user1 is friends with user2. You can set this up with two tables:

users

Code: Select all

user_id (primary)
user_name
friends

Code: Select all

user_id_1 (primary)
user_id_2 (primary)
You would be able to select all friends of user 'jojo' by

Code: Select all

SELECT u.user_name AS user_name_first, u2.user_name AS user_name_second 
FROM users u INNER JOIN friends f ON u.user_id = f.user_id_1 
    INNER JOIN users u2 ON f.user_id_2 = u2.user_id 
WHERE u.user_name = 'jojo' 
OR u2.user_name = 'jojo';

Posted: Wed Jun 14, 2006 2:48 am
by howardr
so if im inserting the relationship into the friends table, i would just insert the reltionship of once. i wouldn't also have to insert the reciprcal releationship?

Posted: Wed Jun 14, 2006 2:56 am
by RobertGonzalez
That's right. If you and I are friends, why would you insert a record for me and you then one for you and me? Both you and I can have any number of friends, but each will be unique to both of us. Enter one relationship, then search on the relationship how you want looking for either person 1 or person 2.

Posted: Wed Jun 14, 2006 3:01 am
by GM
Careful though...

It really depends on whether you are assuming that the relationship is reciprocal or not ie: If UserA is a friend of UserB, then UserB is also a friend of UserA.

I assume that these relationships are controllable by the users themselves, how would you deal with the situation where UserA has UserB on his "buddy list", but UserB doesn't want UserA on his? If you have only one relation in the database, you'd also need a couple of flag fields to show whether the users appear on each other's "buddy lists"...

Posted: Wed Jun 14, 2006 3:12 am
by Weirdan
Everah wrote:That's right. If you and I are friends, why would you insert a record for me and you then one for you and me? Both you and I can have any number of friends, but each will be unique to both of us. Enter one relationship, then search on the relationship how you want looking for either person 1 or person 2.
Quite contrary, some large social networks (LiveJournal is an obvious example) make distinction between the terms friend and mutual friend.

Posted: Wed Jun 14, 2006 3:14 am
by RobertGonzalez
I do believe I have been disproven. GM and Weirdan are both right. If you are planning on offering reciprocation you will need to be able to add flags to the relationships table. Sorry for steering you in the wrong direction.

Posted: Wed Jun 14, 2006 9:57 am
by howardr
thanks for the responses. .i was more curious than anything because there isnt much information on how to do relationships like this. I have played around with graphs before, but that was with java with a ragged array and not mysql. Now i'm going to try to figure out the sql to get the path from node a (user 1) to node b (user 2) if they arnt friends.

Posted: Wed Jun 14, 2006 10:53 am
by GM
howardr wrote:thanks for the responses. .i was more curious than anything because there isnt much information on how to do relationships like this. I have played around with graphs before, but that was with java with a ragged array and not mysql. Now i'm going to try to figure out the sql to get the path from node a (user 1) to node b (user 2) if they arnt friends.
My initial reaction to this post was "SQL won't do that".

Then I thought "SQL can only do that using a stored procedure, which will loop on the table".

Then I thought "Sounds to me like you need a recursive PHP function."


If you do manage it in SQL, let me know, but at first glance it's not possible because you've got know way of knowing the degree of separation of the two users (ie: how many people are in the "chain" of people between them).

Posted: Wed Jun 14, 2006 11:16 am
by RobertGonzalez
You know, there is no reason why you can't use the same table setup proposed. The only change is that you would include reciprocated relationships. So that if I added you as a friend, it would show you as a frined on my list, but not vice versa until you added me to your friends list. Then we would be 'related'. Still possible in my opinion.

Posted: Wed Jun 14, 2006 11:44 am
by GM
Everah wrote:You know, there is no reason why you can't use the same table setup proposed. The only change is that you would include reciprocated relationships. So that if I added you as a friend, it would show you as a frined on my list, but not vice versa until you added me to your friends list. Then we would be 'related'. Still possible in my opinion.
Exactly - the table setup works fine as long as the relationships are NOT reciprocal, and that UserA is friends with UserB does NOT imply that UserB is friends with UserA.

In this case the table would contain one entry for UserA - UserB relationship, and another entry for UserB - UserA relationship.

If however, there is only one entry for the relationship (bad idea in my view), there would also need to be a couple of flag fields (which would say in effect who is friends with who).

On the second post, about trying to write SQL to find the path from A to B where A and B are not friends, I'm pretty much convinced that it can't be done with SQL. I once built a Private Messaging system, where I wanted to be able to show the user the whole history of the PM, ie: The first message, the response to that message, the response to the response etc. etc. and found that SQL would only take me back one level per table join (I was joining the table with itself). Since I had no way of knowing how many messages were in the history, I couldn't do it wth simple SQL, and had to use a recursive function (similar to one that parses a filesystem).

Posted: Wed Jun 14, 2006 3:15 pm
by RobertGonzalez
Trying to find a path from userA to userB without them being friends (either A to B or B to A) isn't a possibility since there is not relationship between them. At least I think.

Posted: Wed Jun 14, 2006 4:32 pm
by feyd
Instead of calling it a "friends" table, why not call it a "relationship" table? Would it then make more sense? You could easily store what kind of a relationship it is, including an "ignore" setting.

Posted: Wed Jun 14, 2006 5:14 pm
by howardr
On my dev machine, i called the "friends" table "relationships", i was just using it as an example.

And a path from userA to userB can be possible. The relationships create an undirectional graph, and as long as two users are within the same graph, a path can be found. The question is can you script sql within mysql to find the path, or would you have to go ahead and use multiple calls with php. Also you probably wouldnt want to find paths that have 5 or 6 edges because of server resources.

http://www.artfulsoftware.com/mysqlbook ... 1ch20.html

Posted: Thu Jun 15, 2006 2:48 am
by GM
howardr wrote:question is can you script sql within mysql to find the path, or would you have to go ahead and use multiple calls with php.
I'm 99% sure that this is impossible with only SQL.

You don't necessarily need to make multiple calls with PHP - you can extract all the data from the table into a PHP array and process that, rather than doing multiple queries.