Social Network Analysis (SNA) - designing the basics in Access

[Ion]

Dear AccessForums Users

I am trying to design the fundamental structure of a database for social network analysis. The basic components consist of the following (I am not sure how many should be tables, queries, etc, so I use the term "component"):

1. One component should have the names of all the people in the network. I assume this should be a table.
2. Another component should record the relations between them. I am not sure whether this should be a table or a query.

I illustrate my dilemma below with some abstract "names":

Names of people in social network:
A
B
C
D
E
F
G

Relations between the people:
A likes B


B likes C
D like G
G likes F
G likes A

Note that the "likes" are mutual, so that if "A likes B", there is no need to record that "B likes A" too.

In the format of an Access table, the relations listed above should be listed as records in two columns, as follows:
A B
B C
D G
G F
G A

From the sample records of relations above, you will note that A, B and G occur in both columns. I need this flexibility when inputting the relations records.
I will use the database to find all the likes of a particular name. For example, if I ask for all of A's likes, I want to get a list as follows:
B
G
But, as you can see from the relations, above, B and G occur in different columns when liked by A. So Access needs to scan both columns in order to find all of A's likes, and then list them as one column.

So, how do I program my names, relations, and the search for any one name's likes.

Any assistance would be most welcomed.

Ion

PS Incidentally, there are plenty of SNA softwares available, but nothing on programming Access for SNA. I do not want to be the prisoner of some prefabricated black box. I want the whole thing done in Access where the programming may be transparent and easily evaluated.

[Micron]

You would make it easier on yourself if you could accept reciprocal likes since A might like B but does that automatically mean that B likes A? Otherwise you are faced with some difficulty in allowing an A B combination in a table but not a B A. If allowed, you could have something like tblNames with all the related data

MemberID	Fname	Lname	UsrName
1	Dave	Brown	Funky
2	Frank	Smith	Spanky
3	Jill	Jones	Aura
4	Jane	Doe	Misty
5	etc	etc	etc
6	etc	etc	etc

The ID field values could be stored in tblLikes. Substituting your letter values with these numbers:

LikeID	Like1	Like2
1	1	2
2	2	3
3	4	6
4	6	1

Combine fields Like1 and Like2 into a unique index (or composite primary key) so that you don't repeat, say, a 2-3 combination. I would prevent repeating values such as a 2-2 combination in the form code that is used to populate tblLikes. Since you want to put the same value from different records into one field, I see a need for a Union query. Your query would look something like

SELECT tblLikes.Like2 AS Liking
FROM tblMembers INNER JOIN tblLikes ON tblMembers.MemberID = tblLikes.Like1
WHERE (((tblLikes.Like1)=1))
UNION
SELECT tblLikes.Like1 AS Liking
FROM tblMembers INNER JOIN tblLikes ON tblMembers.MemberID = tblLikes.Like1
WHERE (((tblLikes.Like2)=1));

If you want to see other fields such as names, add them to the individual query parts.