jo**@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)