Re: Bidirectional Binary Self-Joins

On Mar 29, 2:15 am, mlef..._at_yahoo.com wrote:
> 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

Friendship is symmetric but not transitive binary relation. You have 2 options to represent it:
1. "Normalized form". Only one tuple per each relationship. E.g. <person1=A, person2=B> means that A is friend with B and B is friend with A. You can enforce relationship uniqueness by tuple constraint:

 person1 < personB

2. "Symmetrically closed" relation. Two tuples per each relationship. E.g. <person1=A, person2=B> means that A is friend with B, and <person1=B, person2=A> means that B is friend with A. You can enforce symmetric closure with complex constraint. Received on Thu Mar 29 2007 - 19:05:40 CEST