Very Computer

I'm trying to model a somewhat unusual relationship.  It is a "friend"
relationship where rows of a table are related to other rows of the
same table.  It is a M:N relationship where the same table appears on
both sides; it is like a bill-of-materials type relationship (table
related to itself), except M:N instead of 1:N.

Can anyone think of a convenient way to model this?  I'm trying to
avoid giving "preference" to one side of the relationship over
another.  Here's a sample way to model it that has this problem:

Table: People
Name
----
Joe
Bill
Anne
Betty

Table: Friends {holds the is-a-friend-of relationship)
Friend1 Friend2
------- -------
Joe     Bill
Joe     Anne
Betty   Joe

Note that to get a list of all of Joe's friends I need two queries -
one in case he is in the first column, one in case he is in the
second.  Although the actual problem involves machine parts, this
sample problem is functionally identical.

Obviously, I'm trying to do this under a relational model - the actual
implementation will be used in MS Access, with the data probably
stored in DBF files of some flavor.

Thanks for any help anyone can provide.

-Kyle

--
Phil Yandel              | "Sometimes the most proactive thing we can do
Senior Analyst - ComEd   |  is to smile.  Happiness, like unhappiness,

(312)-394-8211/(312)-394-8573 Fax

Cheers.

The basic model looks about as good as you're going to get with
an RDBMS. Maybe you could represent each is-a-friend-of
relationship with two records in the Friends table, one for A is
a friend of B, and the other for B is a friends of A. Otherwise,
can you do a union query? Note that the recordset returned by a
union query won't be updatable. An example:

parameters [FindFriendsOf] text;
select friend1 as Friend from friends where friend2 = [FindFriendsOf]
union
select friend2 as Friend from friends where friend1 = [FindFriendsOf];

If you need an updatable recordset, this should work:

parameters [FindFriendsOf] text;
select people.* from people where name in
(select friend1 from friends where friend2 = [FindFriendsOf])
or name in
(select friend2 from friends where friend1 = [FindFriendsOf]);

Quote:> Hi all...
> I'm trying to model a somewhat unusual relationship.  It is a "friend"
> relationship where rows of a table are related to other rows of the
> same table.  It is a M:N relationship where the same table appears on
> both sides; it is like a bill-of-materials type relationship (table
> related to itself), except M:N instead of 1:N.
> Can anyone think of a convenient way to model this?  I'm trying to
> avoid giving "preference" to one side of the relationship over
> another.  Here's a sample way to model it that has this problem:
> Table: People
> Name
> ----
> Joe
> Bill
> Anne
> Betty
> Table: Friends {holds the is-a-friend-of relationship)
> Friend1    Friend2
> -------    -------
> Joe        Bill
> Joe        Anne
> Betty      Joe
> Note that to get a list of all of Joe's friends I need two queries -
> one in case he is in the first column, one in case he is in the
> second.  Although the actual problem involves machine parts, this
> sample problem is functionally identical.
> Obviously, I'm trying to do this under a relational model - the actual
> implementation will be used in MS Access, with the data probably
> stored in DBF files of some flavor.
> Thanks for any help anyone can provide.
> -Kyle

Quote:

>Hi all...

>I'm trying to model a somewhat unusual relationship.  It is a "friend"
>relationship where rows of a table are related to other rows of the
>same table.  It is a M:N relationship where the same table appears on
>both sides; it is like a bill-of-materials type relationship (table
>related to itself), except M:N instead of 1:N.
>...
>Table: People
>Name
>----
>Joe
>Bill
>Anne
>Betty

>Table: Friends {holds the is-a-friend-of relationship)
>Friend1 Friend2
>------- -------
>Joe     Bill
>Joe     Anne
>Betty   Joe

>Note that to get a list of all of Joe's friends I need two queries -
>one in case he is in the first column, one in case he is in the
>second.  Although the actual problem involves machine parts, this
>sample problem is functionally identical.

Name
----
Joe
Bill
Anne
Betty

Friendship table:

Friendship_ID
-------------
1
2
3

Friendship_Membership table:

Friendship_ID   Name
-------------   ----
1               Bill
1               Joe
2               Anne
2               Joe
3               Betty
3               Joe

Tom Jamieson

your off.  you can name a table person, have primary key be an id
ln, fn, mi fields, new table is called friend.  relationship is 2 one to many rel.from person to friend.

