Why do I need a temporary database?

Question

I read about temporary databases, and it seems like they built in time aspects. I wonder why we need such a model?

How different is it from a regular DBMS? Can't we have a normal database, that is, RDBMS and say that there is a trigger that associates a timestamp with every transaction that occurs? Maybe there will be a performance hit. But I am still skeptical of temporary databases having a strong business in the market.

Does any of the existing databases support this feature?

Answer 1

A time database effectively stores time series of data, usually with a fixed time interval (for example, seconds or even milliseconds), and then only stores changes in the measured data. The time stamp in the DBMS is a discretely stored value for each measurement, which is very inefficient. The temporary database is often used in real-time monitoring applications such as SCADA. A well-established system is the PI database from OSISoft ( http://www.osisoft.com/ ).

Answer 2

Think about your diary on the appointment / journal - it will run from January 1 to December 31. Now we can request a diary for meetings / journal entries any day. This ordering is called valid time . However, appointments / notes are usually not inserted in order.

Suppose I would like to know what meetings / entries were in my diary on April 4th. That is, all the entries that were recorded in my diary on April 4. This is the transaction time .

Given that appointments / notes can be created and deleted, etc. A typical entry has a start and end valid time that covers the recording period and the start and end time of the transaction, which indicates the period during which the entry appeared in the diary.

This scheme is necessary if the diary may undergo a historical revision . Suppose that on April 5th I understand that the appointment that I had on February 14th really happened on February 12th, i.e. I found an error in my diary - I can correct the error to correct the correct temporary image, but now my request for what was in the diary on April 4 would be incorrect IF the transaction time for appointments / records is also saved. In this case, if I request my diary as of April 4, he will show the appointment that existed on February 14, but if I ask as of April 6, he will schedule an appointment on February 12.

This temporary function of the temporary database allows you to record information about how errors are fixed in the database. This is necessary for reliable verification of the data that is recorded when changes are made, and allows you to request queries regarding how the data was reviewed time.

Most of the business information should be stored in this bitporal scheme in order to provide a reliable audit record and maximize business intelligence - hence the need for support in a relational database. Note that each data element occupies a (possibly unlimited) square in a two-dimensional time model, so people often use the GIST index to implement bit-time-indexing. The problem here is that the GIST index is really for geographic data, and the requirements for temporary data are slightly different.

PostgreSQL 9.0 exception restrictions should provide new ways to organize temporary data, for example. transaction and actual time. PERIODS must not overlap for the same tuple.

Answer 3

As I understand it (and oversimplifies it), the temporary database records facts about when the data was valid, as well as the data itself and allows you to query the time aspects. As a result, you came across the tables “allowable time” and “transaction time”, or “bit-rate tables”, including the aspects “real time” and “transaction time”. You should consider reading either of these two books:

• Darwen, Date and Lorentzos " Temporal Data and the Relational Model " (from print),
• and (to a completely different extreme) SQL Server time-oriented application development , Richard T. Snodgrass, Morgan Kaufmann, Inc., San Francisco, July 1999, 504 + xxiii pages, ISBN 1-55860-436- 7. It goes out of print, but is available as a PDF on its website at cs.arizona.edu (so a Google search makes it pretty easy to find).

Answer 4

Temporary databases are often used in the financial services industry. One reason is that you rarely (if ever) allow any data to be deleted, so the ValidFrom - ValidTo fields in records are used to indicate when the record was correct.

Answer 5

Besides reading the Wikipedia article ? A database that stores an audit trail or similar transaction log will have some properties that are "temporary." If you need answers to questions about who did what, to whom and when , you have a good candidate for a temporary database.

Answer 6

You can imagine a simple temporary database that simply records your GPS location every few seconds. The possibilities for compressing this data are great, a normal database, you will need to store a time stamp for each row. If you need a lot of bandwidth, knowing that the data is temporary and that updates and deletions in the row will never be needed, the program may discard the large complexity inherited in a typical RDBMS.

Despite this, temporary data is usually simply stored in a normal DBMS. PostgreSQL, for example, has some temporary extensions , which makes this a little easier.

Answer 7

Two reasons come to mind:

• Some of them are optimized for insertion and reading and can offer dramatic improvements.
• Some of them have a better understanding of time than traditional SQL, which allows you to group operations by seconds, minutes, hours, etc.

Answer 8

Just an update, the Temporal database is suitable for SQL Server 2016.

To eliminate all your doubts why you need a temporary database, and not configure it using custom methods and how efficiently and smoothly configure SQL Server, check out the detailed 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 release of SQL Server 2016 CTP2 (beta 2), you can play with it.

Check out this video on how to use temporary tables in SQL Server 2016.

Answer 9

In addition, “what new can be done with it,” it may be useful to consider “what old things come together?”. A temporary database is a concrete generalization of a “normal” SQL database. Thus, this can give you a single solution to problems that were not previously associated. For example:

• Web Concurrency . When your database has a web interface that allows multiple users to perform standard Create / Update / Delete (CRUD) changes, you should run into the problem of concurrent web changes . Basically, you need to verify that changing the input does not affect records that have changed since the last user saw these records. But if you have a temporary database, it may already associate something like a “revision identifier” with each record (due to the difficulty of creating timestamps and monotonously increasing). If so, then it becomes a natural, “already built-in” mechanism to prevent other users from crashing data during database updates.
• Legal / tax reports . The legal system (including taxes) places more emphasis on historical data than most programmers do. This way, you will often find advice on schemes for invoices and thus warn you to beware of deleting records or normalizing in a natural way, which can lead to inability to answer basic legal questions such as “Forget about their current address, to which address did you send this invoice in 2001? " With a temporary database base, all the frauds of these problems (they are usually halfway to creating a temporary database) go away. You simply use the most natural pattern and delete when it makes sense, knowing that you can always come back and answer historical questions accurately.

On the other hand, the temporary model itself is halfway to complete version control, which may inspire further use. For example, suppose you collapse your own temporary object on top of SQL and enable forking, as in version control systems. Even limited branching can make the sandbox offer easier — the ability to play and modify the database with failure, without causing any visible changes to other users. This makes it easy to provide highly realistic user training in a complex database.

Simple branching with a simple join tool can also simplify some common workflow issues. For example, a nonprofit organization may have volunteers or low-paid employees who perform data entry. Providing each employee with their own branch can facilitate the ability of the supervisor to review their work or improve it (for example, removing duplicates) before merging it into the main branch, where it will become visible to "ordinary" users. Affiliates can also simplify permissions. If the user is allowed to use / see his unique branch, you do not need to worry about preventing all possible unwanted changes; you merge the changes that make sense anyway.

Answer 10

My understanding of temporary databases is that they are designed to store certain types of temporary information. You can mimic this with standard RDBMS, but using a database that supports it, you have built-in idioms for many concepts, and the query language can be optimized for such queries.

For me, this is a bit like working with a GIS-specific database, not an RDBMS. While you can drag and drop coordinates into RDBMS with launch, having appropriate views (e.g. via grid files) can be faster, and using SQL primitives for things like topology is useful.

There are academic databases and some commercial ones. There are several links in Timecenter.

Answer 11

Another example of where a temporary database is useful is when data changes over time. I spent several years working in a retail power supply network, where we stored meter readings for 30 minute blocks of time. These meter readings can be reviewed at any time, but we still needed to look back at the change history for the readings.

Therefore, we had the last reading (our “current understanding” of consumption in 30 minutes), but we could look back at our historical understanding of consumption. When you have data that can be configured in this way, temporary databases work well.

(Having said that, we manually cut it into SQL, but this was true recently. We would not have made this decision these days.)