Re: Bidirectional Binary Self-Joins
Date: 30 Mar 2007 05:16:24 -0700
Message-ID: <1175256984.344299.77790_at_l77g2000hsb.googlegroups.com>
On Mar 30, 3:41 am, "Kevin Kirkpatrick" <kvnkrkpt..._at_gmail.com> wrote:
>[snip]
> Finally, JOG - I toyed around with the notion of:
> Game {Date, Team, Score, Team, Score}
> suffice to say, that way leads madness....
I would never propose such a schema, so I hope you are not suggesting I did - with no connection between Score and Team other than attribute order (which is totally against the principles of RM), such a structure is useless. , I always find it easiest to go back to the propositions you want to record. If every proposition was of the nature:
"There was a game on Date [Dec-12], where the Home team [Hope] scored
[59] and the Away Team [Calvin] scored [32]"
Then a schema of ( Date, Home_team, Home_score, Away_team, Away_score)
is sufficient. However if there was no such distinction between home
and away teams (perhaps you are recording finals played at neutral
grounds), then your proposition is more like:
"On Date [Dec-12], [Hope] played [Calvin], with [Hope] scoring [59]
and [Calvin] scoring [32]"
The conjunction in that sentence sets alarm bells off in my head,
because it means I could normalize this proposition to:
Then I realize I have two different types of propositions, and hence
two different types of relations. This corresponds to your design for
1a, but it highlights the process of how I would have started to get
there. It also shows that you do not need some sort of artificial ID
key - the date and the team are enough to serve as identity.
Now, what is bugging me however is that my first relation would have
to have attributes team1 and team2, which is entirely unsatisfactory.
RM prohibits the repetition of attribute names, even when the
attributes are playing identical, equal roles in the relationship. I
could solve this by adding a surrogate, to represent the game as a
whole, and then normalizing, but this seems worryingly artificial,
given in RL a game is identified by the teams and the date. Any
illumination from cdt is more than welcome.
"On Date [Dec-12], [Hope] played [Calvin]"
"On Date [Dec-12], [Hope] scored [59]"
"On Date [Dec-12], [Calvin] scored [32]"