Re: Bidirectional Binary Self-Joins

From: Kevin Kirkpatrick <kvnkrkptrck_at_gmail.com>
Date: 29 Mar 2007 19:41:14 -0700
Message-ID: <1175222474.421845.48920_at_p77g2000hsh.googlegroups.com>

JOG wrote:
> On Mar 29, 6:05 pm, "Aloha Kakuikanu" <aloha.kakuik..._at_yahoo.com>
> wrote:
> > 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.
>
> 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.

Actually, I ran into this trying to design a "for-fun" college basketball tracking database for my father. I never settled on a design because my "client" wound up taking the season off (ironic timing of this OP: it just came to my attention that my client is gearing up for this upcoming season)

Anyway, at design time last year, I was torn between two competing designs (with multiple variants):

---

Design 1: use a surrogate GameID to enforce symmetry

---

Design 1a:
Game {GameID, Date}
<1, Dec-12>
<2, Dec-19>
GameEntrant {GameID, Team, HomeAwayNeutral, Points}

<1, Hope College, H, 59>
<1, Calvin College, A, 32>
<2, Hope College, N, 83 >
<2, Calvin College, N, 12 >

------------------------------------------------------------------

Design 1b:
Game {GameID, Date}
<1, Dec-12>
<2, Dec-19>

HostedGame {GameID, HomeTeam, HomePoints, AwayTeam, AwayPoints} <1, Hope College, 59, Calvin College, 32>

NeturalGameEntrant {GameID, Team, Points} <2, Hope College, 83 >
<2, Calvin College, 12 >

---

Design 2: Do not use surrogate GameID (basically the same designs as the OP)

---

Design 2a (one row per game, coin-toss on which is listed first):

Game {Date, Team1, HomeAwayNeutral1, Points1, Team2, Points2, HomeAwayNeutral2}
<Dec-12, Hope College, 59, H, Calvin College, 32, A> <Dec-19, Calvin College, 12, N, Hope College, 83, N>

---

Design 2b (Same as 2a, two rows per game):

Game {Date, Team1, HomeAwayNeutral1, Points1, Team2,

Points2,HomeAwayNeutral2}
<Dec-12, Hope College, 59, H, Calvin College, 32, A>
<Dec-12, Calvin College, 32, A, Hope College, 59 , H>
<Dec-19, Hope College, 83, N, Calvin College, 12, N>
<Dec-19, Calvin College, 12, N, Hope College, 83, N >

------------------------------------------------------------------

Design 2c (same as 2a, no ambiguity):
Game {Date, AlphabeticallyLesserTeam, ALT_Points, ALT_HomeAwayNeutral, AlphabeticallyGreaterTeam, AGT_Points, AGT_HomeAwayNeutral} <Dec-12, Calvin College, 32, A, Hope College, 59 ,H> <Dec-19, Calvin College, 12, N, Hope College, 83 , N>

---

Design 2d (close to 1b):
HostedGame {Date, HomeTeam, HomePoints, AwayTeam, AwayPoints} <Dec-12, Hope College, 59, Calvin College, 32> NeutralGame {Date, ALT_Team, ALT_Points, AGT_Team, AGT_Points} <Dec-19, Calvin College, 12, Hope College, 83>

---

Fortunately, I was "saved by the bell" so to speak, as the project was called off.... However, I was most leaning towards 2c (it most closely resembled my client's data source)... although I kind of liked 2d and 1b for elegantly handling the Home/Away/Neutral.

I'd be keenly interested to hear some feedback on these variations (or others I may have overlooked).

Finally, JOG - I toyed around with the notion of: Game {Date, Team, Score, Team, Score}
suffice to say, that way leads madness.... Received on Fri Mar 30 2007 - 04:41:14 CEST