I need to model a small bank system for a project, but I am having some doubts as to the schema.
I need to have a bank account entity, no problems there. My problem is how to represent a transaction. I thought I could have a transaction table related to the account table, and when a new transaction is completed, insert a row into the transaction table. This seems reasonable, but I thought after maybe 12 months or so of transactions, a query to work out the balance of the account may slow down (simply suming the 'amount' field). Is this the way it's done in real life? Or is there some kind of monthly final balance or something?
Andrew's suggestion is what I've seen in my consulting work in the financial sector. Of course, they still maintain all the line items for a very, very long time (because they need to continually ensure that the balance is correct) but every X period of time they move the xacts to a history table and then roll-up their balance.