sports league database design

I'm developing a database to store statistics for a sports league. I'd like to show several tables:

• league table that indicates the position of the team in the current and previous fixture
• table that shows the position of a team in every fixture in the championship

I have a matches table:

• Matches (IdMatch, IdTeam1, IdTeam2, GoalsTeam1, GoalsTeam2)

With this table I can calculate the total points of every team based on the matches the team played. But every time I want to show the league table I have to calculate the points.

Also I have a problem to calculate in which position classified a team in the last 10 fixtures cause I have to make 10 queries.

To store the league table for every fixture in a database table is another approach, but every time I change a match already played I have to recalculate every f开发者_开发知识库ixture from there...

Is there a better approach to solve this problem?

Thanks

The problem is that (eg) the position or rank of the teams in the last 10 fixtures, is derived data. So it is a duplication to store it in a table, and completely unnecessary, it creates an Update Anomaly where there was none. Of course, as usual, whenever you create duplicates, you then have the consequential problems of keeping them up-to-date and synchronised with each other, and the solutions to such problems are never reasonable. Therefore, do not create the duplicates in the first place, and you will not have the consequences in the second place, and no coded half-solutions in the third place.

Just create Views for all those result sets (that you think of as "tables") that are derived from the real source tables. That includes any list of ranking or position; in any context (fixture, championship, league, nation).

Obtaining ranking or position is easy in SQL. But the code depends on which flavour of SQL (or non-SQL) that you have. Some flavours cannot handle subqueries, and provide a RANK() function instead. If you specify which SQL you have, I can provide specifics.

I assume games occur once a day or week.

If so, you can run a trigger or batch operation after the matches are updated and store those results in a summary table.

That way the calculation happens only once per match.