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
AwayTeamID both as FKs to Teams Table
MatchStatus ............ having values for Completed, Postponed, Abandoned etc.
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:
list of all stats that you want to track like
AB (at bat)
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:
EventID as FK
StatsID as FK linking to the description of that particular statistic
and having StatsDescriptionTable
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!