Credit system: history based or balance based?

Question

I am going to write a simple credit system that user can "add", "deduct" credits in the system. Currently I am thinking of two approaches.

1. Simple one: Store the user' credit as balance field in the database, and all actions ("add", "deduct") are logged but not used to compute the latest balance.

2. History based: Don't store the balance in database. The balance is computed by looking at the history of transactions, e.g. ("add", "deduct")

Both case would works I think, but I am looking to see if any caveat when designing such a system, particularly I am favoring the History based system.

Or, are there any reference implementation or open source module I am use?

Update: Or are there any Ruby/Rail based module like AuthLogic so I can plug and play into my existing code without reinventing the wheel (e.g. transaction, rollback, security etc)?

Saphra

Honestly I would implement both and just add a hook to History to update balance. This will alleviate the need to query History to determine balance every time. This seems especially useful for "deduct" items where you would want to make sure there are enoguh credit to deduct. – Longspur

If you are using MS SQL Server, you can use indexed view to let the DBMS automatically maintain the correct balance (based on history). – Limulus

Answer 1

Absolutely use both.

• The balance-based way gives you fast access to the current amount.

• The history-based way gives you auditing. The history table should store the transaction (as you describe), a timestamp, the balance before the transaction happened, and ideally a way to track the funds' source/destination.

See the Ruby Toolbox for bookkeeping and Plutus double-entry bookkeeping gem.

In addition, if your credit system may affect users, then I recommend also using logging, and ideally read about secure log verification and provable timestamp chaining.

• For logging details see: techniques for ensuring verifiability of event log files.

• For open source code that does credit, you may want to look into: http://www.gnucash.org/

Soapwort

Absolutely. Without a ledger you have zero trust in the values in the system. With a ledger you can validate that the balances are accurate. – Stumpy

I've worked on a system that handles balances/accounting for a small financial services company, and can vouch that this answer is 100% correct. – Was

Answer 2

Adding and deducting credits implies that you might also need to be aware of where these credits came from and where they went. Any time you get into a situation like this, whether it is with currency or some other numerical quantity that needs to be tracked and accounted for, you should consider using a double entry accounting pattern.

This pattern has worked for centuries and gives you all of the functionality you need to be able to see what your balances are and how they got to be that way:

• Audit log of all transactions (including sources and sinks of "funds")
• Running balance of all accounts over time (if you choose to record it)
• Easy validation of the correctness of records
• Ability to "write-once" - no updates means no tampering

If you aren't familiar with the details, start here: Double Entry Bookkeeping or ask anyone who has taken an introductory course in bookkeeping.

You asked for a Ruby on Rails open source solution that you could plug and play into your application. You can use Plutus. Here is an excerpt from the description of this project on Github:

The plutus plugin provides a complete double entry accounting system for use in any Ruby on Rails application. The plugin follows general Double Entry Bookkeeping practices. ... Plutus consists of tables that maintain your accounts, entries and debits and credits. Each entry can have many debits and credits. The entry table, which records your business transactions is, essentially, your accounting Journal.

Trepidation

Plus one for double entry accounting. A solved problem. – Wira

Answer 3

yes, use both.

• On top of that, you'll sometime need to reverse a transaction/ transactions.When doing that, create a new reversed transaction to notate the money transfer.

• sometimes, You'll need to unify several transactions under one roof. I suggest to create a third table called 'tokens' that will be the payments manager and you'll unify those grouped transactions under that token.

  token.transactions = (select * from transactions t where t.token = "123") for example

Tango