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
.

Relevant Pages

  • Re: "Arrows" pads?
    ... If the arc occurs, the energy released locally is minimized. ... terminals of the circuit that normally do not experience high stress, ... attempts at design (if it was a serious design effort and not just a ... accumulation of savvy for the next attempt (if the developer lasts ...
    (sci.electronics.design)
  • Re: NYB
    ... If you have some idea what (like how many "spikes" per arc, ... you can design one on EQ and you can print it out to any size you need. ... Which books should I buy? ...
    (rec.crafts.textiles.quilting)
  • Re: This argument surprised me
    ... An engineered structure of Noah's arc that might actually float. ... The problem, as I understand it, is not to design an Ark that is buoyant, but to design one that will not break apart. ...
    (talk.origins)
  • Re: OT: Why welfare doesnt work!
    ... I have never reported anyone to the FBI, ... He mentioned things from the 'Model T' era of IC design. ... connector on the high voltage connection to the cathode. ... adjust the temperature. ...
    (sci.electronics.design)
  • Re: DR Lag-Site Configuration
    ... >> trying to figure out a few things about this kind of DR design. ... >> 1)Do I have to us a firewall site to site dedicated VPN tunnel or can I ... There is no need if this is a dedicated connection such as a T1. ... > remote site is not well connected you may be better off promoting the site ...
    (microsoft.public.windows.server.active_directory)