Bidirectional Binary Self-Joins
From: <mlefkon_at_yahoo.com>
Date: 29 Mar 2007 02:15:14 -0700
Message-ID: <1175159714.015259.222600_at_b75g2000hsg.googlegroups.com>
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:
person_id ## id of one person in a pair of friends Received on Thu Mar 29 2007 - 11:15:14 CEST
Date: 29 Mar 2007 02:15:14 -0700
Message-ID: <1175159714.015259.222600_at_b75g2000hsg.googlegroups.com>
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
person_id ## id of one person in a pair of friends Received on Thu Mar 29 2007 - 11:15:14 CEST