Results 1 to 2 of 2
Apr 16, 2008, 23:01 #1
How to incorporate 'balance remaining' into database schema
Imagine a system that has registered users, and those users 'top up' their account (by credit card or some other means). Then, as they consume the system's service, their 'balance remaining' is reduced by the appropriate amount.
What's the best way to store the 'balance remaining' in the database? I see two ways of doing it;
Method 1: Store all financial transactions in a table. To calculate the user's balance, simply sum all of the transactions for that user (e.g. +10.00, -2.50, -2.50 = 5.00). Pros: all financial information is recorded only once. Cons: calculating the balance could become resource intensive once the transaction database becomes large.
Method 2: Store all financial transactions in a table, but also store the balance once against the user. The balance would have to be recalculated every time a transaction is entered. Pros: easy to get the balance. Cons: would have to check to make sure the balance matches what the transactions add up to.
Any techinques I've missed? Any advice?
Apr 17, 2008, 04:56 #2
- Join Date
- Jul 2002
- Toronto, Canada
- 52 Post(s)
- 2 Thread(s)
i think you've covered the options nicely
i'd use method 2
if you're a user submitting a transaction, you are already (or should be) accustomed to a slightly slower response time, because you're submitting a transaction, and updates just take longer, everybody knows that
so taking a couple more milliseconds to update one more table is not going to be noticed, since it's only that one user
method 1 suffers from the flaw that if lots of people look at their balance, the overall system performance suffers, and everybody is affected