March 30, 2005 at 8:15 am
I'm working on keeping track of "teams" of "players". Each team consists of exactly two players. For example:
Teams:
TeamNum Points
1 10
2 20
3 30
Players:
playerId Name Team
1 Bob 1
2 Sam 1
3 Jack 2
4 Hal 2
5 Ralph 3
6 Jerry 3
Now, what I need is a query that will return both players in one row; like so:
Team Player1 Player2 Points
1 Bob Sam 10
2 Jack Hal 20
3 Ralph Jerry 30
I can't figure out a clean way to do this. Is by db design wrong? I thought about adding "player1" and "player2" columns to the teams table, but that's a pretty serious normalization violation.
.
March 30, 2005 at 9:13 am
The design isn't "wrong", as long as some assumptions hold true. eg: Can a player be on more than 1 team ? Assumption is No.
Given that:
Select dt.TeamNum, p1.Name As Player1Name, p2.Name As Player2Name, dt.Points
From
(
Select t.TeamNum, t.Points, Min(PlayerID) As Player1, Max(PlayerID) As Player2
From Players As p
Inner Join Teams As t
On (t.TeamNum = p.Team)
Group By t.TeamNum, t.Points
) dt
Inner Join Players As p1
On (p1.Team = dt.TeamNum And
p1.PlayerId = dt.Player1)
Inner Join Players As p2
On (p2.Team = dt.TeamNum And
p2.PlayerId = dt.Player2)
March 30, 2005 at 10:53 am
What makes you say that adding a Player1 and player2 columns to the teams table is a serious normalization violation? If a team can have 2 and only 2 players, then the players can accurately be said to be attributes of team. Both player1 and player2 are wholly dependent on the team.
You probably are thinking that having player1 and player2 violates the rule against "repeating groups", but that term is not used in the formal definition of the rules of normalization. It has come into popular use through an over-simplification of those rules in the "normalization for dummies" type of books and articles. Now, if you had a single column called "players" and in it you stored the values "1,2", that would be a violation, and that kind of violation is what was intended by the term "repeating group". Even that definition is inadequate since the relational model makes no prohibitions against using an array of values (or even a relation valued attribute) as a domain despite the considerable problems that tend to arise from thier use, but that is another story.
The only problem with the "player1, player2" design is the physical issue of how to enforce the rule that a player can only appear once in either the player1 or player2 column. Obviously a unique constraint on either column (or both) won't do the trick, so you would have to define a trigger to enforce that rule.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply