Re: Temporal database - no end date

k.mellor_at_iee.org wrote:

> All,
>
> 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.

If you are satisfied with SQL kludges, then I suppose Snodgrass is okay. If you are interested in something more robust, I suggest you google Lorentzos and check out the Date, Darwen, Lorentzos material. Received on Mon Jan 15 2007 - 18:14:21 CET