#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2005
    Posts
    219
    Rep Power
    10

    League Database design.


    Doing a side project for a league. I am having trouble setting up the tables. Here is what I have. Please provide input on how this should be set up for peak results.

    Players
    Player Id Primary Key
    Firstname
    Surname
    Address1
    Address2
    Address3
    Postcode
    Telephone number
    Shirt number
    Photo
    Team Id.

    Team
    Team Id Primary Key
    Team name
    League Id
    Age Group Id
    Area of team
    Web Address
    Date Formed
    Manager FName
    Manager SName

    League stats
    League Id Primary Key
    Team Id
    Game Id
    Season
    Games Played
    Games Won
    Games Lost
    Games Drawn
    Goals For
    Goals Against
    Goal Diff
    Points

    Fixtures
    Game Id Primary Key
    Team Id
    League Id
    Date
    Age Group Id
    Time
    Home Team
    Away Team
    HomeT Result
    AwayT Result
    Location of game

    League
    League Id Primary Key
    League Name

    AgeGroup
    Age Group Id Primary Key
    Age Group Title
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    fixtures has hometeam and awayteam (correct) but what is team id for?

    why does league stats have team id and game id?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2005
    Posts
    219
    Rep Power
    10
    Originally Posted by r937
    fixtures has hometeam and awayteam (correct) but what is team id for?

    why does league stats have team id and game id?
    Q1.
    That was an issue. should i have teamID twice? since two different teams are playing?

    Q2.
    each player has a team they are on and a game they had those stats in..
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    hometeam and awayteam are ids, aren't they?

    so the table should actually be called player stats, not league stats?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2005
    Posts
    219
    Rep Power
    10
    I am confused on the best way to do stats..

    So what I want is a page where a user logs in and it brings up his team page. from there he can select a game and input the stats for the game. there will be team stats and player stats.

    so a playerstats table wouldnt work out would it? I would need all player stats in the same table as fixtures(games)
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    4
    Rep Power
    0
    Originally Posted by spezialize
    I am confused on the best way to do stats..

    So what I want is a page where a user logs in and it brings up his team page. from there he can select a game and input the stats for the game. there will be team stats and player stats.

    so a playerstats table wouldnt work out would it? I would need all player stats in the same table as fixtures(games)
    I think you would only need to keep track of Player Stats because the Team Stats would essentially be an aggregate of the Player Stats, no?

    So just concentrate on the Player Stats and have the ID of the Player, Team and Game. That way, you can cross-reference and produce aggregate Team Stats on the fly without having to store it in the Database.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2005
    Posts
    219
    Rep Power
    10
    I understand your point. I guess I want to make it clearer on whats going on.

    I want to be able to show specific stats from a specific game played between two teams. Thats where all of my table confusion is coming from.

    So when people log in they can enter in the results from a game. So they select the game and then are able to enter in game stats...

    such as Time of Possession, goals, cards etc.
    but then they are able to select a player, and his stats too.

    I am guessing im going to need two redundant tables one for team stats one for player stats?
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2005
    Posts
    219
    Rep Power
    10
    Alright there is really lack of information on this subject. Many google searches have found me limited information which is surprising as to me this is a very practical use for databases which people will have interest in.

    Here is what I am designing...

    A webpage which will track my football league. Each team owner gets a login where they will log in, select their game, and input game stats and player stats.

    Where I am confused about is how I should do the player stats. I want to be able to show specific player stats related to a single game. I am very confused on how these tables should be laid out. If anyone has any other questions they need answered to help me solve this just ask.

    and this is NOT a hw assignment. This is a personal website I am designing for a league I am involved in for Madden 2009 ps3.
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    madden 2009? whoa, the nfl hasn't even played the 2008 season yet...

    my advice is to put stats aside for a moment, and concentrate only on the other tables: leagues, teams, games (only soccer palyers call them fixtures), and players

    can you get the relationships defined for those?

    one thing that might trip you up is changes in relationships over time

    can a team change leagues? can a player change teams?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2005
    Posts
    219
    Rep Power
    10
    Originally Posted by r937
    madden 2009? whoa, the nfl hasn't even played the 2008 season yet...

    my advice is to put stats aside for a moment, and concentrate only on the other tables: leagues, teams, games (only soccer palyers call them fixtures), and players

    can you get the relationships defined for those?

    one thing that might trip you up is changes in relationships over time

    can a team change leagues? can a player change teams?
    No pretty much I am setting it up for 1 league. Players can change teams however teams wont be changing leagues.
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    okay, then you are allowed to have league_id in the teams table and leave it at that

    can players change teams?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2005
    Posts
    219
    Rep Power
    10
    Originally Posted by r937
    okay, then you are allowed to have league_id in the teams table and leave it at that

    can players change teams?
    Yes players can change teams. However anything like that I can manually switch their teamId. Users wont be able to do that themselves.
  24. #13
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    okay, this is fabulous, it's getting simpler all the time

    could i see your design for the leagues, teams, players, and games tables now?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2008
    Posts
    1
    Rep Power
    0

    Stats


    I'm working on a Database for a League as well... for what I understand, Stats shouldn't be stored in a DB, instead, just store results, and then calculate stats based on those results on real time.
    I.e:
    TableResults
    ResultID
    HometeamID
    AwayteamID
    score

    Then you're able to calculate how many times, team A played... how many times they won/tie/lost, etc...
    using scripting to calculate the stats in real time.
    I hope this helps...
    otherwise, I don't see how you can calculate stats inside the DB in real time to keep the stats updated...

IMN logo majestic logo threadwatch logo seochat tools logo