RE: Social Network, linking members
--- Martin Gallagher [EMAIL PROTECTED] wrote:
of course you have the problem where john has Joe as a friend but
Joe
doesn't have john as a friend. This seeming inconsistency, may or
may not
be a problem depending on exactly what kind of a relationship you are
trying
to define.
You've just hit the nail on the head! That's exactly the problem.
I think I might just have to grin and bear what I already have :-(
-Original Message-
From: 2wsxdr5 [mailto:[EMAIL PROTECTED]
Sent: 07 April 2006 15:11
To: Martin Gallagher; mysql@lists.mysql.com
Subject: Re: Social Network, linking members
Martin Gallagher wrote:
Hi,
I'm trying to find the most efficient way of linking members to
one
another in a social networking application.
Currently I link them using 2 separate fields for the members: id1,
id2.
So,
to find people in your network you would do:
I'm not sure exactly what it is you are doing but I think this may be
it. You have a table of people and you want to know who is friends
with
who. I know 'friend' may not be the best term to use but it is
easier
to type. So I have my people table.
People{
*PID,
Name,
. . .
}
Then the Friend Table,
Friend{
*PID,
*FID
}
If you have person, John, with ID 234, and you want to know all his
friends you can do this...
SELECT f.FID, p.Name
FROM Friend f JOIN People p ON f.FID = p.PID
WHERE f.PID = 234
of course you have the problem where john has Joe as a friend but Joe
doesn't have john as a friend. This seeming inconsistency, may or
may
not be a problem depending on exactly what kind of a relationship you
are trying to define.
--
Chris W
KE5GIX
Gift Giving Made Easy
Get the gifts you want
give the gifts they want
One stop wish list for any gift,
from anywhere, for any occasion!
http://thewishzone.com
No, you don't need to keep what you have. What everyone is trying to
help you do is to normalize your data by splitting your relationship
information into it's own table. The new table (most of the responses
have given it just three columns but it can have MORE... more on that
later) is the fastest way to model the many-to-many relationships
between friends as it allows full usage of available indexes.
The directionality of the relationship is determined by whose ID is
in the from column and whose ID is in the to column. Using Jad's
example:
Relation table
rel_id
user_id
friend_id
The from column is `user_id` and the to column is `friend_id`. That
defines the relationship in one direction. If the friendship is
symmetrical there will be a second entry where the `user_id` and
`friend_id` values are reversed.
Each row in this table represents a relationship between two
people. You go on to describe a status value and possibly a
type of relationship. All this requires is a couple more columns to
the Relation table
Relation table
rel_id
user_id
friend_id
group
flag
This way each user only needs one record to exist in your system. Each
relationship between each pair of users takes either one (asymmetrical
friendship) or two (symmetrical friendship) relationship records. If
all of the fields in this relationship table are numeric, then every
row of data will be the exact same length (as stored on disk). This
fixed-width type of table is EXTREMELY fast for searching and
retrievals.
I honestly encourage you to attempt to use a second table in your
design.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Social Network, linking members
2006/4/8, Philippe Poelvoorde [EMAIL PROTECTED]: Hi, 2006/4/8, Dan Buettner [EMAIL PROTECTED]: Seems like what you're looking for is a way to query your database more efficiently/quickly, and still find all links in either direction. I think the use of a UNION statement should allow this. Basically write your query twice, joining on id1 the first time and id2 the second time, with UNION in the middle. To select reciprocal friends of Dan (that is a friends relationship in both ways), I would do that : select p.name from friends f1, friends f2, people p where p.peopleid=f1.id1 and f1.id2=1 AND f1.id2=f2.id1 AND f1.id1=f2.id2 UNION select p.name from friends f1, friends f2, people p where p.peopleid=f1.id2 and f1.id1=1 AND f1.id1=f2.id2 AND f1.id2=f2.id1; +--+ | name | +--+ | Matt | +--+ But I don't think it's the best solution in term of performance :) Sorry, can anyone comment this query ? How would you find the list of symetric friend of Dan ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Social Network, linking members
--- Philippe Poelvoorde [EMAIL PROTECTED] wrote: 2006/4/8, Philippe Poelvoorde [EMAIL PROTECTED]: Hi, 2006/4/8, Dan Buettner [EMAIL PROTECTED]: Seems like what you're looking for is a way to query your database more efficiently/quickly, and still find all links in either direction. I think the use of a UNION statement should allow this. Basically write your query twice, joining on id1 the first time and id2 the second time, with UNION in the middle. To select reciprocal friends of Dan (that is a friends relationship in both ways), I would do that : select p.name from friends f1, friends f2, people p where p.peopleid=f1.id1 and f1.id2=1 AND f1.id2=f2.id1 AND f1.id1=f2.id2 UNION select p.name from friends f1, friends f2, people p where p.peopleid=f1.id2 and f1.id1=1 AND f1.id1=f2.id2 AND f1.id2=f2.id1; +--+ | name | +--+ | Matt | +--+ But I don't think it's the best solution in term of performance :) Sorry, can anyone comment this query ? How would you find the list of symetric friend of Dan ? Assuming that we have this table pseudo-structure CREATE TABLE person ( id name ) CREATE TABLE friends ( id from_person_id to_person_id ) And the following data: person _ _ID_|_NAME___ 1 | Alpha 2 | Bravo 3 | Charlie 4 | Delta 5 | Echo friends __ _ID _|_FROM_|_TO__ 1 |1 | 1 1 |1 | 2 1 |1 | 3 1 |1 | 4 1 |1 | 5 1 |2 | 1 1 |3 | 1 1 |4 | 1 This represents a graph of Alpha being friends with all of the other people but only Bravo, Charlie, and Delta return the favor (symmetric friendships). The relationship with Echo is asymmetrical. A query to pick out only those relationships that are symmetrical would look like: SELECT p1.name, p2.name FROM person p1 INNER JOIN friends f1 on f1.from_person_id = p1.id INNER JOIN friends f2 on f2.to_person_id = p1.id AND f2.from_person_id = f1.to_person_id INNER JOIN person p2 ON p2.id = f1.to_person_id; There are many ways to make that faster but this is the general form of the query. Shawn Green Database Administrator Unimin Corporation - Spruce Pine __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Social Network, linking members
Hi, 2006/4/8, Dan Buettner [EMAIL PROTECTED]: Seems like what you're looking for is a way to query your database more efficiently/quickly, and still find all links in either direction. I think the use of a UNION statement should allow this. Basically write your query twice, joining on id1 the first time and id2 the second time, with UNION in the middle. To select reciprocal friends of Dan (that is a friends relationship in both ways), I would do that : select p.name from friends f1, friends f2, people p where p.peopleid=f1.id1 and f1.id2=1 AND f1.id2=f2.id1 AND f1.id1=f2.id2 UNION select p.name from friends f1, friends f2, people p where p.peopleid=f1.id2 and f1.id1=1 AND f1.id1=f2.id2 AND f1.id2=f2.id1; +--+ | name | +--+ | Matt | +--+ But I don't think it's the best solution in term of performance :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Social Network, linking members
Hi, I'm trying to find the most efficient way of linking members to one another in a social networking application. Currently I link them using 2 separate fields for the members: id1, id2. So, to find people in your network you would do: WHERE id1=%ID OR id2=%ID This uses indexes inefficiently, especially when I add a JOIN to the mixture. The JOIN must really slow down the query because I use an IF control to get the right ID e.g.: JOIN members ON members.id=IF(social_networking.id1=%ID, social_networking.id2, social_networking.id1) I believed in my ignorance that I could just use the SET data type, like so: WHERE FIND_IN_SET(%ID, link) link being 2 integers separated by a comma: 5,19 However, SET only allows defined values :-( A dynamic SET data type that supported integers would be sweet, but doesn't seem to exist. Does anybody have an efficient solution to this problem? Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Social Network, linking members
How about something like this Users table user_id user_name user_password user_whatever and then Relation table rel_id user_id friend_id and then selecting from relations table On Fri, 2006-04-07 at 14:47 -0700, Martin Gallagher wrote: urrently I link them using 2 separate fields for the members: id1, id2. So, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Social Network, linking members
That's similar to what I currently have set up. The problem is it is a request based system. Example table: id1 id2 group flag When a person makes a request something such as the following is insert: MYID, REQUEST_USER_ID, 'family', 0 If user REQUEST_USER_ID accepts it will be flagged as 1: MYID, REQUEST_USER_ID, 'family', 1 Using 2 tables would help the indexing problem, but it's definetly NOT how I want to achieve this. -Original Message- From: Jad madi [mailto:[EMAIL PROTECTED] Sent: 07 April 2006 15:00 To: Martin Gallagher Cc: mysql@lists.mysql.com Subject: Re: Social Network, linking members How about something like this Users table user_id user_name user_password user_whatever and then Relation table rel_id user_id friend_id and then selecting from relations table On Fri, 2006-04-07 at 14:47 -0700, Martin Gallagher wrote: urrently I link them using 2 separate fields for the members: id1, id2. So, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Social Network, linking members
Martin Gallagher wrote:
Hi,
I'm trying to find the most efficient way of linking members to one
another in a social networking application.
Currently I link them using 2 separate fields for the members: id1, id2. So,
to find people in your network you would do:
I'm not sure exactly what it is you are doing but I think this may be
it. You have a table of people and you want to know who is friends with
who. I know 'friend' may not be the best term to use but it is easier
to type. So I have my people table.
People{
*PID,
Name,
. . .
}
Then the Friend Table,
Friend{
*PID,
*FID
}
If you have person, John, with ID 234, and you want to know all his
friends you can do this...
SELECT f.FID, p.Name
FROM Friend f JOIN People p ON f.FID = p.PID
WHERE f.PID = 234
of course you have the problem where john has Joe as a friend but Joe
doesn't have john as a friend. This seeming inconsistency, may or may
not be a problem depending on exactly what kind of a relationship you
are trying to define.
--
Chris W
KE5GIX
Gift Giving Made Easy
Get the gifts you want
give the gifts they want
One stop wish list for any gift,
from anywhere, for any occasion!
http://thewishzone.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Social Network, linking members
of course you have the problem where john has Joe as a friend but Joe
doesn't have john as a friend. This seeming inconsistency, may or may not
be a problem depending on exactly what kind of a relationship you are trying
to define.
You've just hit the nail on the head! That's exactly the problem.
I think I might just have to grin and bear what I already have :-(
-Original Message-
From: 2wsxdr5 [mailto:[EMAIL PROTECTED]
Sent: 07 April 2006 15:11
To: Martin Gallagher; mysql@lists.mysql.com
Subject: Re: Social Network, linking members
Martin Gallagher wrote:
Hi,
I'm trying to find the most efficient way of linking members to one
another in a social networking application.
Currently I link them using 2 separate fields for the members: id1, id2.
So,
to find people in your network you would do:
I'm not sure exactly what it is you are doing but I think this may be
it. You have a table of people and you want to know who is friends with
who. I know 'friend' may not be the best term to use but it is easier
to type. So I have my people table.
People{
*PID,
Name,
. . .
}
Then the Friend Table,
Friend{
*PID,
*FID
}
If you have person, John, with ID 234, and you want to know all his
friends you can do this...
SELECT f.FID, p.Name
FROM Friend f JOIN People p ON f.FID = p.PID
WHERE f.PID = 234
of course you have the problem where john has Joe as a friend but Joe
doesn't have john as a friend. This seeming inconsistency, may or may
not be a problem depending on exactly what kind of a relationship you
are trying to define.
--
Chris W
KE5GIX
Gift Giving Made Easy
Get the gifts you want
give the gifts they want
One stop wish list for any gift,
from anywhere, for any occasion!
http://thewishzone.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Social Network, linking members
probuly sounds like noobish! or silly but what i would do is have a new
table with 2 colums the first will be a login id and the second will be
a login id
and i would just do like ...
id1 id2
JohnJoe
JohnAlex
JohnFred
Joe Fred
FredAlex
would mean John as Alex, Fred and Joe as his friends
Joe has Fred as a friend
and Fred had Alex as a friend
and would just do like
SELECT `id2` FROM `freiends` WHERE `id1` = 'John';
to get all of Johns friends
and
SELECT `id1` FROM `friends` WHERE `id2` = 'Fred';
to get people that had fred set as their friends
and just work around that, of cause you can have id or account numbers
and not names, can anybody see any problems with that ?
Martin Gallagher wrote:
of course you have the problem where john has Joe as a friend but Joe
doesn't have john as a friend. This seeming inconsistency, may or may not
be a problem depending on exactly what kind of a relationship you are trying
to define.
You've just hit the nail on the head! That's exactly the problem.
I think I might just have to grin and bear what I already have :-(
-Original Message-
From: 2wsxdr5 [mailto:[EMAIL PROTECTED]
Sent: 07 April 2006 15:11
To: Martin Gallagher; mysql@lists.mysql.com
Subject: Re: Social Network, linking members
Martin Gallagher wrote:
Hi,
I'm trying to find the most efficient way of linking members to one
another in a social networking application.
Currently I link them using 2 separate fields for the members: id1, id2.
So,
to find people in your network you would do:
I'm not sure exactly what it is you are doing but I think this may be
it. You have a table of people and you want to know who is friends with
who. I know 'friend' may not be the best term to use but it is easier
to type. So I have my people table.
People{
*PID,
Name,
. . .
}
Then the Friend Table,
Friend{
*PID,
*FID
}
If you have person, John, with ID 234, and you want to know all his
friends you can do this...
SELECT f.FID, p.Name
FROM Friend f JOIN People p ON f.FID = p.PID
WHERE f.PID = 234
of course you have the problem where john has Joe as a friend but Joe
doesn't have john as a friend. This seeming inconsistency, may or may
not be a problem depending on exactly what kind of a relationship you
are trying to define.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Social Network, linking members
Seems like what you're looking for is a way to query your database
more efficiently/quickly, and still find all links in either
direction.
I think the use of a UNION statement should allow this. Basically
write your query twice, joining on id1 the first time and id2 the
second time, with UNION in the middle.
http://dev.mysql.com/doc/refman/5.0/en/union.html
I put together a quick setup like so:
** start of SQL **
create table people (
peopleid int unsigned not null auto_increment primary key,
name varchar(50) );
insert into people (name) values ('Dan');
insert into people (name) values ('Matt');
insert into people (name) values ('Jim');
insert into people (name) values ('Mark');
insert into people (name) values ('Chris');
insert into people (name) values ('Deanna');
insert into people (name) values ('Toni');
insert into people (name) values ('Teri');
create table friends (
id1 int not null,
id2 int not null,
key (id1),
key (id2) );
insert into friends (id1, id2) values (1, 2); /* Dan, Matt */
insert into friends (id1, id2) values (2, 1); /* Matt, Dan */
insert into friends (id1, id2) values (1, 4); /* Dan, Mark */
insert into friends (id1, id2) values (1, 5); /* Dan, Chris */
insert into friends (id1, id2) values (1, 6); /* Dan, Deanna */
insert into friends (id1, id2) values (1, 7); /* Dan, Toni */
insert into friends (id1, id2) values (8, 1); /* Teri, Dan */
/* This should mean that Dan (id 1) has designated friends of Matt,
Mark, Chris, Deanna, and Toni, while Matt and Teri have both
designated Dan as a friend.
Jim, if you're listening, sorry, nobody had said you're a friend. ;)
What we should see from the query below is everyone who has a link in
either direction with Dan (6 total) */
select p.name from friends f, people p
where p.peopleid = f.id1
and f.id2 = 1
UNION
select p.name from friends f, people p
where p.peopleid = f.id2
and f.id1 = 1;
++
| name |
++
| Matt |
| Teri |
| Mark |
| Chris |
| Deanna |
| Toni |
++
6 rows in set (0.00 sec)
EXPLAIN select p.name from friends f, people p
- where p.peopleid = f.id1
- and f.id2 = 1
- UNION
- select p.name from friends f, people p
- where p.peopleid = f.id2
- and f.id1 = 1
- ;
++--+++---+-+-++--+-+
| id | select_type | table | type | possible_keys | key |
key_len | ref| rows | Extra |
++--+++---+-+-++--+-+
| 1 | PRIMARY | f | ref| id1,id2 | id2 |
4 | const |2 | |
| 1 | PRIMARY | p | eq_ref | PRIMARY | PRIMARY |
4 | misc.f.id1 |1 | Using where |
| 2 | UNION| f | ref| id1,id2 | id1 |
4 | const |4 | |
| 2 | UNION| p | eq_ref | PRIMARY | PRIMARY |
4 | misc.f.id2 |1 | Using where |
|NULL | UNION RESULT | union1,2 | ALL| NULL | NULL
| NULL| NULL | NULL | |
++--+++---+-+-++--+-+
5 rows in set (0.00 sec)
** END OF SQL **
You can see from the results of the EXPLAIN query that MySQL (5.0.16
in my case) uses two primary key lookups, so appears to be operating
pretty efficiently. I think even with a lot more data this is going
to remain a very efficient query in MySQL.
MySQL before 4.0 did not support UNION.
Hope this helps!
Dan
of course you have the problem where john has Joe as a friend but Joe
doesn't have john as a friend. This seeming inconsistency, may or may not
be a problem depending on exactly what kind of a relationship you are trying
to define.
You've just hit the nail on the head! That's exactly the problem.
I think I might just have to grin and bear what I already have :-(
-Original Message-
From: 2wsxdr5 [mailto:[EMAIL PROTECTED]
Sent: 07 April 2006 15:11
To: Martin Gallagher; mysql@lists.mysql.com
Subject: Re: Social Network, linking members
Martin Gallagher wrote:
Hi,
I'm trying to find the most efficient way of linking members to one
another in a social networking application.
Currently I link them using 2 separate fields for the members: id1, id2.
So,
to find people in your network you would do:
I'm not sure exactly what it is you are doing but I think this may be
it. You have a table of people and you want to know who is friends with
who. I know 'friend' may not be the best term to use but it is easier
to type. So I have my people table.
People{
*PID,
Name,
. . .
}
Then the Friend Table,
Friend{
*PID,
*FID
}
If you have person, John, with ID 234, and you want to know all his
friends you can do this...
SELECT f.FID, p.Name
FROM Friend f JOIN People p ON f.FID = p.PID
WHERE f.PID
