Best practice for storing credit balance in web application

Question

I am creating a web application where users can buy credits (using real money) and spend them on things.

I will need to keep a history of every time a user buys or spends credits.

I will often need to know the user's current credit balance.

I usually work to the principle that storing the same data twice is bad practice i.e. since I have a history of all increases and decreases in credit balance, I should not store the balance itself as a seperate field.

However, it seems overkill to calculate this balance each time the user opens up a new page (which will display their current balance). On the other hand, it seems wrong to store it either in the database, or in memory on the webserver, just to prevent the system having to work it out each time.

Does anyone have any experience of this and / or know what the recommended best practices are for this situation?

Answer 1

I've worked on several projects with similar requirements, and there's nothing wrong with calculating the current balance on the fly - it's the least error prone solution, and this kind of query is what most databases are designed to do; unless you're working on a site with Facebook-levels of traffic, something like "select sum(transactionValue) from transactions where userID = ?" should be blazingly fast.

Storing the balance somewhere else - duplicating it, in effect - risks introducing subtle bugs and discrepancies, with the "cache" value getting out of sync with the underlying transactions; the amount of work required to get this right is probably significant.