On Tue, 19 Jun 2007, Jeff Davis wrote:
I have a table in my database that holds information on policies and this
table is linked to a number of other tables. I need to be able to keep a
history of all changes to a policy over time. The other tables that are
linked to policy also need to store historical data. When I run a query
on the policy table for a certain period, I also need to be able to pull
the correct related rows (i.e. the information that would have been in
the table at that time) from the tables linked to it.
I highly recommend _Temporal Data and the Relational Model_ by C.J.
Date, Hugh Darwen, and Nikos Lorentzos.
Here's another excellent book: "Developing Time-Oriented Databse
Applications in SQL" by Richard T. Snodgrass. If you go to his web page at
the Univ. of Arizona's site, and follow the publications link, you'll arrive
at
<http://www.cs.arizona.edu/~rts/publications.html>
where you can download a pdf of the book for free.
This was recommended to me by Joe Celko and helped me to solve a similar
problem in one of our projects.
We use a Permit_History table, which has as its primary key a pointer to
the permit_nbr (primary key field) in the Permits table. That's equivalent
to the Policies table referenced above.
A separate histories table can track all changes to a policy so that you
can extract the policy at any given date. Both Joe Celko's "SQL for
Smarties, 3rd Ed." and Rick F. van der Lans' "Introduction to SQL, 4th Ed."
are also excellent sources of useful insight into temporal math.
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
