A Financial transaction essentially moves funds from one account to another account. The transaction, therefore, affects the balances of the accounts involved. Let’s say Dennis and Stephen have accounts with ABZ Bank. Dennis has 50 in his account and Stephen has 20 in his account. Dennis transfers 10 from his account to Stephen’s account. This transfer is a Financial transaction that alters the balances of Dennis and Stephen. Dennis’s account balance will reduce by 10 to become 40 and Stephen’s balance will increase by 10 to become 30. We say, Dennis’s account was debited 10 and Stephen’s account was credited 10.
Our task is to build an Application that will allow users to perform transactions. At every point, the application should be able to tell us the correct balance of an account as well as what transactions have occurred on the account. So in our scenario above, once the transaction occurs, the application should be able to tell us that Dennis’ account balance is 40 and Stephen’s account balance is 60. The application should also be able to tell us that one transaction has taken place on Dennis’ account and it was a transfer of funds from Dennis to Stephen. Similarly, the application should be able to tell us that one transaction has taken place on Stephen’s account and it was a transfer from Dennis to Stephen.
Starting Solution
To be able to know what transactions have occurred on an account we need to store transactions when they occur. We need persistent storage. Let’s settle for a database so say Mysql. We create a database for our App and create a transactions table.
Transactions table
Column Name (Description)
transaction_id (primary key) debit_account_id credit_account_id transaction_details (a description of the transaction. e.g. a transfer) amount date_time (when transaction occurred)
With this transactions table, we can tell the transactions that have occurred on any account by selecting transactions that have that account as either the debit account or the credit account. That meets the requirement for telling what transactions have taken place on an account. What about knowing the current balance of the accounts? We can tell the current balance of an account by simply selecting all the credit transactions on the account and subtracting all the debit transactions on the account. So the other requirement is met as well.
Adding an Accounts Table
Although we can tell the current balance on an account by calculation, we can be more efficient by creating an accounts table and updating the balance of an account in the table when a transaction occurs. This way, if we need the balance of an account, we simply select it from the accounts table.
Accounts Table
Column (Description)
account_id (primary key) balance (the account's current balance)
Bear in mind that this means that the account balance must be updated when a transaction occurs.
When to Update The Account Balance
So with the Accounts Table involved, creating a transaction involves saving the transaction in the Transactions table and updating the account balance in the Accounts table. The question is when do you update the balance? Do you save the transaction and update the balance as one atomic operation? This means that unless the balance is updated in the Accounts Table, the transaction is not created. The advantage of this is that the account balance in the Accounts Table at all times accurately reflects the current balance of the account based on the transactions done on the account. The drawback is that the process of creating the transaction has one additional operation – the updating of the balance.
Do you update the account after saving the transaction? In this case, the updating of the balance will occur but is not required for the transaction to be created. The advantage is that the creating of a transaction is simply a matter of saving to the transactions table. The drawback is that, should the accounts table be used as the source of the account balance between the time a transaction occurs and when the balance is updated, the value will be wrong. Of course, should the updating of the balance fail, then the account table will have a wrong balance. These are all scenarios that will have to be catered for to ensure that the app works correctly.
Saving the transaction and updating the balance as one atomic operation wins and we will go with that. Doing it the other way does not result in any significant time savings. So now, when a transaction occurs, we save to the transactions table and update the accounts table in one atomic operation. When we need the current balance of an account, we query the accounts table. When we need to know what transactions have occurred on an account we select all transactions involving that account.
New Requirement – Tracking Balance Before And Balance After
In addition to knowing what transactions have occurred on an account, we want to be able to track the account balance before and after the transaction occurred. In the case of our starting scenario, when a request is made for the transactions on Dennis’ account, for instance, we must supply the information that, the balance of Dennis before the first transaction was 50 and the balance after the transaction was 40. Assuming Dennis did the same transfer the following day and so in total has performed two transactions, then we must supply Dennis’ second transaction as well and the fact that the balance before the second transaction was 40 and the balance after was 30.
To do this using our current data models, we will have to select the transactions from our transactions table and for each transaction determine the balance before and after the transaction. Let’s take the following two entries in our transactions table (The two transfers Dennis (account_id =1) did to Stephen(account_id=2)):
transaction_id, debit_account_id, credit_account_id, amount, transaction_details 1, 1, 2, 10, "Transfer: Dennis to Stephen" 2, 1, 2, 10, "Transfer: Dennis to Stephen"
To get the transactions that have occurred on Dennis’ account with the balance before and balance after:
transactions = select all from transactions where
credit_account_id=1 or debit_account_id=1 order by date ascending
balance_before = array[transactions.size]
balance_after = array[transactions.size]
current_balance = 0
current_index = 0
for (transaction in transactions){
balance_before[current_index] = current_balance
if (transaction.debit_account_id == 1){
current_balance = current_balance - transaction.amount
balance_after[current_index] == current_balance
}else if (transaction.credit_account_id == 1){
current_balance = current_balance + transaction.amount
balance_after[current_index] = current_balance
}
increment current_index
}
So now for each transaction, we can get the balance before and the balance after from the balance_before and the balance_after arrays respectively. Note that if we have a lot of transactions, supplying the transactions with the balance before and balance after for each transaction could take a while.
Alternative Method for Tracking Balance Before and Balance After
An alternative method for tracking the balance before and balance after for each transaction is to save that information when saving the transaction. The advantage here is that supplying the balance before and balance after for each transaction will be a simple matter of retrieving the transaction. To use this method we will alter our transaction table to now look like this:
transaction_id (primary key) debit_account_id credit_account_id transaction_details (a description of the transaction. e.g. a transfer) amount date_time (when transaction occurred) debit_account_balance_before debit_account_balance_after credit_account_balance_before credit_account_balance_after
This means that when creating a transaction, we will have to determine the balance before and the balance after for the accounts involved and save it as part of the transaction. Of course, the determination of the balance before and balance after adds to the cost involved in creating a transaction. It is, however, not significant.
Thus far, our transactions app can create a transaction, show the transaction history of accounts specifying the account balances before and after.
AOD Coding 