Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: Bidirectional Binary Self-Joins

Re: Bidirectional Binary Self-Joins

From: JOG <>
Date: 29 Mar 2007 16:32:26 -0700
Message-ID: <>

On Mar 29, 6:05 pm, "Aloha Kakuikanu" <> wrote:
> On Mar 29, 2:15 am, 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.

I actually think this raises an issue which is often glossed over - the situation where there is a relationship with no clear antecedent involved (which obviously tend to be symmetric). Within a friendship I have two equally valid components - yet I have to distinguish an antecedent and a consequent for an "is friends with" predicate. I could enter two tuples to indicate the symmetry of this relationship, but should that be necessary? Would it not be possible within the constraints of good data modelling to have a relationship (as codd defined it and not as per the scurrulous definitions of E/R) that has two attributes both of which are "friends" (yes repetition of attribute names). And if so how would this impact on our manipulation of such a structure - negatively or beneficially?

Apologies in advance for wanton mulling off the top of my head. Received on Thu Mar 29 2007 - 18:32:26 CDT

Original text of this message