Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    2

    how do they do it?

    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

  2. #2
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    Although it is not purist, in many cases a table with a current balance is maintained in parallel with the table of transactions.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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):
    Code:
    Date        Item                  Debit      Credit    Balance
    01/07/2004  Opening balance                            1000.00
    02/07/2004  Standing Order        100.00                900.00
    02/07/2004  Deposit                          2000.00   2900.00
    ...
    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.

  4. #4
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    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.
    Thanks,

    Matt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •