Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2013
    Posts
    5

    Question Fantasy football stats, normalization

    For about six years now, I've kept "Top 10" stats for different categories in my fantasy football league. I initially set this up by just creating a table called "records" which contained team owner, player name, year, week, position and score. I could run a query to pull all "QB" and sort by score DESC limit 0,10. Easy.

    Now, because I'm a glutton for punishment, I want to go back and input ALL player scores from every game, and use that group of tables to pull stats from, including the above records. So..... need some help here.

    The simplest table will be "scores":
    - player (name of football player)
    - teamID (ID of user who owned the player)
    - gameID (ID of game in which the score occurred)
    - position (QB, RB, WR, etc.)
    - score (integer)

    The complicated part is how to input the matchups. I can't just create a table called "games" with ID, year, week, score1 and score2. I need to be able to differentiate score 1 and score 2.

    So do I just create a "games" table with ID, year, week... and then add another entry into scores where position is indicative that the "score in that entry is the total score for that person for that game? Or do I make an entirely new table that just has total score for that gameID - which would create 2 entries per gameID in that table?

    Sorry if this is too wordy. Any discussion and personal experience would be appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Make the matchups a view based on your Scores table... You have all of the information that you need (and more), so it ought to be simple to just group up at the game level.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2013
    Posts
    5
    That was my initial thought but since the "total score" (total of all players in a gameID for a specific teamID) is going to be so commonly used, I wanted to limit the amount of math involved.

    I'm considering adding an entry IN the scores for every gameID and teamID using "TS" as the position that will reflect "Total Score".

    Is that a bad practice?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Uff-da! I thought that I replied to this post yesterday, but my reply seems to have gone AWOL on me!

    Anytime you store an aggregate (like a game score) when you also store the discreet data (like the play/player scores), you are looking for trouble and you'll usually find it!

    I would store the player, the datetime of the event, and the points. You can derive the team, player's position, matchup, and total score using simple JOIN and aggregate operations. I would only store additional information if I found that the platform that I was using could not process the data fast enough... Out of nearly a thousand systems, I've seen that less than a dozen times and those were on massively used systems (thousands of users online, with over a thousand SQL Statements per minute).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

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