Avoid redundancy in a database for a tournament software?

Question

I'm about to write a tournament software. Therefor I have two db-tables in mysql database: players and matches. In matches I save every single match played in the tournament:

ID_player1, ID_player2, Goals_player1, Goals_player2

I would like to sum up all goals from all matches of every player, so I can display a ranking, where won mathes and goals and countergoals are displayed. Now the question is, how to realize this in the database: I see several options:

• Save goals and countergoals in the players table. Every time a match is entered into the database, the goals and countergoals are added to the related player. (but what about redundancy..?)
• Save goals and countergoals in the players table. But every time a match is entered into the database, run a query over the matches database and sum up all the goals every time again and again. This way I would at least avoid possible inconsistency.
• Don't save goals and countergoals in players table. But every time I would like to display the players rank with all goals and so, there would be a humangous database query running over the whole matches table for every single player. This is probably not very smart when it comes to performance, is it?

So what is the best way to store that information..? I appreciate every help or hint. greets and thanks in advance!

Answer 1

Start by having a table with one row for each goal-like thingie. It would include player_id, game_id, etc. From that you can do SELECT COUNT(*) ... GROUP BY player_id, goal_type to get counts.

You can either do that 'live' when you need the goal counts, or you can use that SELECT to populate some kind of summary table.

After you have tried all of that, you will have all the data stored so that if the forumlas are wrong, you can repair the counts.

Then you can think about doing the alternatives. Think of this as being a learning exercise.