Re: What should be the Database Design in this case?

>> I would hope that views are not 'built' at all. They should simply
contribute to the overall relational expression prior optimization and evaluation. <<

The Standards require that a VIEW act as if it was materialized. In the real world, this is usually not the case and the view body code can be dumped into the parse tree like a derived table expression. Usually, but not always; some views are materialized for one of two reasons:

1. You have to - it has computed columns, aggregates, etc.
2. Other users are also using the same view in other sessions, so it is MUCH more effective to materialize the table and share it than to let each session re-calculate it over and over.

I think we have a different definitions of a derived table and a snapshot. To me a derived table is a table expression in a FROM clause that has a name:

  SELECT ..
    FROM (<table expression>) AS <derived table name>[(<column name list>)]   

and a snapshot is a persistent table that gets refreshed with the values of a base table or query at a particular point in time. I am looking at rule RE-17 of Dr. Codd's RM/V2. He wants the catalog to hold the date and time of creation along with its creation. tgo quote: "Suppose a snapshot is created from a base realtion S. Unlike a VIEW, a snapshot of S does *not* reflect the insertions, updates and deletions applied to S after the snapshot was created." Received on Thu Dec 12 2002 - 12:58:31 CST