social network data model

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
howardr
Forum Newbie
Posts: 17
Joined: Mon Oct 31, 2005 1:33 pm

social network data model

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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';
howardr
Forum Newbie
Posts: 17
Joined: Mon Oct 31, 2005 1:33 pm

Post 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?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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"...
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
howardr
Forum Newbie
Posts: 17
Joined: Mon Oct 31, 2005 1:33 pm

Post 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.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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).
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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).
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
howardr
Forum Newbie
Posts: 17
Joined: Mon Oct 31, 2005 1:33 pm

Post 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
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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.
Post Reply