Calculating running balance from join table [SQL Database Design]

Question

Let's say I have three tables

TRANSACTIONS

• amount
• date

RECORDS

• amount
• date

CUSTOM_RECORDS

• amount
• date

(Let's just say there are many other fields to justify splitting of these tables)

To calculate running balance I have two methods

-------------METHOD 1 -------------

Heavy on READ, Light on WRITE

Whenever we read, just join the table, sort by date and calculate the running balance.

PRO

• Write is easy, just write into each table

CON

• Reading is very heavy, the calculation needs to be done on each read. It is very strange to be querying (from let's say a span of 1 week) and to have the calculation done for ALL the records. If I query for 10 records then calculation needs to be done for 1 million records to be able to know the 10 record balance.

-------------METHOD 2 -------------

Heavy on WRITE, Light on READ

I have another table

FINAL_TABLE

• date
• amount
• running balance

Whenever I write, I refresh this table and calculate all the running balance again.

PRO

• Read is easy, running balance already computed.
• Querying between time period is as easy as extracting the date between the time span from the FINAL_TABLE

CON

• Write is really slow, each write on any of the Three tables mean refreshing a whole FINAL_TABLE table!

Why didn't I just reuse the latest running balance? This can occur if the entry is a guarantee to be chronological in real life. However, sometimes entry might be added late.

Currently, I am using Method 2 and every time a client save/update a row into any of the three tables, the server freeze as it tries to refresh and compute the FINAL_TABLE. Obviously, this is not very scalable.

Method 1 is also not very scalable in term of querying. I would have to calculate running balance from the beginning of time in order to know the running balance of last week.

Both Method is not very scalable. What is a good design to ensure scalability and relatively fast performance on READ and WRITE? What method does the bank use to keep track of running balance?

Answer 1

It depends.

Suppose you have a report like transaction report where accounts' running balance will be shown. If you want to show real time data then always method 1 will be preferable. And I will suggest to use Quirky Update for this rather than using cursors, loops, sub-queries or recursions.

On the other hand, if you don't need real time running total then you could have use method 2 with a little customization. I will not support updating Final Table while you made a transaction. Rather than I will suggest to update it with interval schedule. Depending on your traffic or load you may update the running total after a interval.

And for real time I will discourage using method 2 as it will make your transaction costly.

To make your method 1 faster here is some link.

Calculating Running Total

Quirky Update

Quirky Update Performance

Halloween Protection

Create Table AccBalance
(
    AccountNO PK,
    Balance
)

Create Table AccDateWiseCumBalance
(
    AccountNO PK,
    SystemDate PK,
    Cumulative Balance
)

First table will be updated by each transaction will keep real time balance but not any history.

Second table keep account and date wise cumulative balance which will be updated at each day end.

So if you need up to previous date cumulative balance you will retrieve data from second table.

And if you need up to current date cumulative balance you will retrieve data from second table up to day before current date and retrieve current date data from first table.