CODEWITHSUNDEEP
mysqldatabasedatabase-design
Neil
Neil

Reputation: 2519

What is the best approach to store data for the box score of a sporting event?

I'm fairly new to MySQL (and databases as a whole) so I am not sure of the best approach to store data like this.

I want to store statistical data for a sporting event, we are going to start with ice hockey. Each player has their career stats, but I also have boxscores for each game (we want to provide gamesheets to the players at the end of each game).

I have a schedule table that contains the teams involved, date, and gameid for each event. I also have a players table that links their identity to a player id.

These are the two approaches I have thought of so far:

  1. Use a stored procedure to create a table for every game on the schedule, and within that table, store all the player ids, and the stats for that particular game. When a scoring play occurs, it alters the table data for the current-game table and the career-stats table for that player.

  2. Or is it better to create a single 'game-score' table and record every scoring play (goal, penalty, etc.) onto that table using the gameid & playerid as reference?

The first approach could get messy really quick if we projected to have 100 matches in a season (individual game stats would be removed at seasons end, and then long term stats are retained)

The second approach could end up with a really long table really quickly which might prove slow to query as it continues to grow.

I am leaning towards the second approach, but would like to hear some input if possible. Or maybe I am taking the completely incorrect approach to this and would love to have someone send me down the right path. Any help is greatly appreciated.

Upvotes: 2

Views: 1760

Answers (1)

Shawn
Shawn

Reputation: 3369

It really depends how granular you want to get with your data but I tend to stay away from stored procedures and prefer to do any business logic in code as its easier to maintain.

I would have the following tables:

Player -> contains player specific items such as size, right-handed, age, etc
PlayerStats-> contains player id, year (of current season), and aggregated stats... you could +1 to this table for scores and penalties for example

Teams -> contains player id, and venue id, team name
Venue -> Items about the arena such as location, average indoor temperature, etc
Games -> contains a home team id (id from teams table) and away team (id from teams table), venue_id, home_box_score, and away_box_score, game_date
PlayerGames -> contains player id, game id, and the game specific goals... For example any player that was in the game goes in here. If some one scores you +1 their score in this table AND the PlayerStats

In general a table should be the attributes of a specific thing cause it then allows you more flexibility in joining the data to create alot of different reports...

For example with the list I just gave you can find out how many times a goalie like fleury has played ovechkin and show the stats comparison of how many goals ovechkin has scored on fleury and the outcome of the games by box score (such as did fleury win or lose the game if ovechkin scored 2 or more goals in the game).

Upvotes: 2

Related Questions