PHP/mySQL - regular recalcuation of benchmark values as new users submit their data

Question

I am confronted with a new kind of problem which I haven't encountered yet in my very young programming "career" and would like to know your opinion about how to tackle it best.

The situation A research application (php/mysql) gathers stress related health data from users. User gets a an analyses after filling in the questionnaire. Value for each parameter is transformed into a percentile value using a benchmark (mean and standard devitation of existing data set).

The task Since more and more ppl are filling in the questionnaire, there is the potential to make the benchmark values (mean/SD) more accurate by recalculating them using the new user data. I would like the database to regularly run a script that updates the benchmark values.

The question I've never used stored precedures so far and I only have a slight notion of what they are but somehow I have a feeling they could maybe help me with this? Or should I write the script as php and then set up a cron job?

[edit]After the first couple of answers it looks like cron is clearly the way to go.[/edit]

Answer 1

PHP set up as a cron job lets you keep it in your source code management system, and if you're using a database abstraction layer it'll be portable to other databases if you ever decide to switch. For those reasons, I tend to go with scripts over stored procedures.

Answer 2

If the volume of data is big enough that calculating it on the fly is too much, then either:

1. Cron job with php script to denormalise the totals
2. Trigger on inserts that increments totals

Answer 3

Go with the cron job way. Simple, solid, works. In the PHP/MySQL world I would say stored procedures are no-go.

Answer 4

The easiest way to make this work is probably to write a script in the same language your website is using (sounds like PHP) and call it from cron.

No need to make it more complicated than it needs to be by putting the logic in two places (your existing calculations and a stored procedure).

Answer 5

What you're considering could be done in a number of ways.

1. You could setup a trigger in your DB to recalculate the values whenever a new record is updated. You could store the code needed to update the values in a sproc if necessary.

2. You could write a PHP script and run it regularly via cron.

#1 will slow down inserts to your database but will make sure your data is always up to date. #2 may lock the tables while it updates the new values, and your data will only be accurate until the next update. #2 is much easier to back up, as the script can easily be stored in your versioning system, whereas you'd need to store the trigger and sproc creation scripts in whatever backup you'd make.

Obviously you'll have to weigh up your requirements before you pick a method.