Re: SQL Theory/performance

Paul Tuckfield wrote:
>
> 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 :)

This is exactly the issue. I realise that there is no set answer but I was wodering what the generally excepted view is on intra-record (in the same record) business rules. Has anyone tried the edit-post-update that I've suggested here?

-- 
John Parrott
Elysium Financial Systems
spo_at_interlog.com
http://www.interlog.com/~spo