PHP/mySQL-新用户提交数据后定期重新计算基准值 [英] PHP/mySQL - regular recalculation of benchmark values as new users submit their data

问题描述

我遇到了一种新的问题，这在我很小的编程职业"中还没有遇到过，并且想知道您对如何最好地解决它的意见.

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.

情况 一个研究应用程序(php/mysql)从用户那里收集压力相关的健康数据.用户填写调查表后将进行分析.使用基准(现有数据集的均值和标准偏差)将每个参数的值转换为百分位值.

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.

问题 到目前为止，我从未使用过存储过程，而对它们是什么只有一个小小的概念，但是我有某种感觉，他们可能会帮助我?还是应该将脚本编写为php，然后设置cron作业?

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]在回答了最初的几个问题之后，cron显然是必经之路.[/edit]

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

推荐答案

您正在考虑的方法可以通过多种方式完成.

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

1. 您可以在数据库中设置触发器，以便在更新新记录时重新计算值.如有必要，您可以将更新值所需的代码存储在sproc中.

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.

您可以编写一个PHP脚本并通过cron定期运行它.

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

#1将减慢对数据库的插入速度，但会确保您的数据始终是 . #2可能会在更新新值时锁定表，并且您的数据将仅在下一次更新之前才是准确的. #2备份起来容易得多，因为脚本可以轻松存储在版本控制系统中，而您需要将触发器和proc创建脚本存储在要进行的备份中.

#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.

这篇关于PHP/mySQL-新用户提交数据后定期重新计算基准值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！