hey!

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?

thanks in advance

Tom

Although it is not purist, in many cases a table with a current balance is maintained in parallel with the table of transactions.

Yes, though I suspect the stored account balance may be at a point in time (e.g. month end), and that the current balance is derived as something like:

account.balance + sum(transaction.amount)
where transaction.date > account.balance_date

That would be consistent with what you see on a bank statement (even with online banking):
The "opening balance" is the saved balance as at 01/07/2004, and the current balance is that plus the sum of transactions since that date.

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.