Professional Web Applications Themes

league standings database - MySQL

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

  1. #1

    Default league standings database

    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

    Roger Guest

  2. #2

    Default Re: league standings database

    On May 22, 2:00 pm, Roger <com> wrote: 

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

    nino9stars@yahoo.com Guest

  3. #3

    Default Re: league standings database

    On May 23, 12:02 pm, "com" <com>
    wrote:
     

    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 Guest

  4. #4

    Default Re: league standings database

    Roger wrote: 
    >
    > 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
    >[/ref]

    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.
    net
    ==================
    Jerry Guest

  5. #5

    Default Re: league standings database

    On May 25, 5:18 am, Jerry Stuckle <net> wrote: 
    > [/ref]
    > [/ref]


    >
    > 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.[/ref]

    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

    nino9stars@yahoo.com Guest

  6. #6

    Default Re: league standings database

    com wrote: 
    >> 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.[/ref]
    >
    > 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)[/ref]

    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!
     

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


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

Similar Threads

  1. League tables
    By arthur in forum ASP Database
    Replies: 2
    Last Post: June 28th, 03:49 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139