Contact Management DB: Handling "Friends"?

[(Pete Cresswell)]

I'm makeing a little DB to help manage high school class reunions.

One feature I'm trying to implement is "Friends". e.g. Fred Smith is a friend
of Joe Jones and Bill Anderson. We record that so that when Fred moves and
our mailing bounces, we can call Joe and/or Bill in hopes of getting Fred's new
address.

I started implementing this as just tblFriend linked to tblPerson on PersonID
plus another link to tblPerson on FriendID.

But now I realize that the data is far from Normal. Seems like when I add Joe
Jones as a 'Friend' of Fred Smith, the app should know that the relationship
goes both ways: i.e. Joe shows up on Fred's screen (as expected...) but Fred now
automatically shows up on Joe's screen.

Anybody been here?
--
PeteCresswell

[Rich P]

Greetings,

I have been there. My fix may be a bit of a kludge but it goes like
this

say that
Fred's ID = A123
Bill's ID = B234
Joe's ID = C345
Sue's ID = D456

tblPeople
MainPersonID, Friend1, Friend2, Friend3, Friend4
A123 B234 C345 D456
B234 A123
C345 A123 D456
D456 A123 C345

Normalized Version of tblPeople
MainPersonID, FriendID
A123 B234
A123 C345
B234 A123
C345 A123
C345 D456
D456 A123
D456 C345

The kludge is that there is no table containing just friend ID's. These
have to be manually associated as in the 2nd tblPeople. I'm sure there
is a way to dynamically associate the ID's. I just haven't come up with
that way. But this is how I handled a similar situation.

HTH

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Salad]

(Pete Cresswell) wrote:

I'm makeing a little DB to help manage high school class reunions.

One feature I'm trying to implement is "Friends". e.g. Fred Smith is a friend
of Joe Jones and Bill Anderson. We record that so that when Fred moves and
our mailing bounces, we can call Joe and/or Bill in hopes of getting Fred's new
address.

I started implementing this as just tblFriend linked to tblPerson on PersonID
plus another link to tblPerson on FriendID.

But now I realize that the data is far from Normal. Seems like when I add Joe
Jones as a 'Friend' of Fred Smith, the app should know that the relationship
goes both ways: i.e. Joe shows up on Fred's screen (as expected...) but Fred now
automatically shows up on Joe's screen.

Anybody been here?

No. But you could create a query. Add the Classmates table and Friends
twice. You should now have Friends and Friends_1. Drag a link from
Classmate to Friends on the PersonID on Friends. Then drag a link from
Classmate to FriendID on Friends_1.

Set the relationship to select All records from Classmates and only
matching records from Friends and Friends_1. (dbl=click on relationship
line)

You would want then want to modify the criteria so that you select the
Person from the Classmate file.

You would get the ID from Friends1 where the ID in Friends is not null.
In the next criteria line (OR) , where ID in Friend_1 is not null.

Or...you could modify this so that you UNION it between 2 queries.
Select all records with PersonID matches PersonID and PersonID matches
FriendID.

[Steve Jorgensen]

On Wed, 03 Nov 2004 16:02:29 GMT, "(Pete Cresswell)" <x@y.z> wrote:

I'm makeing a little DB to help manage high school class reunions.

One feature I'm trying to implement is "Friends". e.g. Fred Smith is a friend
of Joe Jones and Bill Anderson. We record that so that when Fred moves and
our mailing bounces, we can call Joe and/or Bill in hopes of getting Fred's new
address.

I started implementing this as just tblFriend linked to tblPerson on PersonID
plus another link to tblPerson on FriendID.

But now I realize that the data is far from Normal. Seems like when I add Joe
Jones as a 'Friend' of Fred Smith, the app should know that the relationship
goes both ways: i.e. Joe shows up on Fred's screen (as expected...) but Fred now
automatically shows up on Joe's screen.

Anybody been here?

I think the best answer is just to create 2 friendship records, one pointing
each way. If you need cascading delete, just set both relationships from
people to friends as cascade delete, and deleting either person, will then
delete both of the friend relations between that person and each of his/her
friends.

[Tom Mitchell]

Pete:

A half baked idea for you to flesh out...

Assuming two tables

tblPerson
PersonID - pk
PersonName
etc

tblFriend
RelID - pk
PersonID - fk
FriendID - fk

create the following union query:

SELECT tblFriend.PersonID AS Person, tblFriend.FriendID AS Friend
FROM tblFriend UNION ALL SELECT tblFriend.FriendID AS Person,
tblFriend.PersonID AS Friend
FROM tblFriend;

that should get you a list of Person to Friend and the reverse
relationship you are looking for.

Now the half baked part...