hope that helps

: Hi all...

: I'm trying to model a somewhat unusual relationship.  It is a "friend"
: relationship where rows of a table are related to other rows of the
: same table.  It is a M:N relationship where the same table appears on
: both sides; it is like a bill-of-materials type relationship (table
: related to itself), except M:N instead of 1:N.

: Can anyone think of a convenient way to model this?  I'm trying to
: avoid giving "preference" to one side of the relationship over
: another.  Here's a sample way to model it that has this problem:

: Table: People
: Name
: ----
: Joe
: Bill
: Anne
: Betty

: Table: Friends {holds the is-a-friend-of relationship)
: Friend1       Friend2
: -------       -------
: Joe   Bill
: Joe   Anne
: Betty Joe

: Note that to get a list of all of Joe's friends I need two queries -
: one in case he is in the first column, one in case he is in the
: second.  Although the actual problem involves machine parts, this
: sample problem is functionally identical.

: Obviously, I'm trying to do this under a relational model - the actual
: implementation will be used in MS Access, with the data probably
: stored in DBF files of some flavor.

: Thanks for any help anyone can provide.

: -Kyle

Quote:>Hi all...

>I'm trying to model a somewhat unusual relationship.  It is a "friend"
>relationship where rows of a table are related to other rows of the
>same table.  It is a M:N relationship where the same table appears on
>both sides; it is like a bill-of-materials type relationship (table
>related to itself), except M:N instead of 1:N.

>Can anyone think of a convenient way to model this?  I'm trying to
>avoid giving "preference" to one side of the relationship over
>another.  Here's a sample way to model it that has this problem:

>Table: People
>Name
>----
>Joe
>Bill
>Anne
>Betty

>Table: Friends {holds the is-a-friend-of relationship)
>Friend1     Friend2
>-------     -------
>Joe Bill
>Joe Anne
>Betty       Joe

>Note that to get a list of all of Joe's friends I need two queries -
>one in case he is in the first column, one in case he is in the
>second.  Although the actual problem involves machine parts, this
>sample problem is functionally identical.

>Obviously, I'm trying to do this under a relational model ...

Mark

--

WARNING: I cannot be held responsible for the above        They're   coming  to
because  my cats have  apparently  learned to type.        take me away, ha ha!

The Friends table can be described this way:  The table consists of a
composite primary key where each element of the key is (optionally) a
foreign key referencing the key of the People table.  The information
it
is describing is:  For each person on the People table, a row exists
for
EACH friend of that person.  So...is Joe a friend of Betty?  Yes - so
there should be a row on the table describing that relationship (and
there is).  Ok then...Is Betty a friend of Joe?  Sure - but where's the
row?  Obviously, there's a bug in the program that loads the table!  In
fact we can see that there are (at least) three missing rows on the
table.  The properly loaded table should look like this:

Friend1  Friend2
-------  -------
Joe      Bill
Joe      Anne
Joe      Betty
Betty    Joe
Bill     Joe
Anne     Joe

Now a single query will return the required information.

Quote:>I've read a lot of solutions to this problem, but nobody seems to have
>noticed the obvious.  There's nothing wrong with Kyle's data model and
>he certainly hasn't uncovered a "limitation of the relational model".
>It's just a matter of missing records on his table.
>The Friends table can be described this way:  The table consists of a
>composite primary key where each element of the key is (optionally) a
>foreign key referencing the key of the People table.  The information

This means that none of the manipulation could be left to the user to do
with Access-controlled forms; all of the updates would have to be done in
code, encased in transactions.

With the caveat in mind, this is probably the solution I will use.  I do a
have little bit of data that has to be stored in each "side" of the
relationship, and this method provides and obvious place to put such data.

=Kyle

-Kyle

This is because the implicit assumption is that "friendship" is a symetric
relation, i.e., if I regard you as my friend you regard me as your friend.
Wish that were true in real life. :-)

Anyway, if you want to express a symetric relation, you either duplicate
information as above or you create a base table with incomplete data and
create a view with the same two queries proposed originally.  This view can
be regarded as a very simple deductive system in which data is derived from
explicit data and the symetry rule for friendship.

--
Pablo Straub
Assistant Professor of Computer Science
Catholic University of Chile, Vicuna Mackenna 4860 (143), Santiago 22, Chile