List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:August 5 1999 10:56am
Subject:Re: DB design question
View as plain text  
adhir@stripped wrote:
> 
> Hey all - a question for the DB experts out there:
> 
> Say I have a table of records with primary key id (an auto inc field).
> 
> I need to be able to relate records within the table to one another as peers, so
> if entry id 4 is related to entry id 7, the converse should also be true.
> 
> One way to do this is create a table 'relationships' with two fields, id1 and
> id2.  When I want to establish a relationship between entries 4 and 7, I can
> say:
> 
>         insert into relationships set id1=4,id2=7
> 
> Now if I want to get all entries that id 7 is related to, I need to
> 
>         select id1,id2 from relationships where id1=7 or id2=7
> 
> and discard the return value containing id=7 - maybe by doing
> 
>         select if(id1=7,id2,id1) from relationships where id1=7 or id2=7
> 
> This is sort of hokey though.
> 
> So another thing to do is along with the insert statement above, always insert
> the converse, so I would have done:
> 
>         insert into relationships set id1=4,id2=7
>         insert into relationships set id1=7,id2=4
> 
> Now to get all entries that 7 is related to, I simply say:
> 
>         select id2 from relationships where id1=7
> 
> Problem solved.  But its not clean...  Can anyone suggest a data structure which
> would do this "cleanly"?
> 
> Thanks...
> 
> Al

Hi Al

Why don't you use your first relationships table and issue two SELECTs on it to first get
the ones with id1=7 and then get with the secong SELECT the other ones?

Your table structure seems OK.

Tschau
Christian

Thread
DB design questionadhir5 Aug
  • Re: DB design questionChristian Mack5 Aug