At 03:31 PM 1/04/2006, you wrote:

>I think, I learned someting here. I can not have different rules for a
>single column.

You *can*, but it is not ideal from the point of view of consistency.

>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*.

Yes...and bearing in mind that another application, that you do not
have any control over...a database tool, or an app written by someone
with inadequate knowledge of your metadata, could break your plan and
still write inconsistent data.

> > Those are all good reasons for having the server do the work. But
> > the mechanism behind is not safe from external interference.
>
>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.

Yes, but a *different* application could break your plan. This kind
of vulnerability is parallel to the person in this list who wanted to
make some conditions in the database according to the name of the
application. To be ACID-compliant, a database's rules must be
independent of any application code.

> > 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.

A stored procedure *is* executed by 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?

That is generally more robust, since your design makes referential
integrity more complex. A treeview is not a database structure, it
is a user interface. A database *stores* data. It does not (and
should not) dictate to the user interface; and the user interface
equally should not dictate the database structure.

The "Q" in "SQL" means "query". The whole concept of a query
language is that you can generate output, via SQL, in any structure
that your user interface demands, including flattened structures such
as you have set up to "fit the shape" of your treeview UI.

> > 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.

For "consistency" violation, the question to ask is "could external
accidents corrupt my data?" Well, with your dual use value column,
the answer is not just "Yes", but also "and, sooner or later, it will happen."

For "durability" violation, ask "Could something happen to a value,
later in its life, that changes its meaning or content in an
unpredictable way?" And again, the answer here is Yes.

Because users can stuff up data in countless ways, we owe to the
owners of the data to make it as difficult as possible to do so.

./heLen