At 12:49 PM 1/04/2006, you wrote:

>I need to create a tree structure for my accounting application
>like below:
>
>Account Type Balance
>----------------- ------- --------
>Asset Header 1000
> Current Asset Header 500
> Cash Detail 250
> Inventory Detail 250
> Fixed Asset Header 500
> Furniture Detail 250
> Building Detail 250
>
>Liability Header 500
> Current Liability Header 500
> Account Payable Detail 250
> Tax Payable Detail 250
>
>Currently, I have succeded to create database structure and show them
>in VirtualTreeView. I use Delphi. I use latest stable FB version.
>
>My question is related to the table structure. All account_id,
>account_parrent_id, and balance_value are stored in the same table.
>The table not just store the Details value but also the Headers value
>(sum of Details). Some SP's has been created to fill the Headers value
>automatically. These SP's executed when the Details Account inserted,
>deleted, or updated. These SP's also insert and update Header
>accounts and its values when needed.
>
>I know this is not comply with normalization technique (where you have
>to avoid to store the sum value).

It's not so much not complying with normalization rules as much as
risking breakage from the point of view of consistency and
durability. Populating a "concrete" field by way of a trigger
doesn't eliminate the risk that an application could update the
parent record directly, i.e. you have different rules for a single
column, depending on the value in another field.

>But, my consideration are:
>
>(1) I don't have to code the Headers value (sum of Details) in the
>client application.
>
>(2) There are no calculation in the client application.
>
>(3) VirtualTreeView will show the data tree very fast because it had
>just fetch the plain data.
>
>(4) There are no calculation in report designer because it had just
>fetch the plain data.

Those are all good reasons for having the server do the work. But
the mechanism behind is not safe from external interferenced.

>Is it still good practise to store Headers value (sum of Details) in
>the table regarding my consideration?

The *principle* not seriously bad; it's your implementation that is
the problem.
You should have a separate TOTAL column in which to store
totals. And you should use a COMPUTED BY definition for this column,
not a concrete definition that can be modified by idiots and malfaisants.

>My database has not yet tested
>with large data and I use double precision type to store the currency
>value.

Worse, you probably haven't yet tested it with human beings. That's
where ACID violations tend to turn your database into a compost heap
fairly quickly.

Take care with using DP for currency values, too. You can get
serious precision errors in accounting systems that include false
assumptions about floating point arithmetic. Exact numerics are the
best choice for bean-counting, because 3 * 4 is always exactly
12. On the other hand, if your accounting is in a currency that is
likely to have very high numbers involved, exact numeric may not have
enough precision to handle it...you can work around that, in many cases...

./heLen