Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?
share|improve this question
add comment

3 Answers

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.

share|improve this answer
add comment

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.
share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.