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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
