shaneholmes
asked on
Normalizing Tables....
<SMILE>
I am writing a small app for a friend who plays Billiards (8-Ball Only)
And they each match conists of a number of games (Singles & DOubles play).
Singles = Player from Team 1 -vs- Player from Team 2
Doubles = Player1 & Player 2 from Team 1 -vs- Player1 & Player 2 from Team 2
I have a Access Database with the following:
Tables
- Teams
- Players
- Matches
- Games
Table Structure
Teams
- id
- Team
Players
- id
- TeamId
- Player
Matches
- id
- Date
- Team1 - Lookup from Teams
- T1Points
- Team2 - Lookup from Teams
- T2Points
Games
- Id
- MatchId
- MatchType - {Singles, Doubles}
- T1Player1 - Team 1 Player 1 - A Lookup from Players
- T1Points1
- T1Player2 - Team 1 Player 2 - A Lookup from Players
- T1Points2
- T2Player1 - Team 2 Player 1 - A Lookup from Players
- T2Points1
- T2Player2 - Team 2 Player 2 - A Lookup from Players
- T2Points2
Question:
How can I normalize the Game table more? Is it possible?
Maybe a Singles & Doubles Game Tables
Here is what i am thinking? Any Suggestions?
Shane
Games
_________________
- Id
- MatchId
- MatchType
Singles
__________________________
- Id
- GameId
- Player1
- Points1
- Player2
- Points2
Doubles
_________________________
- Id
- GameId
- T1Player1 - Team 1 Player 1 - A Lookup from Players
- T1Points1
- T1Player2 - Team 1 Player 2 - A Lookup from Players
- T1Points2
- T2Player1 - Team 2 Player 1 - A Lookup from Players
- T2Points1
- T2Player2 - Team 2 Player 2 - A Lookup from Players
- T2Points2
Thanks!
Shane
I am writing a small app for a friend who plays Billiards (8-Ball Only)
And they each match conists of a number of games (Singles & DOubles play).
Singles = Player from Team 1 -vs- Player from Team 2
Doubles = Player1 & Player 2 from Team 1 -vs- Player1 & Player 2 from Team 2
I have a Access Database with the following:
Tables
- Teams
- Players
- Matches
- Games
Table Structure
Teams
- id
- Team
Players
- id
- TeamId
- Player
Matches
- id
- Date
- Team1 - Lookup from Teams
- T1Points
- Team2 - Lookup from Teams
- T2Points
Games
- Id
- MatchId
- MatchType - {Singles, Doubles}
- T1Player1 - Team 1 Player 1 - A Lookup from Players
- T1Points1
- T1Player2 - Team 1 Player 2 - A Lookup from Players
- T1Points2
- T2Player1 - Team 2 Player 1 - A Lookup from Players
- T2Points1
- T2Player2 - Team 2 Player 2 - A Lookup from Players
- T2Points2
Question:
How can I normalize the Game table more? Is it possible?
Maybe a Singles & Doubles Game Tables
Here is what i am thinking? Any Suggestions?
Shane
Games
_________________
- Id
- MatchId
- MatchType
Singles
__________________________
- Id
- GameId
- Player1
- Points1
- Player2
- Points2
Doubles
_________________________
- Id
- GameId
- T1Player1 - Team 1 Player 1 - A Lookup from Players
- T1Points1
- T1Player2 - Team 1 Player 2 - A Lookup from Players
- T1Points2
- T2Player1 - Team 2 Player 1 - A Lookup from Players
- T2Points1
- T2Player2 - Team 2 Player 2 - A Lookup from Players
- T2Points2
Thanks!
Shane
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I would go just in opposite direction
Matches
- id
- Date
- Player1Team1
- Player2Team1
- Player1Team2
- Player2Team2
- PointsPlayer1Team1Against1
- PointsPlayer2Team1Against2
- PointsPlayer1Team2Against1
- PointsPlayer2Team2Against2
- PointsPlayer1Team1Against2
- PointsPlayer2Team1Against1
- PointsPlayer1Team2Against2
- PointsPlayer2Team2Against1
- PointsDoubleTeam1
- PointsDoubleTeam2
On condition that there are 4 singles and 1 double in a match it is normalized.
Matches
- id
- Date
- Player1Team1
- Player2Team1
- Player1Team2
- Player2Team2
- PointsPlayer1Team1Against1
- PointsPlayer2Team1Against2
- PointsPlayer1Team2Against1
- PointsPlayer2Team2Against2
- PointsPlayer1Team1Against2
- PointsPlayer2Team1Against1
- PointsPlayer1Team2Against2
- PointsPlayer2Team2Against1
- PointsDoubleTeam1
- PointsDoubleTeam2
On condition that there are 4 singles and 1 double in a match it is normalized.
ASKER
esoftbg, i like where youa re going with this... i think your on the right track,
although, i dont understand where these come into play:?
MatchPartners1
- id
- MatchId
- TeamId - Lookup from Teams
- TPoints
MatchPartners2
- id
- MatchId
- TeamId - Lookup from Teams
- TPoints
Especially with in the Games, or Player results tables
Shane
ASKER
Sorry, I guess I should have stated:
each match is as follows:
One round Singles (Player 1 of Team 1 -VS- Player 2 of Team 2)
One round Double (Player 1 & Player 2 of Team 1 -VS- Player 1 & Player 2 of Team 2)
One round Singles (Player 1 of Team 1 -VS- Player 2 of Team 2)
Shane
each match is as follows:
One round Singles (Player 1 of Team 1 -VS- Player 2 of Team 2)
One round Double (Player 1 & Player 2 of Team 1 -VS- Player 1 & Player 2 of Team 2)
One round Singles (Player 1 of Team 1 -VS- Player 2 of Team 2)
Shane
1. Is this set of games really correct ?
2. round mean one game or a few?
2. round mean one game or a few?
ASKER
Sorry, i guess it is kinda confusing...
each match = A Team against another team
and they play single games & double games
a singles game being - a player from team 1 plays against a player from team 2 (one game of 8 ball)
a doubles game being - two players from team 1 play two players from team2 (one game of b-ball)
I believe everone plays one single game, then they play doubles, then they all play another single game.
Regardless, of what order, just know that there could be a singles game or a doubles game
When I was normalizing my tables, I didn't like the looks of what i had, and knew i was going to have troubles when it came to performing my SQL's.
Thanks for looking Mokule!
Shane
each match = A Team against another team
and they play single games & double games
a singles game being - a player from team 1 plays against a player from team 2 (one game of 8 ball)
a doubles game being - two players from team 1 play two players from team2 (one game of b-ball)
I believe everone plays one single game, then they play doubles, then they all play another single game.
Regardless, of what order, just know that there could be a singles game or a doubles game
When I was normalizing my tables, I didn't like the looks of what i had, and knew i was going to have troubles when it came to performing my SQL's.
Thanks for looking Mokule!
Shane
ASKER
Mokule, i have another question about BitBtns as well, im realy stumped on, if you could help me. Then you could come back to this one... Im in a bind on the BitBtns problem.... Thanks!
Shane
Shane
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
if it is needed to know, which player plays against
MatchCombi
------------
MC_ID
M_ID
P1_ID
P2_ID
meikl ;-)
MatchCombi
------------
MC_ID
M_ID
P1_ID
P2_ID
meikl ;-)
I wrote above last night after midnight. May be it is the reason regarding some mistakes. My todays opinion is:
Tables
- Teams
- Players
- Matches
- MatchPartners1
- MatchPartners2
- Games
- PlayersResults
Table Structure
Teams
- id
- Team
- TeamSummaryPoints
Players
- id
- TeamId
- PlayerId
Matches
- id
- Date
MatchPartners1
- id
- MatchId
- TeamId - Lookup from Teams
- PlayerId - Lookup from Players of selected Team: for this Match patners could be Player 3 and Player 4 from Team 2
MatchPartners2
- id
- MatchId
- TeamId - Lookup from Teams
- PlayerId - Lookup from Players of selected Team: for this Match patners could be Player 1 and Player 3 from Team 5
Games
- Id
- MatchId
// - MatchType - {it is not needed field because the partners are selected: if 1 player = Singles, if 2 players = Doubles}
PlayersResults
- Id
- GameId
- PlayerId - A Lookup from Players
- PlayerPoints
Something as in real sports life: Today Player 3 plays for Team 1, but tomorrow he / she is transferred into another team and plays for it. The points for the Players and the points for the Teams are independent....
Tables
- Teams
- Players
- Matches
- MatchPartners1
- MatchPartners2
- Games
- PlayersResults
Table Structure
Teams
- id
- Team
- TeamSummaryPoints
Players
- id
- TeamId
- PlayerId
Matches
- id
- Date
MatchPartners1
- id
- MatchId
- TeamId - Lookup from Teams
- PlayerId - Lookup from Players of selected Team: for this Match patners could be Player 3 and Player 4 from Team 2
MatchPartners2
- id
- MatchId
- TeamId - Lookup from Teams
- PlayerId - Lookup from Players of selected Team: for this Match patners could be Player 1 and Player 3 from Team 5
Games
- Id
- MatchId
// - MatchType - {it is not needed field because the partners are selected: if 1 player = Singles, if 2 players = Doubles}
PlayersResults
- Id
- GameId
- PlayerId - A Lookup from Players
- PlayerPoints
Something as in real sports life: Today Player 3 plays for Team 1, but tomorrow he / she is transferred into another team and plays for it. The points for the Players and the points for the Teams are independent....
PlayersResults
- Id
- GameId
- TPlayer - A Lookup from Players
- TPoints