Schema/Structure to handle secondary computed data

Question

I'm trying to design a LAMP-based website for my pinball league, which plays competitive pinball every week in match play.

One of the features of our league is that we convert raw scores to matchpoints. For example, suppose four players play a match and their scores are:

• 14,325,320
• 35,332,110
• 34,003,990
• 11,345,920

then their matchpoints would be 2, 4, 3, 1 respectively.

Sometimes we might want a more complex matchpoint algorithm, which might be based on properties of the raw scores (e.g., if the highest score is better than all other scores combined, award an extra matchpoint).

Obviously whatever database schema I use, I would like it to store not just the raw score, but also easily display the matchpoints associated with that score.

What I'm confused about is the best place to compute those matchpoints. Should I:

1. Set a SQL trigger each time a raw score is entered into the database, then have some complex logic to determine if matchpoints should be computed and then modify rows as necessary? I'm not sure if SQL can necessarily handle our matchpoint algorithms easily.
2. Don't store matchpoints in the database at all, but compute them on the fly each time they are requested? This seems like a waste of computation power.
3. Having a scheduled event in SQL, or a cron job on the server, that goes through the database and computes matchpoints, then adding that data to the database as necessary? That seems to have the drawback of needing to wait around for that computation to happen.

Right now the best approach I can think of is a hybrid of 2 and 3, where during the competition matchpoints aren't stored, but computed on the fly, and then having a human-thrown trigger at the end of each week's competition that computes all the matchpoints and puts them into the database. Possible bugs might be that I have to be careful to reuse the same code or else the database scores might differ from the temporary scores. Also in case of human error the trigger might not be thrown.

It seems like I can't be the only person who needs a bit of automated business logic to parse database entries as they come in and then display them. In some sense the ideal result would be something like a SQL View, but where I can add my own logic to it. I have no idea if something like that exists.

Are there any better ideas?

Answer 1

I think a spin-off of option 2 would be ideal based on your requirements that seem to only be for presentation and not further computation.

You query the database for all the scores from match X and order them from high to low. Then in the presentation layer of the web application, simply add ranked number assignments to them starting with 1 for the first and n+1 from there on for each row you fetch from the result set.

A query could be

SELECT name, score FROM matches WHERE matchId = 'X' ORDER BY score DESC

Answer 2

Before deciding you need to consider a few things:

1. How many rows are we talking about?

   • How many in the table as a whole
   • How many are part of the same tournament (assuming that you will have multiple tournaments in this table, and that points are assigned per tournament)

2. How many entries are added per tournament?

   This will tell us how many "update" operations would be hitting the table if you store the matchpoints in it.

3. How many times will people be viewing these entries?

   This will tell us how many times we would be calculating the matchpoints on the fly if using a View to hide the calculation, or keeping the calculation at the app layer, rather than storing in this table.

If you will INSERT 100 times but people will be Viewing (i.e. calculating it) every few seconds for the run of the tournament (think of people constantly reloading to see if their rank has changed as new entries are added), which might be thousands or tens of thousands of repeat calculations, then it would seem better to store.

It also sounds like the calculations are data-driven, at least the two examples so far are. In which case you can have a field to hold match_points and a stored procedure that accepts tournament_id and recalculates the match_points field WHERE tournament_id = @tournament_id. That stored procedure can be kicked off by an INSERT trigger and can be run manually (in the case of changing the formula). An update trigger, assuming it can tell you if the match_points field was a field that was updated, can run the stored procedure again for the tournament_id of the row that had its match_points field changed.

Of course, if you do have truly complex rules, then best to do the updates from the app, but how complicated can the formulas be? The two mentioned so far are actually better/faster done within the database (assuming that MySQL can do the the RANK windowing function).