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