Talk:Temporal database

Problems with the article

The first sentence of the article entitled "Temporal database" starts like this:

"A temporal database is a database management system…"

This is nonsense. A database is what a database management system (DBMS) manages, not the DBMS itself.

But the whole article is really woeful, imo.

Why does a database, in order to be temporal, have to be managed using "a temporal version of structured query language [sic]", specifically? Is the author assuming that every database is accessed using SQL? Perish the thought!

Besides, where in the current technology do temporal versions of SQL exist? Nowhere at all, to the best of my belief. There was a brief attempt, in the mid-1990s, to add an extension to the SQL standard to support temporal data, but the attempt was abandoned partly because of a raging controversy that arose over the USA proposal based on TSQL2 and in any case because the SQL vendors were diverted by the pressing requirement at the time for "object/relational" support (the eventual take-up for which by the users appears to have been negligible!). Then, when O/R support was finally published in SQL:1999, the new presing requirement for XML support got in the way of any attempt to revive temporal support.

A similar comment applies to "the temporal aspects usually include". How can "usually" be justified, given the actual nonexistence of temporal databases, for lack of any technology to support them?

Now, the lack of technology is in stark contrast to the amount of research into the topic that has taken place in the academic world over the past quarter of a century or more. The article makes no mention of that research or its results. It doesn't even mention the severe problems that arise when one requires to record the changes in the truth of certain propositions over time, let alone the various solutions that researchers have proposed to those problems.

For example, the idea of simply adding "from" and "to" attributes to relations to represent "valid time" intervals has been pretty well universally rejected in favour of recording intervals as atomic values, such that a single interval-typed attribute replaces the naive from/to duo suggested in the article, and the justification for interval types is overwhelming when you really think about it. For a start, consider how many times you would have to write a constraint to the effect that no "from" value must be greater than the "to" value with which it is paired; with interval types, the constraint is built into the type definitions, there being no such thing as an interval that ends before it begins. Next, what about the interpretation of the interval bounds? Are the time points they represent in the interval or just out of it? With interval types the question can be finessed using the conventional notation whereby a square bracket denotes a cloised bound, an parenthesis an open one, such that [2:4], (1:4], [2:5) and (1:5) all denote the same interval (of integers).

And what about the importance of quantisation, recognised by most authorities in the temporal database field? For example, how is the duration of an interval represented by a from/to pair to be determined, unless the relevant unit of measure (scale) is known? Even if the so-called discrete (versus continuous) model of time for temporal database purposes is still controversial, surely it and some of the reasons for it should be mentioned, considering that most people intuitively think of time as being continuous?

As for "the Valid-To is filled with infinity (∞)", since when has infinity been a time? What is the duration of an interval that runs from the 1st of January, 2006, to infinity? What is the effect of infinity on all the other operators that can be invoked on time values? The author has dismissed perhaps one of the most difficult aspects of temporal databases--concerning "the moving point, now" at a single, cavalier-like stroke! It might be beyond the scope of the article to describe all the various approaches to that problem that have been advanced, but at the very least the fact shoud be mentioned that it is a big problem, surrounded by a certain amount of controversy.

I hesitate to attempt a replacement for this article myself, because I am a coauthor of a fairly recent book on the subject that advances one particular approach--what might be called the "pure relational" approach--that is much derided by some of those who do not hold the tenets of the relational model of data in such high regard as I do myself (including in particular advocates of the TSQL2 approach). Thus, my attempt might be seen as one-sided and possibly self-serving.

AndrewWarden 18:37, 22 February 2006 (UTC)

Please do replace the article, Mr Darwen. This is the principle on which Wikipedia works: if something's broken, fix it first and discuss it later. If no one complains, great. If someone improves your article, great. If someone reverts your replacement, he will have to explain himself. If you can't agree, refer it to a third party.

It might be that after all is done and said people can't still agree; one could always create an alternative article called 'Temporal relational databases' or something the like.

--

Leandro GFC Dutra 13:47, 7 April 2006 (UTC)

---

Actually, the article is more wrong than that. The model is completely wrong.

The entity classes should be:

PERSON - this describes the person throughout time. The only attribute in this example would be "Name" CITY (or simply GEOGRAPHIC AREA) - the place where a PERSON may live at any particular time. PERSON PLACEMENT this describes the fact that a person lived in a particular city. Attributes are "From date" and "To date". There is a many to one relationship asserting that each PERSON PLACEMENT must be of one PERSON in one CITY. That is, each PERSON may be located via one or more PERSON PLACEMENTS in a CITY, and each CITY may be the object of one or more PERSON PLACEMENTS of a PERSON.

Thus, PERSON and CITY represent what are (for purposes of this example) eternal things, and PERSON PLACEMENT describes the state of a PERSON's being in a CITY at a particular time.

Problem solved.

DaveHay 14:45(CST) 23 March, 2006

Please fix!

--

Leandro GFC Dutra 15:04, 19 April 2006 (UTC)

I think there’s a glitch in the John Doe example. It should show that John Doe lived in Smallville through August 25th and started living in Bigtown on August 26th. As the example is written, he lived in both places on August 26th, which could cause the double-counting of any metrics associated with John Doe on that date. — Preceding unsigned comment added by Elkinsecon (talk • contribs) 20:09, 20 June 2011 (UTC)

External links

This page is linked to by Fabian Pascal in http://www.dbdebunk.com/index.html in the section TO LAUGH OR CRY: 04/07/06 Wikipedia Talk: Temporal Database. --elwikipedista 01:52, 12 April 2006 (UTC)

