List:General Discussion« Previous MessageNext Message »
From:Ben A. Hilleli Date:October 8 2008 7:01pm
Subject:RE: Foreign Keys
View as plain text  
--------------------

>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> 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's friends:

SELECT friend_emailID from USER_FRIEND

WHERE user_emailID="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> wrote in message
news:425b1d300810080856m202f5d5bq572a48fd393c63a9@ style="color:#666">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> 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=1
>>
>>
>
>
> --
> 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=1

 


Attachment: [text/html]
Attachment: [text/html]
Attachment: [text/html]
Thread
Foreign KeysBen A.H.8 Oct
  • Re: Foreign KeysJim Lyons8 Oct
    • Re: Foreign KeysPerrin Harkins8 Oct
  • Re: Foreign KeysBen A.H.8 Oct
    • Re: Foreign KeysArthur Fuller8 Oct
      • RE: Foreign KeysBen A. Hilleli8 Oct
        • Re: Foreign KeysPeter Brawley8 Oct