Re: Bidirectional Binary Self-Joins

          Windows Live™ HotmailBeta

Kevin Kirkpatrick
kk2796_at_hotmail.com
Sign out Mail Web

Inbox

Junk
Drafts
Sent

Deleted
Keepers
Manage folders
Today
Mail
Contacts
Calendar

         New
Reply
Reply all
Forward
Delete
Junk

Options
Test message‎
From: KIRKPATRICK, KEVIN (ATTSI) (kk2796_at_att.com) Sent:
Fri 3/30/07 5:23 PM
To:
kk2796_at_hotmail.com On Mar 30, 1:19 pm, "JOG" <j..._at_cs.nott.ac.uk> wrote:
> 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.

Okay, first, it is a valid constraint that a given team will only play one game per day, so for Game: {Date, Team1, Team2}, both {Date, Team1}
and {Date, Team2} are keys. Savor that - there are phenominally few hard rules in this area of discourse...

To address Marshall directly, in discourse, people might talk about "the
Calvin game on Dec-12", about "the Hope game on Dec-12", the "Hope- Calvin
game on Dec-12", or "the Calvin-Hope game on Dec-12". In no discourse (at least, none that my client would be concerned with) would
a game be referred to via a surrogate game# or GameID (which is the number one reason I'd like to avoid introducing such a thing into the design).

>
>
>
> > > 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- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
Received on Sat Mar 31 2007 - 03:20:19 CEST