Architecture strategies for a complex competition scoring system

Question

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?

Answer 1

My thoughts, based on not knowing your actual algorithms:

• Do the scoring in your application code, unless it is very simple
• Keep running information that will help you generate scores quickly
  • For example, if you need an average, you can keep the sum and count and calculate the average as needed. (Note, for simple things like average, you could just use the Database)
  • Also, take a look at moving averages (http://en.wikipedia.org/wiki/Moving_average) they can allow you to store very little data about the past and still display historically useful information
• I wouldn't worry too much about caching data, rather i would think about keeping the running information and being able to calculate the result quickly. However, if you have a lot of viewers for the same values, you might start caching.

If the total number of data points is 250k, you can do a fair bit of math on that in a very small amount of time.

I am not sure what you are planning to do for the web app architecture, but i would probably write the application you describe as a single page web app with as few server API points to get data as JSON. Then I would do the graphic/display all on the browser in JavaScript using something like D3, Raphael or another chart library.

Answer 2

I've written something very similar to score live sporting events for several different sports, where is what worked for me.

• I found the application layer was the best place to perform scoring calculations for two reasons, 1: We were able to express the logic in c# than SQL. 2: We were using NHibernate so it just seemed more natural not to do it in the database and work with our domain model. Where we needed to expose logic to external applications we used a WCF service layer.

• The model the actual scoring process of each match we used the idea of a match, consisting of two teams playing against each other, as a timeline with each scoring element and any other match related milestone e.g. half time, foul, being an item on the timeline.

each scoring element, which in the implementation consisted of a command object, would then update the match state, e.g. the score, phase of play, but would also contain the logic/data needed to roll back the state change if necessary. sometimes this consisted of having to save the entire match state every time something new was entered on the time line.

each time line entry or collection of entries was tracked with a Guid that represented a transaction in our system. The match class then had logic that would allow it to roll back the last transaction to any previous point in the match.

Each important entity that we need reports on in our system e.g. player, team, league, season, would have its own report class which would be updated by a trigger which fired every time a time line entry was made, what was updated would depend on the timeline entry but the result was we had these cached report objects that always had the latest information pushed to them.

We found no good solution for dealing with ad-hoc queries

• Cache state/timeline entries of any on going matches, also cache your report data and push updates to it rather than update it by polling for changes periodically.

  • If you have your scoring logic on a server and your scoring client software has to connect to it remotely from a sports venue then message queueing is your friend, you will lose connection at some point and reliable message queuing will ensure that each message, in my case each time line entry, will get through and be in order.

Answer 3

I would highly suggest leaving the calculations to the database layer. The database is much better suited to handling the calculations and manipulating large datasets, as well as storing them in a way that is most efficient to retrieve. Not to mention, if you do this at the application layer you will need a way to handle contention and failures, should two users initiate the calculations at once or should a failure occur.

I would recommend storing the raw data in tables and create a view layer to aggregate the data. I am more familiar with Oracle materialized views, but SQL Server offers a similar construct called Indexed Views. These views act like tables, and are perfect for aggregate functions like the calculations you're talking about. And, these Indexed Views can be updated automatically when data is changed on the underlying tables.

Answer 4

Here are my thoughts about this system.

1. The number of scoring elements seems to be very large and I tend to see that they are independent of each other (if not, you need to think about pre-process your large raw input so they become independent). This is the call sign for turning your system into a distributed system. You will have multiple peers each with its own SQL database running, and each with a service layer running on top of the SQL database.

2. There are two choices for the distributed system. A hierarchical one (meaning there is one master specialized node to do coordination, and multiple sub-coordinating nodes) or a peer-to-peer one. I would suggest you use the peer-to-peer model since they have several advantages:

   • This is highly robust, and you essentially have a backup system built-in in this model if you allow some redundant data on each peer node
   • If you can make raw input independent or they are inherently independent then you don't really need to cluster dependent raw input into a coordinating node as in hierarchical model

3. In order to save yourself from multiple headaches caused by concurrent modification of the data, make sure your data schema puts in additional timestamp data instead of deleting/modifying previous data in an edit. Have a timestamp system for each input piece as well. I can think of having 3 kinds of time at the moment: [1] logic time, [2] expired time, [3] write time. When an edit comes in, you add new data with new write-time while you keep the logic dates the same. In short, don't delete previous data. The timestamp is also a partial 'diff' framework for you since if part of the input data does not change, then the node responsible for that part only need to update the timestamp

4. Compute the component score that each peer is responsible whenever new data comes in. That way, you will not have to do much calculation when you want to get the score (current or historical). If you also put in timestamp for these scores, you also get faster historical score read time. This is in principle a cache for you. Also, do this at the service/application layer, don't do this in the SQL database layer. With timestamp, you'll have no problem with ad hoc queries.

5. You have to be careful with error handling and fixing things when something goes wrong at some node when you make a write. This will be non-trivial, you will have some heavy lifting done for you by SQL and message queue though.

The protocol for timestamping data and partial scores will need to be defined more specifically, but I don't see anything that is fundamentally wrong with this architecture. If you see, let me know. Many thanks!

Answer 5

You have a large number of measurements feeding into calculation operations to produce other values, which in turn feed into other calculation operations. It can be visualised as a large and complex directed acyclic graph, where each of the nodes of the graph is a calculation operation (or an input or output) and each of the lines is a value.

I'd base any solution on this fundamental paradigm. You can introduce substantial automation, such as building the graph automatically based on each operation knowing what inputs it needs; you can support multiple data types; you can introduce the concept of data "position" to allow it to deal with streaming data and ensure that inputs are synchronized to the same "position"; you can easily implement caching so that recalculation is not done on the entire graph when one input changes.

I wouldn't attempt doing this on the database: there's too much complexity and code running in the DB is harder to debug and maintain. Also, the only values that really need to be persisted are the inputs, final outputs, and perhaps some intermediate values that are very expensive to calculate (you can create special "persistence" nodes in the graph that simply cache to the DB). All other intermediate values can and should be transient.

I've implemented this approach in the past for EEG signal processing and it worked beautifully.