Solved

Normalizing Tables....

Posted on 2004-04-26
11
250 Views
Last Modified: 2010-04-05
<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
Comment
Question by:shaneholmes
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 12

Assisted Solution

by:esoftbg
esoftbg earned 50 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

by:esoftbg
ID: 10922738
excuse me:

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

Expert Comment

by:mokule
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

by:shaneholmes
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

by:shaneholmes
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 17

Expert Comment

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

Author Comment

by:shaneholmes
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

by:shaneholmes
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

by:
kretzschmar earned 50 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

by:kretzschmar
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

by:esoftbg
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now