Eric On Friday, Oct 15, 2004, at 20:25 US/Eastern, George Essig wrote:
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
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
