3

Imagine you need implement a system that records your checking account transactions over time. Generally, transactions are grouped by month on your statement. There is a beginning balance, and an end balance for each month. Each transaction between the start and end date increases or decreases the balance. You want to be able to tell your current balance.

What is a good way of representing this data? I came up with these two basic choices:

  • one big table for all the transactions. To determine the current balance, sum up all transactions. Big downside: the longer the list of transaction grows, the slower it gets. So I suspect that is not what banks would do or anybody who implements this for reals.

  • same as previous, but with additional "snapshot" information, e.g. a "balance at end of month" table. This way, to determine the current balance, only the transactions of the current month need to be added to the most recent snapshot, which is much faster. The downside is that if a transaction comes in late, or was recorded incorrectly in the past, the snapshots have to be updated. (This should not happen for a bank too often -- I hope --, but might for others if your accounting period -- like for your private life -- is longer than a month)

I was wondering how real-world systems do this.

Johannes Ernst
  • 3,072
  • 3
  • 42
  • 56

2 Answers2

8

Most banks do the following for Transactions and Balances:

Transactions (acct,date,tran_code,amount....)
Balances (acct, date, ledger_Bal,avail_bal....)

Balances are typically captured daily, and usually reflect the ending ledger/available balances for the day (processed at end of day, using sum of transactions and prior day balance to calculate new balance). Daily balances greatly reduce the overhead associated with processes that need to be run daily (like calculation of overdraft fees).

There are numerous other tables, of course, for other relevant information. Interestingly balance tables are usually larger than Transaction tables, as most systems process daily balances regardless of whether there are transactions on that day.

Hart CO
  • 34,064
  • 6
  • 48
  • 63
1

You could have probably at the very less three main tables, namely, one for Account, one for Transactions and one for Corrections.

And so you could probably have these tables along with the fields as shown below:

Account (AcctNo, AcctName, Address, Street, State, Zip, AcctCreationDate, LastBalance)
Transactions (TransNo, AcctNo, TransDate, TransType, TransAmount, EndingBalance, AgentID) 
TransactionCorrection (TransCorrID, RefTransNo, RefAcctNo, forTransDate, AgentID)

You could probably add the User who made the transaction and corrections as well.

Agent(AgentID, AgentLastname, AgentFirstname)
Edper
  • 9,144
  • 1
  • 27
  • 46