Solved

Normalizing Tables....

Posted on 2004-04-26
11
252 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

896 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

13 Experts available now in Live!

Get 1:1 Help Now