Re: database design question

Hi Seerge,

Serge Baduk wrote:
>
> Hi,
>
> I have a general database design question: how should I implement the
> bank account balance, if I were to build a bank database? Currently,
> it seems that I have the following three options:
>
> 1. BALANCE VIEW
> This means that the balance will be recalculated from the
> TRANSACTION table on every query.
> 2. BALANCE TABLE
> This means that every UPDATE, DELETE or INSERT operation on the
> TRANSACTION table will trigger an update, delete or insert operation
> to the corresponding BALANCE TABLE row.
> 3. BALANCE MATERIALIZED VIEW
> This means that the database engine will decide for itself when to
> recalculate the balance field in the BALANCE MATERIALIZED VIEW from
> the TRANSACTION table.
>
> Given that both performance and data integrity are critical, what
> would you think is the best approach? Or how is BALANCE implemented in
> existing banking systems?

We are satisified with the second option: The balance is just another attribute of the accounts table. A row trigger maintains its value after inserts. Of course this can be done for updates or deletes as well, but normally these modifications should be prohibited in a real world bank application, since transactions correspond to events.

Uwe

-- 
Uwe Schneider       | Telefon +49 7244 / 609504
Karlsdorfer Str. 31 | Mail    uwe_at_richard-schneider.de
DE-76356 Weingarten | http://www.richard-schneider.de/uwe
Linux - OS al dente!