×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Table Design Stumbling Block

Table Design Stumbling Block

Table Design Stumbling Block

(OP)
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
It's never too early to begin preparing for 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

RE: Table Design Stumbling Block

Maybe it's even simpler

tblGame

GameID
PlayerID
Score

CODE

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

RE: Table Design Stumbling Block

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

RE: Table Design Stumbling Block

Sorry ... missed a bit

CODE

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

CODE

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

RE: Table Design Stumbling Block

(OP)
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
It's never too early to begin preparing for 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

RE: Table Design Stumbling Block

(OP)
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
It's never too early to begin preparing for 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

RE: Table Design Stumbling Block


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

Option C

Games
-----
 GameID,
 WinnerID,
 LoserID,
 WinnerScore,
 LoserScore

Then create a view:

CREATE VIEW GameView(
  GameID,
  PlayerID,
  PlayerScore,
  Winner)
AS
  SELECT GameID,WinnerID,WinnerScore,1 FROM Games
  UNION
  SELECT GameID,LoserID,LoserScore,0 FROM Games


For total score:

SELECT PlayerID,
       SUM(PlayerScore) as TotalScore,
       SUM(Winner) as GamesWon,
       COUNT(DISTINCT GameID) as GamesPlayed
 FROM GameView
 GROUP BY PlayerID

RE: Table Design Stumbling Block

Quote:

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 Joins

RE: Table Design Stumbling Block

(OP)
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
It's never too early to begin preparing for 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

RE: Table Design Stumbling Block

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 Joins

RE: Table Design Stumbling Block

(OP)
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
It's never too early to begin preparing for 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

RE: Table Design Stumbling Block

I call that "percolating"!!

Good luck!

Leslie

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close