How to keep redundant data in a db properly updated?

Generally we don't want redundant data in a db, but sometimes it's necessary for performance issues, and let's take the EE database for example. It's 2 million solutions + now, and let's say we are looking for point Sums for a particular user and zone. Could ideally be done with Sum(UserPoints) Where User=@User And Zone=@Zone. But - I guess that for a very large db like this it would be better to use redundant data in some way: like a TotalUserPoints DataTable with the necessary columns (User, Zone, Year, Month, Date or something.) Right?

Now, the question is - how to keep the redundant table updated in a proper way, so that the TotalUserPoints table doesn't get out of sync with the actual q&a-table.

If the q&a-table only "inserts" points it would be rather easy, just add the points to the TotalUserPoints table. But when things get more complex like changing already given points et cetera, then how to keep the points synced? (In the EE scenario this might not be so complex, but lets say it is several ways of changing already given points, and from several kinds of processes).

Would you spend most time coding Updates that update TotalUserPoints table correctly for each change in the Q&A-table?
or would you write a function that regurarly runs Sum()-queries to rewrite the rows in TotalUserPoints?

If you have some time, please give me some feedback on this.

Thanx

Start Free Trial