Why do we need a temporal database?

Question

I was reading about temporal databases and it seems they have built in time aspects. I wonder why would we need such a model?

How different is it from a normal RDBMS? Can't we have a normal database i.e. RDBMS and say have a trigger which associates a time stamp with each transaction that happens? May be there would be a performance hit. But I'm still skeptical on temporal databases having a strong case in the market.

Does any of the present databases support such a feature?

Answer 1

Just an update, Temporal database is coming to SQL Server 2016.

To clear all your doubts why one need a Temporal Database, rather than configuring with custom methods, and how efficiently & seamlessly SQL Server configures it for you, check the in-depth video and demo on Channel9.msdn here: https://channel9.msdn.com/Shows/Data-Exposed/Temporal-in-SQL-Server-2016

MSDN link: https://msdn.microsoft.com/en-us/library/dn935015(v=sql.130).aspx

Currently with the CTP2 (beta 2) release of SQL Server 2016 you can play with it.

Check this video on how to use Temporal Tables in SQL Server 2016.

Answer 2

Apart from reading the Wikipedia article? A database that maintains an "audit log" or similar transaction log will have some properties of being "temporal". If you need answers to questions about who did what to whom and when then you've got a good candidate for a temporal database.

Answer 3

My understanding of temporal databases is that are geared towards storing certain types of temporal information. You could simulate that with a standard RDBMS, but by using a database that supports it you have built-in idioms for a lot of concepts and the query language might be optimized for these sort of queries.

To me this is a little like working with a GIS-specific database rather than an RDBMS. While you could shove coordinates in a run-of-the-mill RDBMS, having the appropriate representations (e.g., via grid files) may be faster, and having SQL primitives for things like topology is useful.

There are academic databases and some commercial ones. Timecenter has some links.

Answer 4

As I understand it (and over-simplifying enormously), a temporal database records facts about when the data was valid as well as the the data itself, and permits you to query on the temporal aspects. You end up dealing with 'valid time' and 'transaction time' tables, or 'bitemporal tables' involving both 'valid time' and 'transaction time' aspects. You should consider reading either of these two books:

• Darwen, Date and Lorentzos "Temporal Data and the Relational Model" (out of print),
• and (at a radically different extreme) "Developing Time-Oriented Database Applications in SQL", Richard T. Snodgrass, Morgan Kaufmann Publishers, Inc., San Francisco, July, 1999, 504+xxiii pages, ISBN 1-55860-436-7. That is out of print but available as PDF on his web site at cs.arizona.edu (so a Google search makes it pretty easy to find).

Answer 5

Two reasons come to mind:

1. Some are optimized for insert and read only and can offer dramatic perf improvements
2. Some have better understandings of time than traditional SQL - allowing for grouping operations by second, minute, hour, etc