Best way to maintain a customer's account balance

Question

Is it better to have a field in the database that stores the customers account balance or use views and queries to generate the information.

Answer 1

For performance, I'd say both. Keep a log of all the transactions (in a separate table) but maintain a field in the customer record that stores the current balance that gets refreshed when you add more transactions.

Answer 2

One project I worked on we stored the current balance in one field, and all the transactions on another table, but because of the level of importance that this project had on the data being perfect 100% (or better) or the time, we also stored a hash of the balance in another field and the hash was compared each time it was called to ensure integrity, it if did not match up it was recalculated from the transactions table, and re-hashed then sent to the customer support dept. for review.

Answer 3

I think this depends on a lot of factors, how often will you be accessing the balance, how complex is it to recalculate it everytime you need it. What are the overheads of having views, etc.

Purely on the face of the information you have given I would store the value as recalculating it from scratch each time could be a pain.

Answer 4

"It depends". Most often, you want to avoid derived data. However, there are cases where having the derived total is justified.

Case in point:

I worked on a credit database application, where the balance was comprised of many different things, and different business rules over time. For example, the "balance" was actually a sum of different balances from different buckets, such as Principal, Fees, etc.

As transactions were posted, they were allocated to different buckets according to business rules. Fees went to the fees bucket. Purchases, credits, and debits went to the principal bucket. These allocations and rules were subject to change over time.

Imagine querying 100,000 customer balances on the fly in the face of changing business rules over time. This is a solid case where having the derived value actually make sense. We maintained a set of algorithms to "rewind" the account and reconstruct the balance chronologically for audit and verification purposes, but it was nothing you would want to do for large sets.