Can I store the sum of row's child rows' field in a parent field?

[benmanns]

Sorry if this has been posted before; I am not sure what to search for.

I have a table of users, `users`.
In the table `users` there is a row which stores clicks, `users`.`clicks`.
Each user has it's own referrer, `users`.`referrer_id` which refrences the referring user's id.
In the referring user's row, I would like a field to store referral clicks, `users`.`referral_clicks`.
I would like `users`.`referral_clicks` to be updated each time that a child user's `users`.`clicks` field is changed.

Is this easily possible in MySQL?

Is this more efficient than something like:

SELECT SUM(`users`.`clicks`) FROM `users` WHERE `users`.`referrer_id` = 1;

as opposed to:

SELECT `users`.`referral_clicks`) FROM `users` WHERE `users`.`id` = 1;

Thanks,

Ben Manns

[Atli]

Hi.

As a general rule, it is best not to store the same piece of data twice.
You already have the information you need stored in the `user`.`clicks` field, so adding a second field isn't needed.

I would go with the first method you mentioned, rather than adding unnecessary data. I doubt that you will ever notice the added overhead of using the SUM function, if there even is any.

But, if you choose to add the second field, you could try using Triggers. That way you could have it automatically update the referrer_clicks field whenever a clicks field is modified.

[benmanns]

Thanks. It looks like those are what I am looking for.

I'm going to be storing individual transactions in which thousands of users can have anywhere between 5 and 100 per day. I am thinking that there might be some problem if I have to sum the transactions (up to 36,500 per user per year) every time I want to display or check their balance.

Would overhead still not be a problem, or am I correct in wanting to store the data in the parent user?