Temporal database - no end date

 I have just discovered, thanks to this group, the book "Developing Time-Oriented Database Applications in SQL" by Richard T. Snodgrass which certainly seems to be an excellent reference on the subject of temporal databases. (A pdf can be downloaded for free from http://www.cs.arizona.edu/people/rts/publications.html).

 However, I am failing to grasp something. In the book all histories have two dates. A from date and an end date. Obviously the exact design and usage varies on the application so I will paint a picture.

Lets say that an entity, a gym membership record always has a status, be it active, dormant cancelled, banned etc. I would like to keep a history of the status. Lets say I join a gym on Jan 1st 2007 becoming an active member on that date, Then I break some equipment on Feb 1st 2007 and am banned for the month. On March 1st I return to active status.

 Following the Snodgrass examples I would have a table like this

Status From Date To Date

Active        Jan 1st 2007        Feb 1st 2007
Banned      Feb 1st 2007       Mar 1st 2007
Active        Mar 1st 2007       Jan 1st 2008 (12 month membership)

Given that I must always have a status, why store the "To Date". If I have a simpler table, it is clear that one status stops when the other starts.

Status From Date

Active        Jan 1st 2007
Banned      Feb 1st 2007
Active        Mar 1st 2007

The member would have an expiry date in another table so monitoring when the membership expires isn't difficult. I am really more interested in the best method in terms of SQL queries for reporting (reports to be defined later), performance, data integrity and the like.

Any comments appreciated.

Thanks

Kev Received on Mon Jan 15 2007 - 17:47:40 CET