SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2005
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Complex Sports Team Database

    Hi,

    I'm currently trying to develop a database for my University Rugby team, to include, amongst other things, player profiles, matches, match statistics, league tables etc. At the moment I'm having a lot of trouble getting around the different tables and relationships that need to be created. I have a player table, linked to a team table, linked to a match table but every week a different set of players, playing for the team, are involved in a match. Any help or advice regarding how to model this situation would be greatly appreciated.

  2. #2
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    It sounds like you need the following tables:

    Players
    -------
    player_id
    player_name
    etc.

    Team
    -------
    team_id
    team_name
    etc.

    Match
    -------
    match_id
    home_team_id
    away_team_id
    etc.

    Players_Match
    --------------
    player_id
    match_id

  3. #3
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Also need a table for which player belongs to which team. I suppose you might be able to get away with a foreign key in Players.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Players_Match is incomplete because it doesn't tell you which team the player played the match for
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    Players_Match is incomplete because it doesn't tell you which team the player played the match for
    I left out another table:

    Player_Team
    ------------
    team_id
    player_id

  6. #6
    SitePoint Enthusiast
    Join Date
    Dec 2005
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thanks

    Thanks a lot guys, I'm quite new to the whole database thing, that helps quite a bit. It looks like I'm dealing with a whole load of many-to-many relationships and as you say, I'll have to create quite a few middle tables to deal with the complexity. If I have any more problems, I know where the experts are!

  7. #7
    SitePoint Enthusiast
    Join Date
    Dec 2005
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Another Question

    I got the bit about the player_match and player_team tables, but what if different players are playing for the same team in each match? (ie. the team changes from match to match) does that mean I need some sort of Player_Team_Match table, or how would I go about incorporating that information? Any help is greatly appreciated.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yep, Player_Team_Match might work

    can a player ever change teams?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    Dec 2005
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    many players, many teams

    Yes, not only can different players play in the same team but they can play in many teams, for example, in my position, I could play for the first team or the second team, or someone else might play in my place.

    I was thinking about it quite a lot last night, I don't really need to record the details of opposition players - only my club. That means I don't really need to identify the players with the team as it will be taken as given.

    While I'm on that note, are there any good books/websites that you would recommend on data modelling or relational database design? I can see a lot of books out there when I search but none seems to stand out above the other. I'll be taking a course next semester on web programming and databases to supplement what I do at the minute as a hobby, but I fear that might just go over the basics rather than go a little more in depth. When it gets to this stage the relationships get a bit confusing!

    Thanks very much again for your help!

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Pootsy
    ... are there any good books/websites that you would recommend on data modelling or relational database design?
    Introduction to Data Modeling
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Pootsy
    Yes, not only can different players play in the same team but they can play in many teams, for example, in my position, I could play for the first team or the second team, or someone else might play in my place.
    ignoring your second comment about not needing to record the other team's details, i suggest this adaptation of Hartmann's solution:

    Players
    -------
    player_id
    player_name
    position_id
    etc.

    Team
    -------
    team_id
    team_name
    etc.

    Team_Players
    -------
    team_id
    player_id
    start_date
    end_date

    Match
    -------
    match_id
    home_team_id
    away_team_id
    etc.

    Positions
    -------
    position_id
    position_name

    Players_Match
    --------------
    player_id
    match_id
    team_id
    position_id

    this arrangement allows the following:
    • identify which team a player played for during a particular date range (leave end_date NULL for players actively assigned to a team)
    • allow you to record when a player temporarily joins another team for a specific match, since the team_id is in the players_match table
    • allow you to record when a player competes in a position during a match other than their usual, since the position_id is in the players_match table


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
  •