Re: Bidirectional Binary Self-Joins

On Mar 30, 5:56 pm, "Marshall" <marshall.spi...@xxxxxxxxx> wrote:
On Mar 30, 8:23 am, "JOG" <j...@xxxxxxxxxxxxx> wrote:

On Mar 30, 3:44 pm, "Marshall" <marshall.spi...@xxxxxxxxx> 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 new-
fangled 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


.