I assume you will make a form to show the person data, with a subform
showing friends. The subform will use the union query as the record
source. You will have to manage adding and deleting relationships via
popups since you can't do that directly in the union query. Shouldn't
be a big deal but I'm sure there will be gotchas in there somewhere.

I'd be interested in the gotchas you run across if you use it. If you
don't I'd like to hear why you rejected the approach.

good luck

Tom

"(Pete Cresswell)" <x@y.z> wrote in message news:<91********************************@4ax.com>. ..

I'm makeing a little DB to help manage high school class reunions.

One feature I'm trying to implement is "Friends". e.g. Fred Smith is a friend
of Joe Jones and Bill Anderson. We record that so that when Fred moves and
our mailing bounces, we can call Joe and/or Bill in hopes of getting Fred's new
address.

I started implementing this as just tblFriend linked to tblPerson on PersonID
plus another link to tblPerson on FriendID.

But now I realize that the data is far from Normal. Seems like when I add Joe
Jones as a 'Friend' of Fred Smith, the app should know that the relationship
goes both ways: i.e. Joe shows up on Fred's screen (as expected...) but Fred now
automatically shows up on Joe's screen.

Anybody been here?

[Penguin]

I've done this with

tblPeople
PeopleID
First
Last
etc.

tblRelationshipTypes
TypeID
Type (e.g. Friend, brother, employer, etc.)

Then I can record all types of relationships in
tblRelationships
PeopleID
IsRelatedTo (another PeopleID)
IsRelatedAs (use a TYPEID)
also a comment

My main frmPeople has a subform frmsubRelationships - and does a
lookup to Phone for the person also.
This does not do the union mentioned in a previous post, and that's
actually one thing I've been thinking about adding.

First, however, I'd like to solve the problem of adding a new person.
Since my control for "IsRelatedTo" is a dropdown, Not in List will
call a small form just for adding a person, address & phone.
Unfortunately, it often crashes the database. The only reason I can
think of is that frmPeople has tblPeople open already. When I call me
small frmAddPeople, I'm opening another copy of tblPeople. Should I
be opening a RecordsetClone? Will that let me save a new record? Or
is there a way to close the table before closing the pop-up?

[(Pete Cresswell)]

RE/

SELECT tblFriend.PersonID AS Person, tblFriend.FriendID AS Friend
FROM tblFriend UNION ALL SELECT tblFriend.FriendID AS Person,
tblFriend.PersonID AS Friend
FROM tblFriend;

that should get you a list of Person to Friend and the reverse
relationship you are looking for.

Now the half baked part...

I assume you will make a form to show the person data, with a subform
showing friends. The subform will use the union query as the record
source. You will have to manage adding and deleting relationships via
popups since you can't do that directly in the union query. Shouldn't
be a big deal but I'm sure there will be gotchas in there somewhere.

I'd be interested in the gotchas you run across if you use it. If you
don't I'd like to hear why you rejected the approach.

Done. Works as advertised.

-----------------------
tblPersonFriend:
PersonFriendID (PK)
PersonID_A
PersonID_B
Comments
-----------------------
The union query returns tblFriend recs where PersonID_A
matches the currently-selected person and where PersonID_B matches same.

I feed the Union query to an Append query that loads that info into a work
table, putting PersonID_A or ..._B into the work table's PersonID as appropriate
and putting the other into FriendID. The subform's combo box's.ControlSource is
FriendID and it's .RowSource is a list of people by PersonID, excluding
whoever's record is currently being edited.

------------------
ttblFriend:
PersonFriendID
PersonID
FriendID
Comments
------------------

When Save time comes around, I run four queries:

- qryPersonSave_Friend1_AppendNew
- qryPersonSave_Friend2_UpdateExisting
- qryPersonSave_Friend3_IdentifyDeleted
- qryPersonSave_Friend4_RemoveDeleted
There's a little more processing with deleted recs.
This comes in the form of another work table that I didn't mention yet:

------------------------
ttblFriendDeleteTracker:
PersonFriendID
IsDeleted
------------------------

Right after I load ttblFriend, I load ttblFriendDeleteTracker
by copying info from the just-loaded ttblFriend.

Then, at Save time, I run an Update query that joins ttblPersonFriend to
ttblPersonFriendDeleteTracker on PersonFriendID and sets IsDeleted=True for recs
that have disappeared from ttblPersonFriend.

After that's done, I run a Delete query that joins ttblPersonFriendDeleteTracker
to tblPersonFriend on PersonFriendID and deletes tblPersonFriend recs where
IsDeleted=True.
Sounds like a lot of code, but it's pretty simple and executes in a flash.

Thanks.
--
PeteCresswell