Re: Bidirectional Binary Self-Joins

On Mar 31, 3:37 pm, "Marshall" <marshall.spi..._at_gmail.com> wrote:
> On Mar 31, 2:08 am, mAsterdam <mAster..._at_vrijdag.org> wrote:
>
>
>
> > Marshall wrote:
> > > Bob Badour wrote:
> > >> It would use the equality operation to detect the duplicate. What's more
> > >> disturbing is it would allow the following without complaint:
>
> > >> { date=12-Dec, teamscores={(team=Calvin, score=31), (team=Hope, score=59) }}
>
> > > Whether it complained or not would depend on the keys and other
> > > constraints.
> > > In another subthread I proposed the following key:
>
> > > (date, π_team(teamscores))
>
> > ... allowing (using Bob's tag-notation):
>
> > { date=12-Dec, teamscores={(team=Calvin, score=31),
> > (team=Hope, score=59),
> > (team=Glory, score=71)
> > }
>
> > }
>
> D'oh!
>
> I believe it was earlier stated that each team could play
> only once per day, thus both (12-Dec, Hope) and
> (12-Dec, Calvin) must be unique. This is sufficiently
> annoying with RVAs that I am now inclined to go back
> to the non-RVA model.
>
> (date, team1, team1score, team2, team2score)
> unique(date, team1)
> unique(date, team2)
> check(team1 < team2)
>
> The third constraint's "<" can be any order relation,
> and canonicalizes the tuple type.

This is a good tip - I use this sort of constraint a lot, especially in self-joins to guarantee uniqueness.

>
> Queries like "what games did Hope play in" and
> "what teams scored over 40 points" go back to
> being trivial.

Unfortunately we are left with that same issue - the choice of team1 and team2 is arbitrary, and as David or Paul pointed out, it means I have to do some jiggery pokery on my queries to consider both these different attributes simultaneously, even though they are playing the same roles. This obviously isn't going to kill us, but it doesn't seem elegant, so it niggles.

I would actually add a surrogate identifier for the game if I had to build this db. I'd have a schema for (game_id, team) and two rows representing all the data content for a fixture, because I can regain all that info in single proposition simply with a self-join (or just using a view), but it also allows me to join with other tables without specifying 'team1' or 'team2'. Of course, then I've invented an identifier that the original content didn't have solely to achieve my modeling, which again....well, its not a problem as such, its just one of those Columbo moments, where the little things niggle.

>
> Any my new response to anyone who doesn't like
> the above will be "get over it." :-)

Maybe I need an old raincoat and a cigar. "Just onnne more thing surrr....". Jim.

>
> Marshall
Received on Sat Mar 31 2007 - 22:10:16 CEST