SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Zealot moshe_be's Avatar
    Join Date
    Dec 2000
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I got a problem with MySQL. I am building database to hold results of Ladder Matches and not sure how to store the players.

    --- Option 1:

    If I am having a table LadderGames with fields:
    player1_ID
    player2_ID
    player1_winORlose
    player2_winORlose

    With that scheme when I search the games that player X played, it will probably look like
    SELECT player1_ID, player2_ID FROM.. WHERE player1_ID = 1 OR player2_ID = 1.

    When I want to display the player with ID 1, I will have to check if the right player is at the field player1_ID or the second.

    --- Option 2:

    To have Games and PlayerGames table, then the problem will be at the display, having to find the opponent to each game where player 1 was found at.

    In short, What is the best method to store those players?
    Any help will be appreciated

  2. #2
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I understand the first option, I think. Could you explain the second option a little better?
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  3. #3
    SitePoint Zealot moshe_be's Avatar
    Join Date
    Dec 2000
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sure, second option is having table Games that contain:
    game_id
    game_time
    etc.

    The second table contain the players - PlayerGames:
    pl_game_id
    pl_user_id
    pl_winORlose

    In order to find the games where user 1 participated the query will look like:
    SELECT pl_game_id FROM PlayerGames WHERE pl_user_id = 1

    But then for each game, I will have to find the opponent.

  4. #4
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I like Option 1 better. It makes no sense to separate the players for each game into a separate table. One thought, though... Instead of having 4 columns (player1_ID, player2_ID, player1_winORlose, player2_winORlose), why not just have 2 columns (winner_ID, loser_ID)? I'm assuming ties are not possible. If they are, you could put a third column to flag tied games.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  5. #5
    SitePoint Zealot moshe_be's Avatar
    Join Date
    Dec 2000
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The Games are existing on the table before the match started.

    One player challenge another one, then the game is added to the table.

    After the game was completed the table is updated.


    Some reasons to use the Games and GamePlayers table is easier searching.

  6. #6
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by moshe_be
    The Games are existing on the table before the match started.

    One player challenge another one, then the game is added to the table.
    I see. In that case your Option 1 database structure looks fine.

    Some reasons to use the Games and GamePlayers table is easier searching.
    But as you've shown above, searches can be performed just fine with the Option 1 data structure... I don't see any real problem with your proposed layout.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  7. #7
    Grumpy Mole Man Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,066
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You need to have something for if there's a draw.

    I'd suggest:

    Player1_ID
    Player2_ID
    Winner

    Where winner can be set to 1, 2, or Draw. 1 means player 1 won, 2 means player 2 won.

  8. #8
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Do I hear an echo?
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  9. #9
    SitePoint Zealot moshe_be's Avatar
    Join Date
    Dec 2000
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sounds good , I will start working on it and see if that works out.

    Thanks for the replies


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
  •