Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [data-versioning]

51 questions
29
votes
4 answers

When to use CDC to track history?

SQL Server Change Data Capture is a feature that reads historical data from the SQL Server transaction logs and stores them in a special table. Through the use of special table value functions (TVF) it then allows the user to query this data, making…
magnattic
  • 469
  • 3
  • 7
  • 17
13
votes
2 answers

How to check if the SYSTEM_VERSIONING for a table is ON?

I know that SQL Server 2016 let us to use the SYSTEM_VERSIONING like : CREATE TABLE EmpSalary ( EmpID int NOT NULL PRIMARY KEY CLUSTERED , SalaryAmt decimal (10,2) NULL , SalaryBeginDT datetime2 GENERATED ALWAYS AS ROW START NOT…
eandbsoftware
  • 131
  • 1
  • 1
  • 3
11
votes
6 answers

Is it bad practice to have a "most_recent" boolean column in addition to a "create_at" timestamp column to track the latest version of a record?

The table looks like this, it's SCD type 2: +-----------+------------------+------------------------+ | id (text) | version (serial) | created_at (timestamp) | +-----------+------------------+------------------------+ For 99% of queries we will be…
Henry
  • 213
  • 2
  • 6
6
votes
3 answers

Building a branched versioning model for relational databases

I am database designer and at my current project I'm implementing versioning capabilities required to concurrently edit rows of data in RDBMS. The project requirements says, that data editing sessions can go on for several hours or days until…
Nipheris
  • 161
  • 1
  • 4
6
votes
2 answers

Equivalents to `ora_rowscn` in other databases?

Do other databases other than Oracle have ora_rowscn (http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns007.htm) equivalents, or do you have to implement them yourself in your fields, code and stored procedures? ora_rowscn is a…
leeand00
  • 1,722
  • 6
  • 20
  • 36
5
votes
3 answers

Versioning: Is this technology used in DBMS other than spatial?

ESRI's spatial database management systems, called geodatabases (more), use a technology called versioning. A version represents a snapshot in time of the entire geodatabase and contains all the datasets in the geodatabase. Versions are not…
User1974
  • 1,517
  • 25
  • 54
4
votes
1 answer

Data History Table vs Using a Current Record Flag

There are two strategies when dealing with data records which contain multiple versions. One is to have current records in one table, and their past versions in a history table. The other is to have all versions in the same table with a flag on the…
AnotherDeveloper
  • 143
  • 1
  • 5
4
votes
0 answers

LIFO with stock splits

Background I am managing a database containing my users' trading of financial products, stocks bonds, ETFs etc. The system offers simple bookkeeping functionality (create portfolio, create instrument, book a trade, book a cash flow ...) for my…
Kermittfrog
  • 141
  • 2
4
votes
2 answers

Save history editable data RDBMS

I want to make application like testing system. Every question has one or many variant of answers (and one or many can be right). I apologize that tutors and students use my testing system. It means that tutor can make CRUD operations with subject,…
Ray
  • 143
  • 1
  • 5
4
votes
1 answer

MySQL Workbench team strategy

I'm considering to use MySQL Workbench at work, mainly because of the modelling tools and to easily sync the development and the production DBs. I work with another developer on the same project, and we currently use GIT to version our work. In a…
Alessandro Cappello
  • 206
  • 1
  • 4
3
votes
1 answer

Is there any database with versioning (column based) built in?

I'm wondering if there is a database (SQL or NOSQL) which provides versioning for for the records ( if the column/field/property is enabled for versioning). I'm developing an authentication software and I need to provide a log /"history" for a…
The user with no hat
  • 131
  • 3
3
votes
1 answer

Updating table of versioned rows with historical records in PostgreSQL

I have a master table of versioned rows: CREATE TABLE master ( id SERIAL PRIMARY KEY, rec_id integer, val text, valid_on date[], valid_during daterange ); INSERT INTO master (rec_id, val, valid_on, valid_during) VALUES …
danpelota
  • 133
  • 1
  • 5
3
votes
1 answer

Relational modeled design to store versioned files

The scenario I have images in a database table and some of these need to be altered then re-saved(not overwriting the original) whilst providing a reference back to the original. I also save the directory path which the images are stored, if a…
cecilli0n
  • 305
  • 2
  • 4
  • 9
3
votes
0 answers

Is there a reliable way to determine the order in which Postgres transactions were committed without logical decoding?

Is there a reliable way to determine the serial order in which Postgres transactions were committed without logical decoding? From what I understand pg_xact_commit_timestamp can’t necessarily be relied upon for this (and I’m not even sure I’ll be…
Jordan
  • 151
  • 5
3
votes
0 answers

Should Postgres USER/ROLE management be done with Flyway?

I've recently inherited a company database. The current practice is to put everything schema-related in a Flyway migration and obviously we never change the schema outside of Flyway. This includes USER and ROLE operations but does it need to? Will…
medley56
  • 173
  • 8
1
2 3 4