3

Possible Duplicate:
Database design: Calculating the Account Balance
should the user's Account balance be stored in the database or calculated dynamically?

Where is the best place to store the user's account balance and why?

1) "Transaction" table, as calculated at the time when the transaction occurred or

2) "Account" Table, updated every time the user login or at the time the transaction occurred.

Community
  • 1
  • 1
001
  • 62,807
  • 94
  • 230
  • 350
  • 1
    I would say account table, as the account balance should be tied in with the account information? –  Mar 22 '12 at 12:46

1 Answers1

1

According normalisation rules, you should not store something that you can calculate from other data you have.

Having said that, which bank is there not to store it? This is where you de-normalise to improve performance since balance is used in many parts of the system and calculating on all transactions everytime you need the balance is not possible.

There is an alternative to have a consolidated value which gets calculated overnight but then adapt the value based on transactions since last consolidation.

Aliostad
  • 80,612
  • 21
  • 160
  • 208
  • It is not only not possible, it is also not legal. you do not really need account value, but with number AND TIMESTAMP. YOu need to know what teh value was at that point in time for auditing purposes. – TomTom Mar 22 '12 at 12:55
  • @TomTom thanks for the legal insight as well. I was coming purely from technical side but you have to consider legal too. – Aliostad Mar 22 '12 at 12:57
  • 2
    Yes. Remember account statements. The one from 13of of january must look the same EVEN IF you cancel a transaction some weeks later ;) Ths is why you NEVER correct values, alwaays put up counter transactions. Plus you may have transaction NOT VALID YET (due date in future) ;) – TomTom Mar 22 '12 at 12:59
  • 1
    Since the transactions are calculated at the time the transaction is created, what happens to "pending" transactions? (what happens if pending transaction fails, or pending transactions are completed)? – 001 Mar 23 '12 at 00:01