Re: Bidirectional Binary Self-Joins


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

.

Relevant Pages

  • Re: BALLY Lamp Driver Board TEST AID Final Product Done!
    ... electronics engineering degree for nothing. ... The funny thing is that in the 80's when I was in college, ... I would like to design some "cool" electronic mods for pins as well ... If Jeff's testing units are as good as the artowrk he used to repro, ...
    (rec.games.pinball)
  • Re: Layer 3 switch model?
    ... I'm a college student & my final year project is to upgrade & ... reconfigure my college network... ... Cisco have some good design docs that explain general principles as well as ... The design may get cheaper if you only use layer 3 in some locations - maybe ...
    (comp.dcom.lans.ethernet)
  • Re: If Stern dies and how pinball will be revived.....
    ... William's biggest expenses was the many design team's salaries and royalties ... if their game was built. ... competition to design a game and the one picked and built, give the college ...
    (rec.games.pinball)
  • Re: Newby question
    ... > I am new to pcb design etc, I want to design my own pcbs and either make ... Find what software your college uses and see if they have an arrangement ... you could buy a low-cost package like Easy-PC ...
    (sci.electronics.cad)
  • Re: OT:kerry
    ... And no doubt every single one of these friends had their college education ... the armed services are overwhelmingly filled with people ...
    (comp.sys.hp.mpe)