Record to Record relationships with mult incidents.

[jdapd]

First, thanks for looking at this post.

I am trying to document the relationship (interpersonal / not neccesarily DB
relation) of 1400 people to each other. One table within the database
contains the data for each of these people in individual records.


Obviously, It would be rediculous to have a table with 1400 fields and as
many records.

I want to accomplish 2 things. The first is to show the relationship of each
of these people to eachother. I want to be able to show degrees of separation
between any of the 1400 people. The second is to do it with as little
replication of input and data as possible.

I have a separate table to define the relationships. The types of
relationships I have are:

Acquaintance
Aunt / Uncle / Niece / Nephew
Boss / Employee
Child / Parent
Client / Vendor
Cousin
CoWorker
Dating / Affair
Ex-Dating
Ex-Married
Friend
In Law
Neighbor
Room-mate
Same Person
Sibling
Spouse
Student / Teacher / Instructor
Undetermined

I tried to make the relationship type apply in both directions to hopefully
only define the relationship once for both persons. (sibling instead of 1.
brother 2. sister or student teacher instead of 1. teacher and 2. student.)
Field 1 pulls from the list of people, Field 2 pulls from the relationship
table, field 3 pulls from the same source as field 1.

I think I am on the right track, but Im not sure where to go from here.

 

[John Vinson]

On Tue, 7 Dec 2004 07:59:04 -0800, "jdapd"

Answers inline.

First, thanks for looking at this post.

I am trying to document the relationship (interpersonal / not neccesarily DB
relation) of 1400 people to each other. One table within the database
contains the data for each of these people in individual records.


Obviously, It would be rediculous to have a table with 1400 fields and as
many records.

I want to accomplish 2 things. The first is to show the relationship of each
of these people to eachother. I want to be able to show degrees of separation
between any of the 1400 people. The second is to do it with as little
replication of input and data as possible.

Good thinking.

I have a separate table to define the relationships. The types of
relationships I have are:

I tried to make the relationship type apply in both directions to hopefully
only define the relationship once for both persons. (sibling instead of 1.
brother 2. sister or student teacher instead of 1. teacher and 2. student.)
Field 1 pulls from the list of people, Field 2 pulls from the relationship
table, field 3 pulls from the same source as field 1.

I think I am on the right track, but Im not sure where to go from here.

You need just one more table: Relationships.

Relationships
Person1ID
Person2ID
Relationship

This would be linked to two instances of the table of people by the
two PersonID fields. This structure does have directionality (so, if
you decide you do want to keep directional relationships such as
teacher-student, you can). To get bidirctionality you can use a
self-UNION query:

SELECT Person1, Person2, Relationship FROM Relationships
UNION ALL
SELECT Person2, Person1, Relationship FROM Relationships


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps