Re: Rookie Questions: Storing the results of calculations vs. not?

[Rich Shepard <rshepard(at)appl-ecosys(dot)com>]

On Tue, 29 May 2007, Ron Johnson wrote:

> > 1)  The data contains the price of the underlying stock, the strike price
> > of the option, and the option premium. From this I can calculate the
> > "cost basis" and the "maximum potential profit", which are elements I
> > would like to be able to SELECT and ORDER.  Should I store the results of
> > these calculation with the data, or is this "business logic" which
> > doesn't belong in the database.  Is this what views are for?
>
> I'd say "business logic", and yes, views are good for that.

  Years ago I was taught that calculation results should not be stored, but
re-calculated as needed. If you change the underlying formulae you
immediately see the new results without having to update every table.

  You can use views, and you can return the results by specifying the SELECT
statement with a column name for the calculation. For example,

  SELECT original_cost - (selling_price + commission) AS net_profit
  FROM ...

> > I have rudimentary python skills, and I'm getting the hang of psycopg2. 
> > After reading the postgresql manual, what should I read next?

  Three suggestions:

  "wxPython In Action" by Robin Dunn to write the GUI for your application.
  "Introduction to SQL, 4th Edition" by Rich F. van der Laans to learn the
power of the various SQL data manipulation clauses.
  "SQL for Smarties, 3rd Edition" by Joe Celko to get better insight into
effectively written advanced queries.

  Don't forget the PostgreSQL docs for specifics of the language and
implementation.

Rich

--
Richard B. Shepard, Ph.D.               |    The Environmental Permitting
Applied Ecosystem Services, Inc.        |          Accelerator(TM)
<http://www.appl-ecosys.com>     Voice: 503-667-4517      Fax: 503-667-8863