Basic DB design question- if a calculation query keeps happening over and over, better to have a separate table for it?

Question

Practicing web development by making a social network. Users can earn points for performing certain activities, so I have a PointHistory table for each User where point transactions are stored. To calculate the point balance, it makes more sense to have a dedicated PointBalance table that gets updated every time there is a change in a user's point balance, right?

Example scenario: userA earns 10 points -userA's PointHistory table is updated, transaction1 is recorded as +10 -userA's PointBalance table is updated to add 10 to the pointTotal

The alternative would be to have an algorithm that runs through the PointHistory table and calculates the point balance every time a point transaction occurs.

The dedicated table sounds like it would take fewer resources- or am I missing something?

Answer 1

Normally, if the information can be derived from existing persisted data, it is better to recalculate than to save calculation results. This way, the recalculated data will always reflect persisted data and be accurate if the calculation changes.

Of course, if there is a performance issue with this calculation, you may want to consider storing the result. However, you need to remember to updated this whenever any field it depends on changes, or if the calculation itself changes.

It is difficult to say which scenario will be easier on resources (you only mention memory, though CPU, IO and Network usage all also come into play) - you should go with one solution with the idea of measuring as you go along and changing things if needed. Make sure your code is supple enough to adapt to such a change.

Answer 2

That's actually a very common balancing act between normalization and performance. These are the common solutions:

Just calculate it on the fly if it's fast enough.

Have each history insert lock the calculated field and add itself to it. This is good if history isn't added very often but can slow the system with lock contention is there are a lot of history inserts at the same time.

Have the calculated record store the total and the last history record in the total. When a request for the total comes in, add in any new history records since the last request and store it.