Bidirectional Binary Self-Joins

Hi-
I've been banging my head over how to represent this theoretical example: in a group of people, trying to keep track of who is friends with whom. I'm assuming that if person A is friends with person B, then the reverse is also true. However if B is friends with C, then A is not necessarily friends with C. What is the best way to represent this to ensure best integrity and at the same time make the structure simple and efficient to query. Here is my best go at is:

TABLE 1 - people (PrimaryKey: person_id) person_id
person_name

TABLE 2 - friendships (PrimaryKey: friendship_id, pair_id, secondary unique key: friendship_id, person_id )
friendship_id ## this id will start at 1 and increment for each 'pair' of associated recs added (so will be the same number for two records)
pair_id ## restricted value: 1, 2--> purpose is to only allow two values to be stored for each 'friendship pair'. Keeps database structure intact.
person_id ## id of one person in a pair of friends Received on Thu Mar 29 2007 - 04:15:14 CDT