Re: Bidirectional Binary Self-Joins

On 03/30/07 14:41, Kevin Kirkpatrick wrote: [...]
> 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 >

Ah, you beat me to it! :^) Your design 1a was essentially what I was going to suggest. The home/away cases are an interesting twist, though, introducing some asymmetry into the relationship.

Fun problem, though. I remember having a similar situation (people and connections between them) and realised that I didn't want a directional relationship, and I didn't want to have to store each pair twice (as (A,B) and (B,A)).

I figured I was really modelling an undirected graph (i.e. a network structure), which could be stored as the following relations:

Node {NodeID}
Arc {ArcID}
Connection {ArcID, NodeID}

Applying this to the OP's scenario, Node would be Person, and Arc would be the friendship relationship. Connection simply identifies the endpoints of the arcs. With this design you somehow have to ensure that each arc connects to exactly 2 nodes, which seemed like an unusual sort of constraint, but more elegant overall than the other approaches I'd considered.

Hmm, how about this: suppose you could define a domain for an unordered pair of node IDs. You could then do away with the Connection relation, and store the nodes that an arc connects in the Arc relation. An arc's node set could be its only attribute, i.e. it could be identified by the unordered pair of nodes that it connects (assuming that you would only ever record 1 connection between a given pair of nodes). That is:

Node {NodeID}
Arc {ArcID, Nodes}

Ah, but you now can't enforce referential integrity on Arc.Nodes because the NodeID values are hidden away within the scalar attribute. OK, maybe not a useful idea after all...

--
Chris