Re: SQL Theory/performance

In article <324D426A.2107_at_worldnet.att.net>, bmaclean_at_worldnet.att.net says...
> John Parrott wrote:

 . . . .
> > I have a record where the values of fields in the records depend on
> > other fields in the same record.
> >
> > Eg. (Price * Quantity) + Tax = Amount
> >
> > If I have a table with all four fields, changing any one would mean the
> > others have to change.

 . . .
> Since storage of derived data is a form of denormalization, I always start with the plan of NOT storing
> derived data, and then denormalize only if I can find very specific reasons. If you decide not to store the
> derived data, you can create a VIEW that will show the derived columns.
>
> Regarding your front end display, I am not familiar with Delphi, but I would imagine that you can just locate
> a calculated field on the screen that shows the derived amount to the user.
>

This was my suggestion too but the main issue, I realized after a followup, was keeping the business rules in the database, but reflecting the derived values instantaneously on the front end. I think this would be a problem even if the data were more normalized, because you'd still have to put the rule, the formula for ammount in this case, in the front end to get the instantaneous calculation of ammount as the user enters or changes the other fields. The only other alternative seems to be to post then retrieve the record on each field edit. (I assumed the above example was just a simple example, not his real problem)

It seems like the only choices are replicating the business rules in the front end or posting and re- retrieving a record on each field update. On the issue of re-retrieving a row to show business rules applied to derived fields . . . At least you can count on the row being in cache since you've just updated it :) Received on Mon Sep 30 1996 - 00:00:00 CEST