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

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 3

If views and queries give you fast enough result then do not store in a separate field.

If it's not fast enough then store it into a separate field. Since this field will store redundant information it is very important to keep it synchronized.

Answer 4

That would be a function of how often you need to access this information. If its once in a while, then I'd say go ahead and recalculate it.

Answer 5

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 6

Use views and queries - you'd be suprised at how fast it'll run.

Answer 7

"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.

Answer 8

Everyone here's right. It does depend. But you can have the best of both worlds by using a view. Sounds like you might have a fairly small system, and dynamically calculating the balance will be the easiest thing to do. In order to keep it simple, I would define a single view that has the account data you want (calculated dynamically).

If you're going to need more performance than that, I would set up a trigger-based system for updating the balance to the main account table, then update the view behind the scenes so you don't need to change any other code. Just make sure you're using the right database isolation mode for either query, or you'll be in trouble! ;-)