Table Design Stumbling Block

[MasterRacker]

I'm beginning design of a league management package. I may be getting senile, but I seem to be having difficulty with my "Games" table.

In simplified form, I need to track 2 players, what each scored and who won for each game. (There's more to it obviously, but this will illustrate my mental block.) I've come up with two scenarios for table design:

Option A:
GameID, Player1ID, Player1Scored, Player2ID, Player2Scored, WinnerID

Option B
GameID, PlayerID, OpponentID, PlayerScored, PlayerAllowed, PlayerWon

Now, lets look at the simple scenario of trying to find out Jeff's total points scored for the season:

In Option A, my query has to look for JeffsID=Player1ID OR JeffsID=Player2ID and then total either of the respective scored columns. To me, that seems messy.

In Option B, the queries are straightforward, but each game ends up appearing twice. Ex. One entry with me as the player and also a "mirrored" entry with my opponent as the player and me as the opponent.

Because I'm having this issue at all, I suspect I don't have my data normalized completely, but I don't know what else to do here. Any suggestions?

_____
Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[Golom]

Maybe it's even simpler
[tt]
tblGame
GameID
PlayerID
Score
[/tt]

Select A.GameID
     , A.PlayerID
     , A.Score As [A Score]
     , B.PlayerID
     , B.Score As [B Score],
     , (Select X.Player 
        From tblGame X 
        Where X.GameID = A.GameID
              X.Score = (Select MAX(Score) From tblGame
                         Where GameID = X.Game)) As [Winner]
       
From tblGame A INNER JOIN tblGame B
     ON A.PlayerID > B.PlayerID

 

[Golom]

PS. You will of course, need to ensure that you insert exactly two records for each game.

 

[Golom]

Sorry ... missed a bit

Select A.GameID   As [Game]
     , A.PlayerID As [Player]
     , A.Score    As [Player Score]
     , B.PlayerID As [Opponent]
     , B.Score    As [Opponent Score]
     , (Select X.Player 
        From tblGame X 
        Where X.GameID = A.GameID
              X.Score = (Select MAX(Score) From tblGame
                         Where GameID = X.GameID)) As [Winner]
       
From tblGame A INNER JOIN tblGame B
     ON A.GameID = B.GameID AND A.PlayerID > B.PlayerID

Your "Total Points Scored" query would be

Select PlayerID, SUM(Score) As [Total Points]
From tblGame
Group By PlayerID

 

[MasterRacker]

So you're using a variation of Option B - where it takes two rows to store all the relevant info for a "game"? Am I reading your variation correctly in that GameID is repeated and the PK is therefore a compound of GameID+PlayerID?

_____
Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[MasterRacker]

I should add that there is more game info I'm storing (this is pool) like innings, who racked, etc. I already have a higher level "Matches" table. The way you've trimmed the game info, I would have to get who the two players are from that. I'll have to ponder whether that is a good place to store the other info as well.

_____
Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[Zathras]

I would be inclined to go with a version of option A:

Option C
[tt]
Games
-----
GameID,
WinnerID,
LoserID,
WinnerScore,
LoserScore
[/tt]
Then create a view:
[tt]
CREATE VIEW GameView(
GameID,
PlayerID,
PlayerScore,
Winner)
AS
SELECT GameID,WinnerID,WinnerScore,1 FROM Games
UNION
SELECT GameID,LoserID,LoserScore,0 FROM Games
[/tt]

For total score:
[tt]
SELECT PlayerID,
SUM(PlayerScore) as TotalScore,
SUM(Winner) as GamesWon,
COUNT(DISTINCT GameID) as GamesPlayed
FROM GameView
GROUP BY PlayerID
[/tt]

 

[lespaul]

I should add that there is more game info I'm storing (this is pool) like innings, who racked, etc. I already have a higher level "Matches" table. The way you've trimmed the game info, I would have to get who the two players are from that.

you should store information ONCE. If you already have the playerID in the Matches there is no need to store the players again somewhere else, in fact it breaks normalization rules.

what's the difference between matches and games? What information are you storing about the MATCH? Are you storing any of that SAME information about the GAME?

read the fundamentals document linked below.....


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[MasterRacker]

I have a previous version of this app where the DB was done by another person. They stored each game twice similar to my Option B. This is one of the things I'm trying to correct. After giving it some more thought, it gets even more complicated. The issue is still how to get the games atomic however.

In general terms, things are structured like this.
There are multiple leagues. A League plays on a specific day of the week for a set number of weeks. Leagues are independent of each other and but given player can play on more than one league. Ex. I could play a Sunday league and a Wednesday league.

A "Match" is played between two teams of 5 players. Different leagues can have different match structures. There are round-robin leagues where each player plays each player on the opposing team once and match-play leagues where each player plays only one player from the opponent, but plays multiple games against them in a "set".

Somewhere, I will need to track the specific sequence the games were played in so I can regenerate a score sheet correcting data entry errors and also for calculating winning streaks (awards are given for longest win streak).

The "Game" itself is common across all formats. The items I need to track per game are: HomePlayer, HomeScore, HomeSafeties, VisitorPlayer, VisitorScore, VisitorSafeties, Winner (in some formats, it's possible to have a tie score but still have a "winner"), Innings, Breaker.

I designated the players "home" and "away" in this example because I need toknow that explicitly somehow. I can't rely on my team rosters in all cases since we have some leagues where "open" substituting is allowed (A player who's on a roster can play for another team if they're short a player - there are also "floating" subs who are not regulars on any given team but just sub randomly for whoever needs one.

I want to be able to report statistics for teams, and players for their teams withing a league, etc. All the normal stuff you would expect. I also want to be able to report on a Player's lifetime totals across leagues or PlayerA vs PlayerB in the last 5 years, or any other wierd stat a person could think of, (similar to baseball).

Golom's idea leads me to think about splitting a "game" into header info and a collection of "Scores". I hadn't thought of that before.

Just brainstorming here...

_____
Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[lespaul]

alright! that's great information.....now, have you had a chance to read the fundamentals document too?

So you should start out by defining your tables based on your description above.

You have LEAGUES, PLAYERS, MATCHES, GAMES...you are also going to need some tables like Game_Players - this is where you would track which players played which games. Maybe one like Match_Games that tracks which games took place at which match...You could also have a League_Players table to track many players joining many Leagues (see the "relationship" between Leagues and players?)

Now you need to look at your existing data and ask yourself "Is this about the LEAGUE, the PLAYER, the MATCH or the GAME?" and decide what table the attribute (field name) belongs to.




Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[MasterRacker]

Leslie, I had run across the fundamentals document a while ago and read it. The SQL joins link is a good one also. I'm aware of the basics of normalization and the concept of single-instance data.

Most of the data normalizes out in a fairly straightforward way. The matches and games stuff has been is sticking point and much less intuitive than other projects I've worked on. I'm probably carrying too much of the older flawed design in my head.

This discussion has already pointed my in a couple of new directions. I'll have to let this fester in my head for a while and see what floats to the top.



_____
Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[lespaul]

I call that "percolating"!!

Good luck!

Leslie