Ensuring the integrity of a user's 'balance' in a Rails application

Question

'Balance' represents the difference between the sum of credits, and the sum of debits. The following simplified schema represents an example:

Schema http://dl.dropbox.com/u/10177092/Personal/stackoverflow_question.png

Note the 'balance' column. I have identified three approaches to determining balance:

(1) When updating the credit or debit table (and updating the balance), wrap the code in a Transaction block. For example:

ActiveRecord::Base.transaction do
  current_user.credits.credit(100)
  current_user.balance.increment(100)
end

(2) Don't include a 'balance' column - instead, calculate the balance each time it is requested:

credit = current_user.credits.sum('amount')
debit = current_user.debits.sum('amount')
balance = credits - debits

(3) Create a database view.

I would appreciate advice regarding:

• The advantages and disadvantages of each approach.
• Alternative approaches.

It seems to me like calculating balance (e.g. 2. and 3.) best ensure the integrity of the value. However, I am concerned (2) may become inefficient as users engage in additional transactions. Database views seem like a theoretically sound option, but I don't believe rails_sql_views supports Rails 3, and I have noticed several threads that imply database views are undesirable/often associated with legacy databases.

Answer 1

Create a Movement model.

user_id, quantity, balance, updated_at, created_at

Define add_credit, add_debit and balance instance methods in User model:

 def add_credit(quantity)   
    self.movements.create :quantity => quantity 
 end

 def add_debit(quantity)
    self.movements.create :quantity => -quantity
end

def balance
  self.movements.last.balance
end

Use an after_save callback in Movement model:

before_save :update_balance

def update_balance
    if balance
       balance = self.user.movements.last.balance + self.quantity 
    else
       balance = self.quantity # First movement
    end
end

This way you assure that when credit or debit is added, the balance is updated because it's contained in the same credit/debit movement object.