I am currently building a social network type application on-top of MySQL and have run into a problem I can't wrap my head around. In my web application each user has a field called "weight" and this weight is calculated by a few different factors.
- The number of connections a user has
- The number of connections a users friend has
Essentially a users weight is affected by their network and the weight of each of their friends. I need to average out the weight score of a users network and then use that to update a users self weight score. I would like it to be a living thing where the weight can change at any time if the network average changes for a user (but performance wise this might not be a good idea).
I was thinking once or twice per day maybe via a scheduled trigger, a users weighting score could be calculated. My question is, how can I best implement something like this keeping in mind if I have 100,000 users and or millions in future, the performance of updating that many rows. Am I thinking about this all wrong?
Thank you in advance.