From: Peter Brawley
Date: October 8 2008 7:10pm
Subject: Re: Foreign Keys
List-Archive: http://lists.mysql.com/mysql/214813
Message-Id: <48ED0599.20509@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------010003010907080309050408"

--------------010003010907080309050408
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

>I'll check the site mentioned, thank-you so much!

http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html

PB/

-----
/
Ben A. Hilleli wrote:
>
> --------------------
>
> >So you are talking about parent-child relationships in a single table, 
> or in the technical jargon
>
> >reflexive relationships. See www.artfulsoftware.com 
> <http://www.artfulsoftware.com/> for detailed examples of how this is 
> done,
>
> >but here is the thumbnail sketch:
>
> ------------------------------
>
> /Actually a Parent-childREN relationship... /
>
> /A USER can have 0 to many friends. /
>
> /This person can also BE THE friend of 0 to many other USERS./
>
> / /
>
> /AKA: I (A user) can have many friends (other users), I can be the 
> friend (user) of many users/
>
> --------------------
>
> >The table has to have a ParentID column (call it what you want) that 
> points, in the case of a
>
> >Friend, to the "Friend Of Whom" UserID column
>
> ------------------------------
>
> /Not sure what you mean as aren't you describing a 1 to 1 relation?/
>
> / /
>
> /I'll check the site mentioned, thank-you so much!/
>
>  
>
> Ben A. Hilleli
>
> *Programmer / Analyst*
>
> 	
>
>  
>
> ------------------------------------------------------------------------
>
> *From:* Arthur Fuller [mailto:fuller.artful@stripped]
> *Sent:* October 8, 2008 2:55 PM
> *To:* Ben A.H.
> *Cc:* mysql@stripped
> *Subject:* Re: Foreign Keys
>
>  
>
> So you are talking about parent-child relationships in a single table, 
> or in the technical jargon reflexive relationships. See 
> www.artfulsoftware.com <http://www.artfulsoftware.com/> for detailed 
> examples of how this is done, but here is the thumbnail sketch:
>
>  
>
> The table has to have a ParentID column (call it what you want) that 
> points, in the case of a Friend, to the "Friend Of Whom" UserID column.
>
>  
>
> This simple statement avoids the complexity of the situation where a 
> Friend can have Friends and so on. That problem requires detailed 
> explanation, which you can obtain from the site mentioned above. See 
> also Joe Celko's books.
>
>  
>
> Arthur
>
> On Wed, Oct 8, 2008 at 3:33 PM, Ben A.H. <ben@stripped 
> <mailto:ben@stripped>> wrote:
>
> I know that this is the standard means of dealing with a  many to many
> relationship, I'm just not sure it fits here.
>
>
>
> > USER:
>
> >        emailID (PK)
>
> >        userName
>
> >        Password
>
> >        Address
>
> >        Etc
>
> >
>
> > FRIEND:
>
> >        emailID (PK)
>
> >
>
> > USER_FRIEND
>
> >        user_emailID (PK)
>
> >        friend_emailID (PK)
>
>
>
> So if I want a list of USER ben@stripped <mailto:ben@stripped>'s 
> friends:
>
> SELECT friend_emailID from USER_FRIEND
>
> WHERE user_emailID="ben@stripped <mailto:ben@stripped>"
>
>
>
> In this (and pretty much every case), the FRIEND table is useless and
> doesn't make sense logically.
>
>
>
> *I THINK I EXPLAINED THINGS INCORRECTLY*
>
> Let me try again:
>
> * I'm not sure if it's even a true many to many relationship as this is
> actually a relationship between ONE RECORD in a TABLE and a ANOTHER RECORD
> in THE SAME TABLE!
>
>
>
> A USER is:
>
>
>
> USER
>
>  emailID
>
>  userName
>
>
>
> A friend is really just another RECORD in the USER table.
>
>
>
> i.e. IF I HAD TO MAP THE RELATIONSHIP LOGICALLY, IT WOULD BE:
>
>
>
> USER:
>
>  emailID
>
>  userName
>
>     |    1
>
>     |    many
>
> FRIEND:
>
>  emailID
>
>     |    many
>
>     |    1
>
> USER:
>
>  emailID
>
>  username
>
>
>
> (i.e. it's two records in the same USER table)
>
>
>
> How are relationships between records in the same table usually dealt with
> in terms of design? Implementation?
>
>
>
> ThanX,
>
>
>
> Ben
>
> ""Jim Lyons"" <jlyons4435@stripped <mailto:jlyons4435@stripped>> 
> wrote in message
> news:425b1d300810080856m202f5d5bq572a48fd393c63a9@stripped...
>
> > Indexes speed up joins.  Foreign keys should be indexes themselves, so
> > they
> > can also speed up joins.  If the FK is not an index, it won't help.  So,
> > index your FKs
> >
> > On Wed, Oct 8, 2008 at 10:43 AM, Ben A.H. <ben@stripped 
> <mailto:ben@stripped>> wrote:
> >
> >> Does using foreign keys simply enforce referential integrity OR can it
> >> also
> >> speed up JOIN queries?
> >>
> >>
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe:
> >> http://lists.mysql.com/mysql?unsub=jlyons4435@stripped
> >>
> >>
> >
> >
> > --
> > Jim Lyons
> > Web developer / Database administrator
> > http://www.weblyons.com <http://www.weblyons.com/>
> >
>
>
> --
>
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
>
> To unsubscribe:   
>  http://lists.mysql.com/mysql?unsub=fuller.artful@stripped
>
>  
>
> ------------------------------------------------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG - http://www.avg.com 
> Version: 8.0.173 / Virus Database: 270.7.6/1713 - Release Date: 10/7/2008 6:40 PM
>
>   

--------------010003010907080309050408--