Stack Overflow
Stack Overflow

Questions tagged [temporal-database]

a temporal database has built-in time aspects, usually including valid-time and transaction-time.

93 questions
87
votes
4 answers

How to re-save the entity as another row in Doctrine 2

Let's say I have entity $e. Is there any generic way to store it as another row, which would have the same entity data but another primary key? Why I need this: I'm implementing some sort of Temporal Database schema and instead of updating the row I…
zerkms
  • 249,484
  • 69
  • 436
  • 539
54
votes
11 answers

Why do we need a temporal database?

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…
Arnkrishn
  • 29,828
  • 40
  • 114
  • 128
28
votes
4 answers

How to implement a temporal table using JPA?

I would like to know how to implement temporal tables in JPA 2 with EclipseLink. By temporal I mean tables who define validity period. One problem that I'm facing is that referencing tables can no longer have foreign keys constraints to the…
Alfredo Osorio
  • 11,297
  • 12
  • 56
  • 84
23
votes
2 answers

Seed data with old dates in Temporal Table - SQL Server

I need to seed data for my local development purpose in the following Temporal Table, the start date should be old. The given Table Schema is CREATE TABLE [dbo].[Contact]( [ContactID] [uniqueidentifier] NOT NULL, [ContactNumber]…
B.Balamanigandan
  • 4,713
  • 11
  • 68
  • 130
22
votes
5 answers

Entity Framework not working with temporal table

I'm using database first entity framework 6. After changing some of the tables in my schema to be temporal tables, I started getting the following error when attempting to insert new data: Cannot insert an explicit value into a GENERATED ALWAYS…
Matt Ruwe
  • 3,386
  • 6
  • 39
  • 77
18
votes
5 answers

What are the pros and cons of Anchor Modeling?

I am currently trying to create a database where a very large percentage of the data is temporal. After reading through many techniques for doing this (most involving 6nf normalization) I ran into Anchor Modeling. The schema that I was developing…
Chuu
  • 4,301
  • 2
  • 28
  • 54
16
votes
3 answers

Historical / auditable database

This question is related to the schema that can be found in one of my other questions here. Basically in my database I store users, locations, sensors amongst other things. All of these things are editable in the system by users, and…
Mark
  • 1,296
  • 13
  • 28
12
votes
3 answers

What is the best way to query deleted records with SQL Server 2016 temporal tables?

I'm looking at SQL Server 2016 temporal tables and can't find any efficient way to query for all historical records that are now deleted. I prefer not to soft-delete or moving to a 'deleted items table', as I feel with temporal tables it is…
Le-roy Staines
  • 2,037
  • 2
  • 22
  • 40
11
votes
8 answers

Storing changes on entities: Is MySQL the proper solution?

i want to store changes that i do on my "entity" table. This should be like a log. Currently it is implemented with this table in MySQL: CREATE TABLE `entitychange` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `entity_id` int(10) unsigned…
Stefan
  • 2,028
  • 2
  • 36
  • 53
10
votes
2 answers

Backing up MariaDB Temporal Database

Generally, I am excited by the Temporal Database feature. However, mysqldump is not supported for database export and restore. I can find no resource in the documentation (linked to above) that indicates which methods of backup and restore are…
ftrotter
  • 3,066
  • 2
  • 38
  • 52
10
votes
4 answers

Are there any design patterns for bitemporal NoSQL databases?

I'm curious if anyone has implemented or even knows of any bitemporal databases built on NoSQL platforms (e.g., riak).
Pinko
  • 3,309
  • 2
  • 20
  • 15
9
votes
3 answers

How can alter existence tables to SQL Temporal table by keeping data?

I have many table with data, which I want to convert to Microsoft Temporal table, But when I want to convert temporal table cause lost my data. My code is: Alter TABLE dbo.Employee ( [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED ,…
Hamed Roshangar
  • 150
  • 8
9
votes
2 answers

Database row snapshots/revisions

I am looking for a suitable process by which to keep revisions, or snapshots of rows (and their relations) in a database. Take for example an eCommerce platform- A customer creates an order. The order is associated with a billing address and a…
Ben Swinburne
  • 25,669
  • 10
  • 69
  • 108
8
votes
2 answers

How to implement temporal data in MySQL

I currently have a non-temporal MySQL DB and need to change it to a temporal MySQL DB. In other words, I need to be able to retain a history of changes that have been made to a record over time for reporting purposes. My first thought for…
HartleySan
  • 7,404
  • 14
  • 66
  • 119
8
votes
4 answers

Database design to hold a person's information that changes with time?

We use a third-party product to manage our sports centre membership. We have several membership types (eg. junior, student, staff, community) and several membership statuses (eg. annual, active, inactive, suspended). Unfortunately the product only…
dave
  • 11,641
  • 5
  • 47
  • 65
1
2 3 4 5 6 7