473,703 Members | 3,300 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Contact Management DB: Handling "Friends"?

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
Nov 13 '05 #1
6 2121
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!
Nov 13 '05 #2
(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.
Nov 13 '05 #3
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
automaticall y 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.
Nov 13 '05 #4
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.Perso nID AS Person, tblFriend.Frien dID AS Friend
FROM tblFriend UNION ALL SELECT tblFriend.Frien dID AS Person,
tblFriend.Perso nID 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?

Nov 13 '05 #5
I've done this with

tblPeople
PeopleID
First
Last
etc.

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

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

My main frmPeople has a subform frmsubRelations hips - 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 "IsRelatedT o" 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?
Nov 13 '05 #6
RE/
SELECT tblFriend.Perso nID AS Person, tblFriend.Frien dID AS Friend
FROM tblFriend UNION ALL SELECT tblFriend.Frien dID AS Person,
tblFriend.Pers onID 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.ControlSo urce 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_F riend1_AppendNe w
- qryPersonSave_F riend2_UpdateEx isting
- qryPersonSave_F riend3_Identify Deleted
- qryPersonSave_F riend4_RemoveDe leted
There's a little more processing with deleted recs.
This comes in the form of another work table that I didn't mention yet:

------------------------
ttblFriendDelet eTracker:
PersonFriendID
IsDeleted
------------------------

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

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

After that's done, I run a Delete query that joins ttblPersonFrien dDeleteTracker
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
Nov 13 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

93
3958
by: jacob navia | last post by:
In this group there is a bunch of people that call themselves 'regulars' that insist in something called "portability". Portability for them means the least common denominator. Write your code so that it will compile in all old and broken compilers, preferably in such a fashion that it can be moved with no effort from the embedded system in the coffe machine to the 64 bit processor in your desktop.
7
9586
by: seven.reeds | last post by:
Hi, I have a caontainer div that is 80% of the width of my display and 40em high. It will hold from 2 to 10 columns of data and will almost always have more than 40ems of lines... so it will scroll. All of this is working. Each column has a heading that, as the scrollbar is moved, scrolls away just like the regular detail info. So I am now asked to have the column headings "float" above the scrolling grid so that when the scrollbar...
1
2516
Coldfire
by: Coldfire | last post by:
Hello I need a HTML script for Emailing the link, with description, of my site to any email address for-example in the case of "Share this Page with Friends". plz help
2
1265
by: JohnQ | last post by:
Yeah, I'm a lil burnt out and need to go elsewhere. I'll go do that. I know how bad it gets when I mix livelihood.. no, I didn't mean that! I was harkening back to when I worked as a wage slave and fell in love. It was good love then. Well and now too. But, I was not free. I was taking "the giant leap of faith". I think the love would have been good. I'm not going to characterize a past love (read, a real woman, geeks (!), not a...
84
8567
by: aarklon | last post by:
Hi all, I found an interesting article here:- http://en.wikipedia.org/wiki/Criticism_of_the_C_programming_language well what do you guys think of this article....??? Is it constructive criticism that needs to be appreciated always...???
1
2288
FLEB
by: FLEB | last post by:
I'm working on a PHP/MySQL site with social networking and "message blast" functionality, and I'm not sure what the best method to represent multiple levels of interpersonal relationships in the database would be. What I'd like to happen is this: A user posts a message to their page, with a "blast radius" option. The system then sends the message that many levels outward from the original person: A radius of 1 would go to their friends, a...
0
955
by: delhi institute of management & services | last post by:
Delhi Institute of Management & Services Dear friends, We are extremely happy to welcome you to the world of Management... We are in the process of preparing some 5 minutes revision Q & A type lessons for management students. They are in no way, a replacement for the classroom lectures, textbooks or any other study guides. If you wish to go through these lessons. Please visit today's BLOGS:
0
8759
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
8670
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9252
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8966
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5922
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4687
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3125
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2455
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2070
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.