Helen Borrie wrote:

> 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

Hello,

I'm just considering the COMPUTED BY field versus SP when calculating
totals, from the performance pov and also from altering structure
when/if needed
I think that using COMPUTED BY SUM(.... FROM detailtable WHERE ...) is
slower than a SP. Considering that some tables usually have more
calculated fields from detail tables, isn't a COMPUTED BY approach
executing virtually the same query multiple times?
As an example a master table has 3 sum columns (eg TOTAL,
TOTAL_TAX,TOTAL_WITH_TAX), each of then calculated as as SUM from detail
table. When executing a SELECT * FROM master isn't the server executing
the SELECT SUM(price) FROM detail and then SELECT SUM(tax) FROM detail
... so on?
I think that a SP with something like SELECT SUM(price),SUM(tax)... FROM
detail where FK_ID=:PK_ID) and left join in the SELECT * from master
will be mode beneficial.
Also will be mode easier to change a SP rather than drop and recreate
COMPUTED BY columns. I know that it isn't something that is is done
daily, but we had to do something like this changing some COMPUTED BY
and it wasn't a pleasure trip, not mentioning how many times they were
referenced. Well, clients...

What is your opinion about this?

Radu