Competition description:
There are about 10 teams competing against each other over a 6-week period.
Each team's total score (out of a 1000 total available points) is based on the total of its scores in about 25,000 different scoring elements.
Most scoring elements are worth a small fraction of a point and there will about 10 X 25,000 = 250,000 total raw input data points.
The points for some scoring elements are awarded at frequent regular time intervals during the competition. The points for other scoring elements are awarded at either irregular time intervals or at just one moment in time.
There are about 20 different types of scoring elements.
Each of the 20 types of scoring elements has a different set of inputs, a different algorithm for calculating the earned score from the raw inputs, and a different number of total available points. The simplest algorithms require one input and one simple calculation. The most complex algorithms consist of hundreds or thousands of raw inputs and a more complicated calculation.
Some types of raw inputs are automatically generated. Other types of raw inputs are manually entered. All raw inputs are subject to possible manual retroactive adjustments by competition officials.
Primary requirements:
The scoring system UI for competitors and other competition followers will show current and historical total team scores, team standings, team scores by scoring element, raw input data (at several levels of aggregation, e.g. daily, weekly, etc.), and other metrics.
There will be charts, tables, and other widgets for displaying historical raw data inputs and scores.
There will be a quasi-real-time dashboard that will show current scores and raw data inputs.
Aggregate scores should be updated/refreshed whenever new raw data inputs arrive or existing raw data inputs are adjusted.
There will be a "scorekeeper UI" for manually entering new inputs, manually adjusting existing inputs, and manually adjusting calculated scores.
Decisions:
Should the scoring calculations be performed on the database layer (T-SQL/SQL Server, in my case) or on the application layer (C#/ASP.NET MVC, in my case)?
What are some recommended approaches for calculating updated total team scores whenever new raw inputs arrives? Calculating each of the teams' total scores from scratch every time a new input arrives will probably slow the system to a crawl. I've considered some kind of "diff" approach, but that approach may pose problems for ad-hoc queries and some aggegates. I'm trying draw some sports analogies, but it's tough because most games consist of no more than 20 or 30 scoring elements per game (I'm thinking of a high-scoring baseball game; football and soccer have fewer scoring events per game). Perhaps a financial balance sheet analogy makes more sense because financial "bottom line" calcs may be calculated from 250,000 or more transactions.
Should I be making heavy use of caching for this application?
Are there any obvious approaches or similar case studies that I may be overlooking?
