Storing user's balance in a web application - single field or generate on the fly?
I am building a web application (in Django, though this isn't a Django-specific question). I will have a number of users and they will each have an account balance which will be incremented each time they deposit money with me (via PayPal) and decremented each time they use my application's API.
I am trying to decide which method would be more appropriate:
- Store the balance along with the user as a single integer field. When money comes in, this field is added to. When it goes out, it is subtracted from. Simple, but prone to errors.
- Generate the balance on the fly (with caching presumably) adding up all the payments and charges the user has made. A touch more complicated and slower however less prone to errors.
So, which is best or is there another way of doing it?
My opinion is that the first option is less prone to errors than the second. When you start relying on caching for that pretty important value you have to deal with cache synchronization. Obviously every time the users balance changes, you need to invalidate the cache and make sure that any read requests will be 100% accurate. If you do go the cache route, you just have to make sure that extra layer is very well coordinated.
What I suggest is that you store both a transaction history, and a balance total. The act of making a transaction needs to be atomic so that both the transaction history is made and the update of the balance total is executed, before anything else can read it. You then always have the option of reconciling the balance to the history, or reading and adding directly from the history.