--- 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]

Reply via email to