Cache a complex calculation in Rails 3 model

I'm new to Ruby/Rails, so this is possibly (hopefully) a simple question that I just dont know the answer to.

I am implementing an accounting/billing system in Rails, and I'm trying to keep track of the running balance after each transaction in order to display it in the view as below:

Date     Description     Charges($)   Credits($)    Balance($)

Mar 2    Activity C       $4.00                       -$7.50

Feb 25   Payment for Jan                 $8.00        -$3.50

Feb 23   Activity B       $1.50                      -$11.50

Feb 20   Activity A       $2.00                      -$10.00

Each transaction (also known as line item) is stored in the database, with all the values above (Date, Description, Amount) except for the Balance. I can't store the balance for each transaction in the database because it may change if something happens to an earlier transaction (a payment that was posted subsequently failed later for example). So I need to calculate it on the fly for each line item, and the value for the Balance for a line item depends on the value for the line item before it (Balance = Balance of Prev Line Item + Amount for this Line Item, i.e.)

So here's my question. My current (inept) way of doing it is that in my LineItem model, I have a balance method which looks like :

def balance
  prev_balance = 0
  #get previo开发者_运维知识库us line items balance if it exists.
  last_line_item = Billing::LineItem.get_last_line_item_for_a_ledger(self.issue_date,self.ledger_item_id)

  if last_line_item
    prev_balance = last_line_item.balance
    .. some other stuff...
 end

 prev_balance + (-1*net_amount) # net_amount is the amount for the current line item
end

This is super costly and my view takes forever to load since I'm calculating the prev line item's balance again and again and again. Whats a better way to do this?

You're basically paying a price for not wanting to store the balance in each transaction. You could optimize your database with indices and use caches etc; but fundamentally you'll run into the problem that calculating a balance will take a long time, if you have lots of transactions.

Keep in mind that you'll continue to get new transactions, and your problem will thus get worse over time.

You could consider several design alternatives. First, like Douglas Lise mentioned, you could store the balance in each transaction. If an earlier dated transaction comes in, it means you may have to do an update of several transaction since that date. However, this has an upper-bound (depending on how "old" transactions you want to allow), so it has a reasonable worst-case behavior.

Alternatively, you can do a reconciliation step. Every month you "close the books" on transactions older than X weeks. After reconciliation you store the Balance you calculated. In def balance you now use your existing logic, but also refer to "balance as of the previous reconciliation". This again, provides a reasonable and predictable worst-case scenario.