Bank transaction table design

Question

I’m making a little site which may, if I’m lucky, hold a little of data with regards banking transactions. This is version 2 of a previous learning project.

I will have a table with transactions. Based on the type (payment, deposit or transfer) the value will be negative or positive.

Best cases scenario, I have 10,000 users and they each have 1500 transactions per year and 8 years of data. 120,000,000. One primary bit of info stored and shown a lot is Current Balance. But also, the running total shown on multiple different types of statement and transaction screens.

So, should I break the rules and store the running total or transaction on every row, and recalculate all transactions that an insert/update may effect? I mean, that’s usually just the last weeks transactions.

Or store a separate DailyBalance table, with a date and account number as the pk with that days transactions added?

Or simply calculate on the fly?

Any advice on structure for performance as data integrity would be handy.

I’m using SQL Server 2016. I’d have a pk on the transactionid. And an index on accountId As well as one on transactionDate probably.

Maybe clustered on Id, accountId?

Answer 1

The typical design here is to have a Transaction table and an Account table

The Account table has the current balance, as of the last transaction that has "posted". A modern "online" system will post transactions immediately, but older systems would capture transactions online, but run "batch" to post the transactions later.

PK on the transaction table would normally be (AccountId,TransactionId), which balances the cost of insert and the cost of pulling the transactions for a single account.

I don't think there's a rule controlling whether you also store the running balance on the transaction table. Normalization does not require you to recalculate the account balance or the running balances. Balance is a proper attribute of Account, regardless of whether it can be derived from rows in another table. And you don't necessarily have to perform these calculations as you insert transactions.