?
Solved

Normalizing Tables....

Posted on 2004-04-26
11
Medium Priority
?
264 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
[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
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 12

Assisted Solution

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

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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 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

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

762 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