Cacluated Field

I am designing an object model and DB and I can't decide where to put
calculated fields... Should it be in the database or the middle-tier?

In other words, if I have an OrderItem on an Order and there are two
columns called "Quantity" and "Cost" I also will want "TotalCost" =
"Quantity x "Cost"... Do I have the "GET" stored proc return this
caculcated value or just create the property in my middle-tier and have
it calculate it there?

Opinions anyone?<josh@.nautilusnet.com> wrote in message
news:1111427709.765197.314850@.f14g2000cwb.googlegr oups.com...
>I am designing an object model and DB and I can't decide where to put
> calculated fields... Should it be in the database or the middle-tier?
> In other words, if I have an OrderItem on an Order and there are two
> columns called "Quantity" and "Cost" I also will want "TotalCost" =
> "Quantity x "Cost"... Do I have the "GET" stored proc return this
> caculcated value or just create the property in my middle-tier and have
> it calculate it there?
> Opinions anyone?

For such a simple calculation, I would do it in either the stored procedure
or perhaps a view - the view is useful if you want to have the value
available to other clients which may not always call your proc, and for ad
hoc queries. You could use a computed column as well, but I haven't used
them much, so I don't really know how they perform with large data sets.

More complex calculations may be better in the middle tier, especially if
you only calculate for relatively small data sets, and if you need to use
mathematical functions which aren't available in TSQL, then you may have no
choice. As in many cases, the best way to get a good answer is to test it
yourself with your own data and see which solution works out better for you.

Simon|||josh@.nautilusnet.com wrote:

> I am designing an object model and DB and I can't decide where to put
> calculated fields... Should it be in the database or the middle-tier?
> In other words, if I have an OrderItem on an Order and there are two
> columns called "Quantity" and "Cost" I also will want "TotalCost" =
> "Quantity x "Cost"... Do I have the "GET" stored proc return this
> caculcated value or just create the property in my middle-tier and have
> it calculate it there?
> Opinions anyone?

First off, I would highly suggest that you have all of these calculated
fields defined in some sort of data dictionary so that you can use a code
generator of some sort to generate it. This lets you change your mind
about implementation details after the fact.

Calculated fields can come down to these types:

1) EXTEND, most common, extended = price * qty
2) FETCH, pull price from items table into orders, trigger action is change
of value of order_detail.item_code.
3) AGGREGATE, any sum, avg, min, max or count() from detail to header
4) DISTRIBUTE, like a fetch, in that a value goes from header to detail,
but triggering action is a change in value in header, and it is pushed to
*all* rows in child table that match on pk/fk. Included for completeness
but considered evil.

All approaches boil down to either materializing in the tables, or not doing
it.

The simplest approach if you don't put them into tables is to create views.
I've done this with a view generator and it is pretty nifty. The danger is
that the very simplicity of the views will obscure very deeply nested
subqueries, which may not be discovered until the system comes under heavy
load.

The other option is to materialize them into the tables. This is considered
evil by relational theorists, but the only real requirement if you do this
is that you not let a casual user update the automated columns. So a
straight command "UPDATE ... SET TotalCost=5 " should fail with an error.

--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@.(Sec)ure(Dat)a(.com)|||I am just now getting back to this thread. In my opinion the problem
with putting calculations into the the sproc is that it's not possible
to no the calculation until call the sproc again. This feels unnatural
when working with an object model, for example:

OrderItem item = new OrderItem();
item.Quantity = 5;
item.Cost = 10.00;

Response.write(item.Total);

For the above code to work using the "calculations in sproc" method I
would have to hit the database again for item.Total to have a value...|||If you want to see the calculated total immediately, then a view is
probably better than a procedure:

create view dbo.OrdersWithTotalCost
as
select
OrderID,
OrderItemID,
...
/* Other columns from Orders */
...
Quantity,
Cost,
Quantity * Cost as 'Total'
from
dbo.Orders

I'm not sure I understand your concern about hitting the database again
- since your calculation is so simple, you will know the value of Total
before you even INSERT the new order item, and you may not need to
retrieve it again (unless there's further processing in the database,
of course).

If you really want to avoid another query, then one option is to create
an InsertOrderItem stored procedure, which INSERTs the new item and
then returns the total as an output parameter.

Simon|||Simon Hayes wrote:

> If you want to see the calculated total immediately, then a view is
> probably better than a procedure:
> create view dbo.OrdersWithTotalCost
> as
> select
> OrderID,
> OrderItemID,
> ...
> /* Other columns from Orders */
> ...
> Quantity,
> Cost,
> Quantity * Cost as 'Total'
> from
> dbo.Orders
> I'm not sure I understand your concern about hitting the database again
> - since your calculation is so simple, you will know the value of Total
> before you even INSERT the new order item, and you may not need to
> retrieve it again (unless there's further processing in the database,
> of course).
> If you really want to avoid another query, then one option is to create
> an InsertOrderItem stored procedure, which INSERTs the new item and
> then returns the total as an output parameter.

My original suggestion held that the definitions should be stored in a data
dictionary and any implemention, views or sprocs, should be generated from
that.

If you do that, the client (some OO code) can read the dictionary, or you
can generate code for classes, and they can do their own calculations on
the fly for user convenience. You then can independently decide how to
implement the same formulas on the server.

If the implementation is based on a dd, you can try different methods and
change your mind rather painlessly.

--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@.(Sec)ure(Dat)a(.com)