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:
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:
- 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.
- 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.
- 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?