Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57

    Events, tournaments, matches, & games (teams or single/multiple players as opponents)

    Hello,

    This is driving me crazy. I can't quite figure out how to model this data.

    The domain is computer gaming.

    The easy part is one Event has many Tournaments has many Matches. The difficult part is the matches/games and opponents.

    Each match has 1 or more games, and each game can be played 1-on-1, 2-on-2, etc. (i.e. 2 players from one team vs 2 from the other), or Team vs Team.

    Here are some examples from another site:

    Player vs Player: SK Gaming - Esports, Professional Gaming, Counter-Strike, Warcraft III, World of Warcraft, FIFA, DotA, Starcraft, Quake, Console
    Team vs Team: SK Gaming - Esports, Professional Gaming, Counter-Strike, Warcraft III, World of Warcraft, FIFA, DotA, Starcraft, Quake, Console
    Team Players vs Team Players: SK Gaming - Esports, Professional Gaming, Counter-Strike, Warcraft III, World of Warcraft, FIFA, DotA, Starcraft, Quake, Console

    It's also necessary to know (in the last case), which two teams are playing each other.

    You can assume player and team tables with ids and names.

    Any ideas?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    I'd load both players and teams into the same table. You can separate them logically using dedicated views, but this allows either a player or a team to be assigned to game.

    And of course, a player can be assigned to a team.

    This is the process of creating a physical schema from a logical design. The two do not always match one-for-one.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    Hi Blindman,

    Is that the only option? Also, what about when a game is 2 against 2, for example?

    This is what I have so far ...

    Code:
    matches
        id
        tournament_id
        start_date
    
    match_games
        id
        match_id
        winning_opponent_id
    
    match_opponents
        match_id
        opponent_id
    
    game_opponents
        game_id
        opponent_id
    
    opponents
        id
        name (“Team 1” or “Player 1”)
    _da.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Its the best option.

    When a game is 2 on 2, why not form them into teams of 2?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    You mean they are part of larger "clans"?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    There's a many-to-many relationship between teams and players?
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  8. #8
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    Quote Originally Posted by blindman View Post
    You mean they are part of larger "clans"?
    Yes. ..........

  9. #9
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    Quote Originally Posted by loquin View Post
    There's a many-to-many relationship between teams and players?
    Yes. ..........

  10. #10
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    I'm thinking of doing something like this with the opponents (Class Table Inheritance):

    Code:
    game_opponents
        game_id
        opponent_id
    
    opponents
        id
        type        (Player / Team / TeamPlayer)
    
    players
        id            (Linked to opponents.id)
        name
    
    teams
        id            (Linked to opponents.id)
        name
        is_national_team
    
    team_players
        id            (Linked to opponents.id)
        team_id
        player_id
        date_from
        date_to
    _da.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Well, there is more than one way to skin a cat, but I've already told you that I do not recommend that approach.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    That's what the business logic in your application is for. You were close with the model before. blindman was pointing out that you were trying to assign two different meanings to the word/entity "team". Introduce the concept of a clan. Teams and clans are both comprised of players, but they are very different things in the real world.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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