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?