[2 days left] Whatâ€™s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

# Normalizing Tables....

Posted on 2004-04-26
Medium Priority
267 Views
<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
0
Question by:shaneholmes
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 3
• 2
• +1

LVL 12

Assisted Solution

esoftbg earned 200 total points
ID: 10922644
Tables
- Teams
- Players
- Matches
-MatchPartners
- Games
- PlayersResults

Table Structure

Teams
- id
- Team

Players
- id
- TeamId
- Player

Matches
- id
- Date

MatchPartners1
- id
- MatchId
- TeamId - Lookup from Teams
- TPoints

MatchPartners2
- id
- MatchId
- TeamId - Lookup from Teams
- TPoints

Games
- Id
- MatchId
- MatchType - {Singles, Doubles}

PlayersResults
- MatchId
- GameId
- TPlayer - A Lookup from Players
- TPoints
0

LVL 12

Expert Comment

ID: 10922738
excuse me:

PlayersResults
- Id
- GameId
- TPlayer - A Lookup from Players
- TPoints
0

LVL 17

Expert Comment

ID: 10922764
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.
0

LVL 11

Author Comment

ID: 10922766

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
0

LVL 11

Author Comment

ID: 10923272
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
0

LVL 17

Expert Comment

ID: 10923568
1. Is this set of games really correct ?
2. round mean one game or a few?
0

LVL 11

Author Comment

ID: 10923618
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

0

LVL 11

Author Comment

ID: 10923625
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
0

LVL 27

Accepted Solution

kretzschmar earned 200 total points
ID: 10925619
well, my try

Games
-------
G_ID
Name
NumPlayers

Teams
-------
T_ID
Name

Players
-------
P_ID
T_ID
Name

Match
--------
M_ID
G_ID
Date

MatchDetails
------------
MD_ID
M_ID
P_ID
Points

meikl ;-)
0

LVL 27

Expert Comment

ID: 10925647
if it is needed to know, which player plays against

MatchCombi
------------
MC_ID
M_ID
P1_ID
P2_ID

meikl ;-)
0

LVL 12

Expert Comment

ID: 10928248
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....
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code Â  (CODE) The Imâ€¦