I'm doing a major overhaul of a league database.
Currently, there is a single record created to reflect a person's win/
loss for the day. I want to be able to keep track of individual games
as opposed to a summary.
There are 5 types of games. 2 types of singles, 2 types of doubles,
and 1 team game. In the doubles, the person will have one of their
team-mates for a partner, not always the same one. For the results
record, I want to keep track of who their opponent is, and if it's a
doubles, keep track of their doubles partner + the two players from
the opposing team. there are 4 people for a legal team, but some
teams may have 5 or more players on the team. A match consists of 17
games, 8 singles, 8 doubles and 1 team game.
I originally had thought of using a single table for the results,then
just have a code to figure out which game was being played. After
thinking about it, I'm thinking it might be best to have 5 tables, one
for each type of game. Is the multiple table for the results
probably the best way to track the results?
Should I have a single record for the game, or should their be one
record for each player involved in the game? I'm leaning towards a
record for each player in a game. So in theory, a doubles game would
create 4 records, one from the perspective of each player. I'm
figuring 1 record per player would make it easier for totaling on the
stats page.
Are my thoughts pretty much 'the way' something like this should
headed, or am I way off base?


Roger

On May 22, 2:00 pm, Roger <roger.in.eug...@gmail.com> wrote:
> I'm doing a major overhaul of a league database.
> Currently, there is a single record created to reflect a person's win/
> loss for the day. I want to be able to keep track of individual games
> as opposed to a summary.
> There are 5 types of games. 2 types of singles, 2 types of doubles,
> and 1 team game. In the doubles, the person will have one of their
> team-mates for a partner, not always the same one. For the results
> record, I want to keep track of who their opponent is, and if it's a
> doubles, keep track of their doubles partner + the two players from
> the opposing team. there are 4 people for a legal team, but some
> teams may have 5 or more players on the team. A match consists of 17
> games, 8 singles, 8 doubles and 1 team game.
> I originally had thought of using a single table for the results,then
> just have a code to figure out which game was being played. After
> thinking about it, I'm thinking it might be best to have 5 tables, one
> for each type of game. Is the multiple table for the results
> probably the best way to track the results?
> Should I have a single record for the game, or should their be one
> record for each player involved in the game? I'm leaning towards a
> record for each player in a game. So in theory, a doubles game would
> create 4 records, one from the perspective of each player. I'm
> figuring 1 record per player would make it easier for totaling on the
> stats page.
> Are my thoughts pretty much 'the way' something like this should
> headed, or am I way off base?
>
> Roger

Wow! Couldn't quite figure out what sport or league this is, but it
sounds pretty intense! Haha! Regardless, if I understand correctly,
this is how I would approach it:

One table would be all the players that are in the league with their
information.
Create table Players {
player_id int(10) not null auto_increment,
player_fname...
player_lname...
.... etc

Another table would be games that were played with the final match
information (***uming always team 1 vs team 2)
Create table Games {
game_id int(10) not null auto_increment,
game_team1_result...
game_team2_result...
.... etc

The final table would be each individual player, the game they played
(tied to games), and the team they were on (tied to games). From this
you can determine their individual stats (how they did), who they
played with (based on the "team" they were on), and who their
opponents were (based on the other "teams" player IDs).
Create table Stats {
stats_id int(10) not null auto_increment,
stats_game_id int(10), --tied to Games table
stats_team int(2), --tied to Games table for actual game result and
tied to Stats for game partner(s)
stats_player_id int(10), -- tied to Players table
.... here would be all the individual stats you want to track
} primary key (stats_id));

I may have over simplified, but based on my understanding of your
goal, this should do the trick. If I misunderstood any parts, let me
know and maybe I can help. Hope at least this gets you started...

Nino