Eric D. Nielsen wrote:
> I'm in the process of adding more historic information to one of my
> databases. I've liked the theoretical treatment of the concept in
> "Temporal Data and the Relational Model", by Date, Darwen, &
> Lorentzos. A lot of it is not realizable without a lot of user
> defined types/functions/etc. I was wondering if anyone else has tried
> to use their approach as a base for their historical databases in
> PostGreSQL and has any "lessons learned" to share.
I have not read the book you mentioned, but I have read a book that may be related. I recommend
looking at:
Developing Time-Oriented Database Applications in SQL
by Richard T. Snodgrass
The book is out of print, but the author has made the PDF available on his website at:
http://www.cs.arizona.edu/people/rts/tdbbook.pdf
One of the main ideas in the book is to define valid time periods to record when information was
true or visible. Valid time periods are implemented by adding 2 columns to a table for the start
date and end date of a period. Much of the book is about how to test for and resolve valid time
period overlap between different rows. Topics include temporal versions of primary keys, inserts,
updates, and deletes. I have implemented these ideas in PostgreSQL. I can talk further about
this if you're interested.
The last part of the book is about adding 2 more columns to a table to define transaction time
periods. Transaction time periods can be used to reconstruct the state of a database at a
specific point in time. I didn't read this part as closely and haven't implemented these ideas.
Hope this helps,
George Essig
