social network analysis

[PhillipM]

Is it possible to construct a database in access that would keep track of
complex (many-to-many) social relationships? I've been puzzling over
relationships for a while but can't see how it can be done. Am I missing
something?

What access won't seem to do is this:
There is a primary table with a list of people.
In each record there is a multiple-entry field for inserting links to other
records in the same table.

So, for example, John, Mary, Bob, and Fran are in the database. John is
friends with Mary and Bob but not Fran. I would like to be able to insert
links on John's record to Mary and Bob. Similarly, Mary is friends with
John, Bob and Fran and I would like to have a field containing links to all
three other records. The idea is to have a database that can examine and
manuplate social network information: i.e., X is Y's spouse , friends with A,
B, & C, and works with P & Q. Is there some way of doing this in Access or
do I need a different product?

 

[Steve Schapel]

Phillip,

I am not sure whether this is the best way, but here's an idea... You
will need a table to define the relationships. It will require 3
fields, RelationshipFrom, RelationshipTo, and RelationshipType. The
RelationshipFrom and RelationshipTo fields will both contain linked data
to the primary key field of the main People table, let's say this is
PersonID. This will allow for non-reciprocal relationships. In other
words, if A is B's workmate, then B will presumably always be A's
workmate, but if C thinks D is his friend, doesn't automatically mean
that D will regard C as his friend. So, for example, if the PersonIDs
of John, Mary, Bob, and Fran are 1, 2, 3, and 4, and let's suppose John
and Fran are workmates, and Fran also regards Bob as a friend, and Mary
and Bob are married, the data in the Relationships table might look like
this...

1 4 workmate
4 1 workmate
4 1 friend
2 3 husband
3 2 wife

On the basis of this, you should be able to do the analyses you need.
As for data entry, if you have a form based on the Relationships table,
which you place as a subform on the main People form, you will be able
to enter as many relationship definitions with as many other people as
required, for any given individual. You could use VBA code on this
subform's AfterUpdate event to automatically enter the "mirror"
relationship where applicable, e.g. if you are on John's record and
enter a workmate relationship with Fran, the code will append a record
for Fran's workmate relationshp with John.

 

[John Nurick]

Hi Phillip,

The general idea is to have one table that has one record per person.
Let's call it

tblPersons
PersonID*
FirstName
LastName
etc.

(* indicates a field is, or is part of the primary key).

Then have a table called something like

tblRelationshipTypes
RelationshipType*

with records such as
IsFriendOf
IsBosomPalOf
IsLoverOf
IsMarriedTo
WorksWith
WorksFor

and then a third table

tblRelationships
FirstPerson* - foreign key into tblPersons
SecondPerson* - FK into tblPersons
RelationshipType* - FK into tblRelationshipTypes

So the fact that Mary (PersonID = 24) is friends with John (PersonID =
18) and Bob (PersonID = 31) would be stored as the following records in
tblRelationships:
24, 18, "IsFriendOf"
24, 31, "IsFriendOf"

That's the general idea. With additional fields here and there you can
track other data such as when did a relationship begin or end. With a
structure like this, when you want to find out who Mary's friends you
just use a query along these lines to pull out every friendship in which
Mary figures:

SELECT tblPersons.*
FROM tblPersons INNER JOIN tblRelationships
ON tblPersons.PersonID = tblRelationships.FirstPerson
WHERE (tblRelationships.SecondPerson = 24)
AND (tblRelationships.RelationshipType = "IsFriendOf")
UNION
SELECT tblPersons.*
FROM tblPersons INNER JOIN tblRelationships
ON tblPersons.PersonID = tblRelationships.SecondPerson
WHERE (tblRelationships.FirstPerson = 24)
AND (tblRelationships.RelationshipType = "IsFriendOf")
ORDER BY tblPersons.LastName, tblPersonsFirstName


But there are complications you'll need to think through before you
start creating tables.

For instance, the example above shows that Bob and Paul are both friends
of Mary but it says nothing about whether and how well they know each
other. Maybe that's what you want, and you'll use a third record
18, 31, IsFriendOf
to store the relationship between Bob and Paul. But maybe you need to
model some situations as single "relationships" involving groups of
people rather than as a series of two-person "relationships". In that
case the structure gets a little more complicated, with something like
this:

tblPersons (as above)

tblRelationshipTypes
RelationshipType*

e.g.
Friendship
Marriage
WorkGroup
HobbyGroup

tblRelationships
RelationshipID*
RelationshipDescription
RelationshipType - FK into tblRelationshipTypes

tblRelationshipMembers
RelationshipID* - FK into tblRelationships
PersonID* - FK into tblPersons

Here, the friendship(s) between Mary, Bob and Paul would be stored as
one record in tblRelationships, e.g.
22,"Friendships centered on Mary","Friendship"
and three more in tblRelationshipMembers
22,24
22,18
22,31

And so on. Access is a relational database management system which can
do virtually anything in the way of querying and reporting on data
*provided* it can be put into a rigid relational structure like those
above. This presents obvious, sometimes severe, challenges if you need
to model inherently flexible fuzzy things like human relationships: if
you're not careful you find you've chosen a structure that can't handle
the real-world situation you need to model.

So if you don't need full flexibility for quantitative analysis of the
data, it's worth considering a different sort of database system: one
that is better at handling loosely structured data. Anthropologists,
sociologists and ethologists face this problem, so it should be worth
investigating software developed by or for them.