## M:N "friend" relationship - Data Modelling Problem

### M:N "friend" relationship - Data Modelling Problem

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

### M:N "friend" relationship - Data Modelling Problem

>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

One possible solution would be to have your application insert two rows
into the friends table each time a pair is identified.  So when Tom
becomes friends with Betty, you would insert both the Tom-Betty and the
Betty-Tom rows into the relationship table.  Of course, care must be
taken to document this so that when Tom and Betty have a fight and are
no longer friends, both relationship rows must be found and deleted.

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

### M:N "friend" relationship - Data Modelling Problem

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

Build a VIEW that provides the reflection of the columns in Friends, and to
avoid duplicates, ensure that Friend1 is always 'smaller' than Friend2.
I'm not sure if MS Access supports either of these concepts.
(Union and triggers are probably needed to make it automatic)
--

### M:N "friend" relationship - Data Modelling Problem

>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

One of the joys of discovering to what depth Access's relational model
has been taken is to find that "self joins" work.  I had a similar
need in a development way back in Access 1.1 days, where the
PrimaryKey of the "CardHolder" table had to be 1:n joined to a
ForeignKey in the same table to show "sponsor" for this credit card
system.  It works.  Just apply the normal rules of INNER and OUTER
joins and you'll be rewarded with a working system.

Cheers.

### M:N "friend" relationship - Data Modelling Problem

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

--

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!

### M:N "friend" relationship - Data Modelling Problem

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.

People table:

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

### M:N "friend" relationship - Data Modelling Problem

> Good idea - but alas, I'm working with software that doesn't really
> support "views".  Access queries aren't too bad of an imitation of
> views, but I have found SQL UNION queries to be vvveeerrryyy ssllooww.

The speed shouldn't be much of an issue is you UNION the two results (i.e.
Joe is a friend of whom, and who is a friend of Joe) rather than selecting
from the union.
--

### M:N "friend" relationship - Data Modelling Problem

Distribution:

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

### M:N "friend" relationship - Data Modelling Problem

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

You have hit a limitation of the relational model.  The first report of this,
in writing, of which I am aware, came in the late 1970's in the book,
"Data and Reality," by William Kent.  Any implementation you choose,
under any basically relational approach, will be a compromise.

Mark

### M:N "friend" relationship - Data Modelling Problem

> > Good idea - but alas, I'm working with software that doesn't really
> > support "views".  Access queries aren't too bad of an imitation of
> > views, but I have found SQL UNION queries to be vvveeerrryyy ssllooww.
> The speed shouldn't be much of an issue is you UNION the two results (i.e.
> Joe is a friend of whom, and who is a friend of Joe) rather than selecting
> from the union.
> --

Use UNION ALL instead of just UNION. Otherwise, Access only
returns unique rows from the result, which slows things down
dramatically.

--

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!

### M:N "friend" relationship - Data Modelling Problem

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

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

>You have hit a limitation of the relational model.  The first report
of this,
>in writing, of which I am aware, came in the late 1970's in the book,
>"Data and Reality," by William Kent.  Any implementation you choose,
>under any basically relational approach, will be a compromise.

>Mark

### M:N "friend" relationship - Data Modelling Problem

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

Actually, several other people pointed this out.  The only hangup with this
is that it is very important to make sure that whenever the database is
updated this double-row correspondance is maintained; the program must be
very careful to always insert/delete/modify the corresponding row.

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

### M:N "friend" relationship - Data Modelling Problem

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

The problem is that this table has lots of duplicate information: this will
lead no only to wasted space (not a terrible problem) but also to the
possibility of inconsistencies.

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

Due to the up coming closure of my Lab I have been reading a lot
of job ads. One of the frequent buz words is "Data Modelling".
What is the definition?

--
o|     Time to dust off     Don Berryman
--\__   the Telemark Skies,  Defence Research Establishent Pacific
|     and think SNOW!!     FMO CFB Esquimalt
/\                         Victoria, BC, CANADA, V0S 1B0
//\\    /\                  (604) 363-2731 Voice
////\\  //\\  /\             (604) 363-2856 Fax

They pay me to do oceanography, but I'd rather do glaciology.
At least my ocean is partially frozen.