Solved

Normalizing Tables....

Posted on 2004-04-26
11
256 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 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
Independent Software Vendors: 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 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

Industry Leaders: 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!

Question has a verified solution.

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

Suggested Solutions

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

749 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