PHP & mySQL: Do I store the Balance column or generate it dynamically whenever the application needs it?

Question

Hi all,

WHAT I ALREADY HAVE:

Within my application, I have Credit and Debit columns. I am using the Column type as Decimal 8,2 with Default value being 0.00, for both the columns, in the table. When a user gets credited, with say, $50, the credit column gets the value as 50.00 and Debit gets populated as 0.00.

WHAT IAM TRYING TO ACHIVE:

Now I also want to show the balance for a transaction in a third column, adjacent to the Debit column in the frontend. So do I create a new column called Balance with the Column type as Decimal 8,2 and Default value being 0.00 and just populate the Balance as (credit-debit) within my code? Or do I compute the Balance values dynamically every time a user visits this page? Which method would be the best and why?

The recent transactions show first. Here's an example of how I want my front end to work/look:

PK      Credit Debit Balance
3        0.00  20.00  35.00
2       10.00   5.00  55.00
1       50.00   0.00  50.00
Totals: 60.00  25.00  35.00    

The PK is the auto-inc, Primary Key. What would be the code to be able to produce the output as indicated above?

Thank you in advance.

Answer 1

Hi,

I think it can be done both ways, but my vote is for keeping the balance updated. it's a bit easier in lots of situations IMO - an account balance could be the result of years of activity and could include transactions which have already been archived out of the database. A checking account could potentially be 10 years... Also, reading, checking, querying on, and displaying the balance are likely to be more common in the app than modifying the balance, so it would simplify that logic.

But sure, there are cases where the other way makes sense.

Note, you could also just have one column for the transaction amount (positive is credit, negative is debit).

Answer 2

You should compute the Balance column dynamically each time, and do not store in database.

If you have it stored, and if you have to edit a past record, you have to update records of your balance column, that is a pain to compute that time and introduces hangs(write locks)