One of the books I mention in my Amazon Wish List is Developing Time-Oriented Database Applications in SQL (Morgan Kaufmann Series in Data Management Systems) .

A few days ago I got an email from Steven Gravitz telling me that the book is actually available in downloadable PDF form for free. Here's the direct link (PDF).

One of the reasons I was once interested in such a book was that I had to build and design a payroll system, which had many interesting facets relating to time. Mainly, it had to keep track of the past 7 years of payrolls, terms of employment and raises in salaries, with each month having it's own total history (that is - the 12 months that count for doing salaries in February 2002, may be totally different that the 12 months that count for calculating the salaries for March 2002 - it's really an amazing concept. I call each such month a "comet" with it's preceding history as the "tail" - each month is its own comment with its own tail.)

 

It was head-squeezing fun for me, as I learned all the hard stuff the hard way. This book seemed to be talking about just these kinds of problems. In fact, here are a couple paragraphs from the preface:

"This is how it goes.
We develop a database application, and initially the project proceeds  smoothly enough.There are alternatives to weigh during the schema design,problems to contend with while writing the SQL code,and constant reconfiguration and interaction with other programs and legacy data, but all in all the project is under control.Then we decide that one of the tables needs another DATE column, recording when the row was valid. ( After all, we added a birthdate column a few weeks ago, with no surprises.) So we rework the part of the application that maintains that table, noticing that the code is getting more complicated. During testing, we discover that the primary key no longer is sufficient.We add the DATE column to the primary key, acknowledging that this is only a stop gap measure, and hope that the input data will be well formed, as there isn't  time to write code that checks those constraints properly. In the back of our mind is the lingering doubt that perhaps referential integrity checking isn't working quite right either. We soon realize that we need another DATE column to record when the row
was no longer valid. In doing so we encounter a raft of off-by-one bugs..."

"...Around this time, users start complaining that reports aren't  consistent, that copies of the end-of-the-year summary have different numbers in them..."

And so he goes on to describe a situation that many of us had faced in the past or will face in the future...

I'll get to reading it before bedtime..

 

Thanks for the tip, Steven!