Re: Bidirectional Binary Self-Joins

On Mar 30, 5:56 pm, "Marshall" <marshall.spi..._at_gmail.com> wrote:
> On Mar 30, 8:23 am, "JOG" <j..._at_cs.nott.ac.uk> wrote:
>
> > On Mar 30, 3:44 pm, "Marshall" <marshall.spi..._at_gmail.com> wrote:
>
> > > Idle thought: if you had RVAs, you could do something like:
>
> > > { date=12-Dec, teamscores={(team=Hope, score=59), (team=Calvin,
> > > score=32)}}
>
> > > Marshall
>
> > I hate the idea of having to invent a surrogate key to identify a
> > game, just so that we can model it in RM (especially given it is
> > perfectly identifiable from the date and teams).
>
> The key of course depends on the requirements, which we're
> just making up as we go along. I would expect there would
> need to be a game id even without the RM, because otherwise
> how can we talk about it? Suppose two teams play each other
> twice in the same day? "Hey, did you see that game where
> Hope played Calvin? Awesome." "You mean game ten?"
> "No, the morning game, game nine."

I think this is broadly a red herring. I'd assumed one game per day, but this is easily fixed by a date and a time say (unix timestamp me up). A game can be identified as being unique in the real world without it having an ID, so for the sake of a more challenging example lets assume it doesn't.

>
> > Hence using RVA's
> > certainly seems preferable. But then we've added seemingly unnecessary
> > complexity to our queries compared to:
> > { (date:12-dec, team: Hope, team:Calvin) }
> > { (date:12-dec, team: Hope, score 59), (date:12-dec, team: Hope, score
> > 32) }
> > Someone fix my thinking. quick.
>
> Which queries?

Well in my schema to find a set of teams who have scored more than 40 points in any game this season I could ask: "SELECT team FROM scores WHERE score > 40". But with a nested relation I must be looking at something more verbose - now I'm sadly not versed in using RVA's enough to know what the standard SQL syntax for that would be (assuming there is any), but if you do know then I would find that particularly interesting.

>
> Perhaps the part of your thinking that needs fixing is the part
> where you see this as a problem?

Aye, can never rule that out.

>
> Another idle thought. Given my earlier proposal:
>
> > > { date=12-Dec, teamscores={(team=Hope, score=59),
> > > (team=Calvin, score=32)}}
>
> You were saying we could uniquely determine a game
> by the date and the two teams. Okay, why can't we
> just do that in the above? The primary key would
> be the pair (date, projection of teamscores over team)

Again an interesting idea, that I'd like to see explored more. My initital concern is just that we are producing a structure that isn't a simple as the unnested version, and my query-bias radar is starting to twitch. But hey, that's all wooliness on my part.

What isn't so wooly is that if the RVA version is our only relation, storing the whole informational content and utilizing one of these newfangled  projected keys, what happens prior to the game when there are no scores? nulls? In the unnested version we can list the fixture in the matches relation, and just omit the results in the scores relation until it is played. Not so with the RVA's.

>
> I really wish we'd talk about nested relations more; I want
> to investigate these sorts of questions.

Agreed.

>
> Marshall
Received on Fri Mar 30 2007 - 20:19:01 CEST