Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2007
    Posts
    108

    Sports statistics database design question No.1!

    Hi this is my first post here, hope I'll enjoy it!

    My intention is to design comprehensive database that would track the results of sports events (soccer, baseball, basketball, hockey - with intention of expanding the list) and track any kind of statistic that you can link to a particular sport (also with the intention of adding more detailed statistics with time).
    Along with the results and match details it would store odds provided by sportsbooks.

    The idea of such detailed database would be to serve as a predictive tool for upcoming sporting events!

    Now here comes my first problem and that is:

    TRACKING OF EVER-EXPANDING STATISTICS ON PARTICULAR MATCH!

    Suppose you have BaseballEvent table with

    EventID as PK
    DateTime
    HomeTeamID
    AwayTeamID both as FKs to Teams Table
    MatchStatus ............ having values for Completed, Postponed, Abandoned etc.
    HomeTeamScore
    AwayTeamScore
    AverageHomeTeamLine
    AverageAwayTeamLine

    This would be the most basic structure and suppose it serves you OK, you get some predictive edge by analyzing such historical data, but you believe you would gain even more edge by including more statistics and now you want to include:
    Results of every inning, Pitching stats (starter, closer, 3rd pitcher, ER - earned runs, H - hits), batting stats etc.

    I guess you could design special tables that would have the following structure:

    EventID
    PlayerID
    ....
    and now
    ....
    ....
    list of all stats that you want to track like
    ....
    H
    ER
    AB (at bat)
    Err
    ...
    and so on!

    But what happens when you want to add new statistic, either because it's invented or you haven't tracked it before or it wasn't available to you, like Temperature, Wind Speed, Attendance, Man of Match award.

    What happens if you want to know every pitch for every game, like you want to know that Halladay threw a strike on his 76th pitch in a particular game?

    HOW DO YOU MODEL THAT?

    My guess would be creating some generic BaseballStats Table along with StatsDescriptionTable like having:

    BaseballStatID
    EventID as FK
    StatsID as FK linking to the description of that particular statistic
    ValueOfThatParticularStat

    and having StatsDescriptionTable

    StatsID
    StatsDescription

    IS THAT A GOOD DESIGN? I don't know how to factor in that the fact that some stats are based on event like temperature, wind; some stats are for the home team like the result of the bottom of the 12th inning and some stats are intended for players like number of pitches thrown by Zito!

    Please help, I'm lost in this!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Riorin
    Please help, I'm lost in this!
    that isn't a very succinct question, is it

    the answer is: add the tables that you need, with due regard to relationships of the keys

    if there can be multiple stats of the same type for each event, then they have to go into a separate table

    if the additional stat is in a one-to-one correspondence with the event itself, e.g. attendance, then it should go right into the event table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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