Hypothetical Bucket-Transaction Architecture

transaction_buckets_models.rb

class TransactionItem < ActiveRecord::Base
  has_many :transaction_bucket_item_links

  before_save :sub_account_balance
  before_destroy :sub_account_balance, :destroy_links
  after_save :add_account_balance
  
  def destroy_links
    TransactionBucketItemLink.destroy(:all, ["transaction_item_id = ?", self.id])
  end

  def buckets
    TransactionBucket.find_by_sql <<-SQL
SELECT transaction_buckets.*
FROM transaction_buckets
INNER JOIN transaction_bucket_item_links ON transaction_bucket_item_links.transaction_bucket_id = transaction_buckets.id
WHERE transaction_bucket_item_links.transaction_item_id = #{self.id}
SQL
  end

  def add_account_balance
    self.buckets.each do |bucket|
      bucket.update_balance( self.amount )
    end
  end

  def sub_account_balance
    self.buckets.each do |bucket|
      bucket.update_balance( -1 * self.amount )
    end
  end
end

class TransactionBucket < ActiveRecord::Base
  has_many :transaction_bucket_item_links, :dependent=>"destroy"
  belongs_to :parent_bucket, :local_key=>"parent_bucket_id", :class_name=>"TransactionBucket"
  
  def transaction_items
    TransactionItem.find_by_sql <<-SQL
SELECT transaction_items.*
FROM transaction_items
INNER JOIN transaction_bucket_item_links ON transaction_bucket_item_links.transaction_item_id = transaction_items.id
WHERE transaction_bucket_item_links.transaction_bucket_id = #{self.id}
SQL
  end
  
  def update_balance(delta)
    self.update_attributes { :cached_total => ( self.cached_total + delta ) }
    self.parent_bucket.update_balance(delta) #bubbles up the balance cache update
  end
  
end