wow, it's time to get enligthment here, thx for very valuable
information. Too bad, i haven't yet a chance to buy your book. But I
will use all of my effort to buy the book for FB 2 :)
> 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.
>
I think, I learned someting here. I can not have different rules for a
single column.
So I have to change the conditions below to reduce ACID violation
(specially for C):
(1) Totals Value and Details Value was in single field.
(2) The Totals value was depend on Details value that is in the *same
field*.
> Those are all good reasons for having the server do the work. But
> the mechanism behind is not safe from external interferenced.
Actually my application restrict user to save multiple record to the
table, so they have to commit single record before they do insert,
update, or delete another record. I hope this will reduce ACID
violation. There are maximum 25 client will access the single database
server.
> 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.
hmmm.. I see (may be not all), my current SP calculate the total not
the server. By using COMPUTED BY I will eliminate the trouble that may
be come from not well tested SP. If I have to separate TOTAL column, I
am affraid that I have to separate them to 2 tables, Detail table and
Total table. What do you think?
> 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...
yes, the currency field may have very high number (billion, FYI my
currency is Indonesian Rupiah) for single record entry and could be
more than billion for Total field. I just need 2 digit after decimal
and it will highly used. What is proper data type for handling my
currency?
I hope, I can get more information about ACID violation that may
occure related to my current condition above.