Remarks section moved to this talk page

There was a small section titled "Remarks/Issues" which clearly belong on the talk page instead. The contents of that section (which I removed) were

* The temporal example could do with rewording for clarity.

-Dmeranda 18:23, 18 July 2006 (UTC)

The article has been significantly improved since I wrote that long criticism in February, 2006. That criticism should now be regarded as totally irrelevant but I'm not sure what should properly be done about it.

AndrewWarden 13:43, 27 May 2007 (UTC)

Decision Time

Decision time may also want to be recorded in a temporal database. Decision time is the time at which a decision was made to execute an event. —Preceding unsigned comment added by 91.110.37.230 (talk) 13:33, 26 January 2009 (UTC)

I added a remark about other timelines (so you get a multitemporal database) and added Decision Time as an example. RonaldKunenborg (talk) 15:05, 14 July 2009 (UTC)

Why end-date?

Woops - i've read a bit more about TSQL2, SQL3 and dates, and I removed my old comment(s) regarding the use of data ranges. I'll go and read a bit more before sticking my foot in my mouth again.

Btw: Interesting article with design patterns related to time: http://martinfowler.com/ap2/timeNarrative.html

A remark: Oracle now has Oracle Workspace Manager, which implements most (or all) of TSQL2's features in Oracle, transparent to the user and developer. Is it a good idea to place a link to it, or is this 'not done' because its a commercial product? RonaldKunenborg (talk) 16:21, 11 July 2009 (UTC)

Errr, what the heck does TSQL have to do with temporal databases? This article doesn't even mention TSQL anywhere. Am I missing something? Does compliance with TSQL 2 require having all the characteristics of a temporal database built into the product?

By the way, if there are databases that actually qualify as temporal databases as describe in the article, or plugins that make then into that, then please be bold and make a new section called "Implementation in databases" or "Implementation in commercial databases" and add it there. --Enric Naval (talk) 19:39, 11 July 2009 (UTC)

TSQL2 has nothing to do with TSQL. If you read the references with the article about temporal databases, you can see that it's a proposal for integrating Time into SQL, which has later been integrated in the proposal for SQL 3 and unfortunately has been delayed by the ISO because they wanted to integrate Object Oriented stuff first.

About the section: good idea, I'll do that. RonaldKunenborg (talk) 14:20, 14 July 2009 (UTC)

Ah, ok, I see an explanation in page 9 of this source. I see that the article gives absolutely no explication of what TSQL2 is. There should be another section (called "TSQL2 proposal"?) exclusively for explaining the proposals so people can see where this temporal database thing is coming from. We can redirect TSQL2 to that section, as it's currently a red link and I'm not sure that there is enough material to make a full article of its own. A section here would provide a nice home for TSQL2 material.

The article currently makes no effort to explain the standarization efforts, TSQL2 (ANSI X3.135.-1992 and ISO/IEC 9075:1992) and SQL/Temporal (ISO/IEC 9075-7), and it doesn't explain that there isn't a standard (yet), and why it didn't go into SQL'99. --Enric Naval (talk) 15:18, 14 July 2009 (UTC)

True - i'll give it a shot. There's a website with some more background and material, I hope I can find it again. If yes, it will show up shortly in the article. RonaldKunenborg (talk) 21:31, 14 July 2009 (UTC)

I created a redirection and added some history, but I'm not sure how to redirect to a section, and perhaps you could take a look at the section as well? I'd appreciate it. I also used your suggestion for the Cite Web template, that improved the link a lot.RonaldKunenborg (talk) 22:58, 14 July 2009 (UTC)

I changed the redirect. I don't have time right now to check the section, will look later. --Enric Naval (talk) 01:59, 15 July 2009 (UTC)

I made some tweaks to the section to make it clearer to the reader what each thing is. My limited knowledge of the topic doesn't let me add more. Congrats for the work in writing it, Ronald. --Enric Naval (talk) 16:29, 15 July 2009 (UTC)

PSA: The above linked source can now be found at: Developing Time-Orientated Applications in SQL

- Jim Grisham (talk) 03:43, 13 March 2023 (UTC)

Another updated link: Temporal Patterns / Temporal Patterns (archive of original URL)

- Jim Grisham (talk) 03:48, 13 March 2023 (UTC)

Such a good article

Dear contributors, thank you very much, that is one of the best Wikipedia articles — simple, clean, and yet complete. Respect! — Preceding unsigned comment added by Danilcha (talk • contribs) 13:20, 20 May 2014 (UTC)

From Point in Time to Time Interval

On these pages bitemporal tables are explained graphically and an algorithm for the transformation of bitemporal time points into intervals is derived:
www.bitemporal.net

Temporal relationships

Is there a way to keep track of relationships? Example: Some wing was attached to an airplane from 2014 through 2017, then it was a part of another plane from 2018 to 2020, then it was stored in a warehouse; and, then, since this year, it used by another aircraft. I guess that data might be stored in a many-to-many table (many parts, many planes) and not in the tables representing the objects. George Rodney Maruri Game (talk) 05:48, 18 May 2022 (UTC)

Further reading

German-language Wikipedia pages such as Temporal data retention and Temporal database have further information on this topic (for individual reading and/or for potential inclusion in this article / elsewhere in the English-language Wikipedia).

- Jim Grisham (talk) 04:14, 13 March 2023 (UTC)