On May 23, 12:02 pm, "nino9st...@yahoo.com" <nino9st...@yahoo.com>
wrote:

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

It's for a dart league.
Anyway, I have an existing database, a table for their location, table
for teams, table for players. there could be multiple teams per
location. I have it pretty much working, just need to re-figure out
the results part of the thing.
I had planned on using a single table for the results, then have
something like what you have. After I got thinking, it would end up
with NULL values for opponents and partners for the singles matches.
Not to mention having to use codes for the type of games.
To minimize the NULL fields, I could just use different results tables
for singles than I do for doubles. (meaning I won't have to have
partner info as fields since they don't have partners in singles).
that got me thinking of having a table for each type of game
played. I may want to be able to run reports for certain types of
games, could be more efficient in the long run if I break them out.

Roger

Roger wrote:
> On May 23, 12:02 pm, "nino9st...@yahoo.com" <nino9st...@yahoo.com>
> wrote:
>
>> 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));
>>
>
> It's for a dart league.
> Anyway, I have an existing database, a table for their location, table
> for teams, table for players. there could be multiple teams per
> location. I have it pretty much working, just need to re-figure out
> the results part of the thing.
> I had planned on using a single table for the results, then have
> something like what you have. After I got thinking, it would end up
> with NULL values for opponents and partners for the singles matches.
> Not to mention having to use codes for the type of games.
> To minimize the NULL fields, I could just use different results tables
> for singles than I do for doubles. (meaning I won't have to have
> partner info as fields since they don't have partners in singles).
> that got me thinking of having a table for each type of game
> played. I may want to be able to run reports for certain types of
> games, could be more efficient in the long run if I break them out.
>
> Roger
>

Or you could just have a table (match_players) with matchid and
playerid. Add as may entries as you need for however many players there
are in the match.

You'll probably need to also keep track of which team someone is on in
the match (i.e. home/visitor, for lack of a better description). A
simple third column.

A matches table keeps track of the individual matches, and the
match_players tracks the number of players, whether there are two or two
hundred.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
EMAIL REMOVED
==================

On May 25, 5:18 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> Roger wrote:
> > On May 23, 12:02 pm, "nino9st...@yahoo.com" <nino9st...@yahoo.com>
> > wrote:
>
> >> 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));
>
> > It's for a dart league.
> > Anyway, I have an existing database, a table for their location, table
> > for teams, table for players. there could be multiple teams per
> > location. I have it pretty much working, just need to re-figure out
> > the results part of the thing.
> > I had planned on using a single table for the results, then have
> > something like what you have. After I got thinking, it would end up
> > with NULL values for opponents and partners for the singles matches.
> > Not to mention having to use codes for the type of games.
> > To minimize the NULL fields, I could just use different results tables
> > for singles than I do for doubles. (meaning I won't have to have
> > partner info as fields since they don't have partners in singles).
> > that got me thinking of having a table for each type of game
> > played. I may want to be able to run reports for certain types of
> > games, could be more efficient in the long run if I break them out.
>
> > Roger
>
> Or you could just have a table (match_players) with matchid and
> playerid. Add as may entries as you need for however many players there
> are in the match.
>
> You'll probably need to also keep track of which team someone is on in
> the match (i.e. home/visitor, for lack of a better description). A
> simple third column.
>
> A matches table keeps track of the individual matches, and the
> match_players tracks the number of players, whether there are two or two
> hundred.

This is what I was trying to get at. Just have one table that holds
each player that played. You can always add columns for some of those
special cases (like having it be only a single match, or multi-team
match). I would do something like this:
Matches:
match_id
match_type (singles, team, etc)
match_teams (1 for singles, 2, 3, etc)
match_result
....

Players:
player_id
player_match_id (tie to match)
player_team_id (which team the player was on, 1 for just him/herself)
....

A lot of your work will be on the coding side to convert the
information you store in the database to something that is readable. I
always think of some fields as "flags" for something I want to denote
on a page. For example, you will need to use your code to decipher
which team each player is on, and how the teams look in display, but
the database has enough information to decipher that as it is above...

Nino

EMAIL REMOVED wrote:
> On May 25, 5:18 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> Roger wrote:
>>> On May 23, 12:02 pm, "nino9st...@yahoo.com" <nino9st...@yahoo.com>
>>> wrote:
>>>> 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));
>>> It's for a dart league.
>>> Anyway, I have an existing database, a table for their location, table
>>> for teams, table for players. there could be multiple teams per
>>> location. I have it pretty much working, just need to re-figure out
>>> the results part of the thing.
>>> I had planned on using a single table for the results, then have
>>> something like what you have. After I got thinking, it would end up
>>> with NULL values for opponents and partners for the singles matches.
>>> Not to mention having to use codes for the type of games.
>>> To minimize the NULL fields, I could just use different results tables
>>> for singles than I do for doubles. (meaning I won't have to have
>>> partner info as fields since they don't have partners in singles).
>>> that got me thinking of having a table for each type of game
>>> played. I may want to be able to run reports for certain types of
>>> games, could be more efficient in the long run if I break them out.
>>> Roger
>> Or you could just have a table (match_players) with matchid and
>> playerid. Add as may entries as you need for however many players there
>> are in the match.
>>
>> You'll probably need to also keep track of which team someone is on in
>> the match (i.e. home/visitor, for lack of a better description). A
>> simple third column.
>>
>> A matches table keeps track of the individual matches, and the
>> match_players tracks the number of players, whether there are two or two
>> hundred.
>
> This is what I was trying to get at. Just have one table that holds
> each player that played. You can always add columns for some of those
> special cases (like having it be only a single match, or multi-team
> match). I would do something like this:
> Matches:
> match_id
> match_type (singles, team, etc)
> match_teams (1 for singles, 2, 3, etc)

I wouldn't even do that. You can already determine that from the number
of players. Don't duplicate data - it's more to change when you find
something wrong later!

> match_result
> ...
>
> Players:
> player_id
> player_match_id (tie to match)
> player_team_id (which team the player was on, 1 for just him/herself)

Same thing. This is already in the Players table, unless the player
moves during the season. Then you would need it here.

> ...
>
> A lot of your work will be on the coding side to convert the
> information you store in the database to something that is readable. I
> always think of some fields as "flags" for something I want to denote
> on a page. For example, you will need to use your code to decipher
> which team each player is on, and how the teams look in display, but
> the database has enough information to decipher that as it is above...
>
> Nino
>


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
EMAIL REMOVED
==================