Skip to main content

Questions tagged [data-versioning]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1 vote
2 answers
78 views

how best to have a record versioning or tracking in relational database?

The scenario involves maintaining versions of a 'Product' object (e.g., Product table) and creating new records when the product changes to the latest version (e.g. v2 from v1). It is crucial not to ...
Enigma's user avatar
  • 111
11 votes
6 answers
3k views

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) | +-----------+------------------+---------...
Henry's user avatar
  • 213
0 votes
1 answer
25 views

How to model edited linked records of a multi-schema entity while preserving the original linked records?

If you have a base class parent. parent can have multiple child records, a one-to-many relationship. parent schema: +-----------+ | parent_id | +-----------+ child schema: +-----------+----------+ | ...
Henry's user avatar
  • 213
0 votes
1 answer
27 views

Timestamp consistency of dependent system-version tables

I want to system-version some items that consists of data in forms of blobs as well as metadata. I plan to use two tables, say Metadata and Data, to store them. Data has a metadata_id to refer to the ...
user209974's user avatar
1 vote
0 answers
24 views

sdc type 2 and fk between dimension tables

this kind of argument has been discussed many times but I can't find a precise answer. I have two dimension tables, say Customer and Address, that i want to model after scd type 2 pattern, with ...
DRC's user avatar
  • 111
0 votes
1 answer
347 views

How to check if a table is system versioned for MariaDB?

How to check if a table is system versioned for MariaDB ? This post refers to same question on "SQL Server 2016" How to check if the SYSTEM_VERSIONING for a table is ON? I've tried this ...
John D.'s user avatar
0 votes
0 answers
90 views

Horizontal scaling with database schema

I am designing a database schema for our customers on-premise, which will be moved to Azure PaaS later. Regarding the "scale out" strategies I lean towards to create "one database",...
Avi's user avatar
  • 265
4 votes
0 answers
184 views

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, ...
Kermittfrog's user avatar
0 votes
1 answer
1k views

How to eliminate duplicates made by recursive CTE [closed]

I have following schema in PostgreSQL 12 (objects and their relations are versioned by date ranges): CREATE TABLE tmp_deps ( id bigint, code text, name text, start_date date, ...
Dmitry K.'s user avatar
  • 143
0 votes
0 answers
41 views

DB relationships with historical tables review

Idea is simple. User -> Projects -> Sprints -> Tasks, but I need to keep historical versions for them. It will fit to my requirements or there are some changes to be applied? - or different ...
p__d's user avatar
  • 113
3 votes
0 answers
333 views

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 ...
Jordan's user avatar
  • 141
1 vote
1 answer
31 views

When placing an order in a shop, should a shapshop be taken of the products in an order also?

...to prevent messing up with data updates, such as prices, titles, of the products that are placed in an order. Namely, a customer buys 3 items: for $5, $10 and $33 and pays for them. All is well. ...
Dajaku's user avatar
  • 41
0 votes
1 answer
1k views

Foreign key referencing table with composite primary key when I only need 1 column association

So lets say we have 2 tables on SQL Server database: CREATE TABLE VersionedData ( Id INT NOT NULL, Version INT NOT NULL CONSTRAINT [PK_VerData] PRIMARY KEY (Id, Version) ) And: ...
Julius Rupšys's user avatar
1 vote
1 answer
85 views

How would I maintain referential integrity across a database where I use versioned records while keeping data in sync?

I am building a database that consists of a few tables that are used as data sources for a form our application generates. [form] [question] [responses] id id id name ...
Barry Chapman's user avatar
0 votes
2 answers
120 views

Database layout to keep track of the "freshness" of a column value in a specific row

I have a Person with columns like name, address and bio. How do I store data in order to know the "freshness" of these columns? For instance, "for the Person Y, the name has changed 2 ...
den's user avatar
  • 1
2 votes
2 answers
1k views

Identify the version of a collation from ICU in Postgres

Postgres 10 and later incorporates the International Components for Unicode (ICU) library for text-handling and other internationalization issues. Changes happen to human languages, such as sorting ...
Basil Bourque's user avatar
1 vote
0 answers
48 views

Getting DEADLOCKS frequently on Versioned TABLE

I am having trouble to understand the increase in DEADLOCKS for Update transactions on Versioned TABLES. The problem, was not present earlier and then we enabled the versioning and after a while we ...
user3801185's user avatar
2 votes
1 answer
1k views

History table implementation: "Tuple-versioning" vs Effective Date

I'm designing a database that will have history tables (SCD Type 4 I guess) for auditing purpose. The question is what are the advantages of having tuple-versioning (start and end dates) vs just ...
AC.'s user avatar
  • 123
13 votes
2 answers
10k views

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 , ...
eandbsoftware's user avatar
3 votes
0 answers
2k views

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 ...
medley56's user avatar
  • 173
0 votes
0 answers
379 views

Best practice for record snapshot? (versioning?)

What's the best practice for storing a record snapshot? Use case example: Assume a user bought a product in an online store. It is a pleasant experience that the user can view a copy of what he saw ...
otong's user avatar
  • 101
2 votes
0 answers
431 views

What are best practices for managing DB record versioning with cross-reference tables?

In our system, we need to be able to track the changes to our objects and to be able to generate a history file for each such object. For the most part, this is straightforward: We can either create ...
JollyGood's user avatar
1 vote
0 answers
495 views

Postgres upsert perform multiple actions _OR_ versioning of events in an "append only" store

Background I am running on postgres 9.5 I have a table with events (in theory, it is, currently, an append only store). Certain event types can appear only once on a given date. (So currently, we ...
Yaneeve's user avatar
  • 177
5 votes
3 answers
780 views

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 ...
User1974's user avatar
  • 1,477
2 votes
1 answer
204 views

Database upgrade script for removing columns

So I have a SQL database project which tracks our application's database schemas, stored procedures, etc. It also has deployment script generation sections, including pre- and post-deployment scripts. ...
Nate Diamond's user avatar
0 votes
1 answer
179 views

Design rules to make database easier to maintain

I'm designing a small system that includes an API and a Database. On the API side I'm familiar with versioning the functions (Endpoints) to allow older clients to continue working. I'm looking for ...
Johan's user avatar
  • 613
3 votes
2 answers
988 views

Restore a SQL 7 database into SQL 2014 without in-between versions?

Our software is over 20 years old, and dates way back to databases such as Sybase and SQL 7. Now let's ignore the Sybase part. We have many old customers using very old versions of our software, ...
Jerry Dodge's user avatar
2 votes
1 answer
1k views

Rowversion database lock performance

As the documentation says, rowversion data type is auto-updated every time a row is updated using a global auto-incremented number within the entire database scope. My question is the following: Say ...
Ignacio Hagopian's user avatar
2 votes
1 answer
174 views

Conflict resolution in versioned tables

I'm trying to merge two workspaces that contain version-enabled tables. I can't figure out how to resolve conflicts, because I have a lot of rows with the same ID value. They differ only in time ...
Sergey Bolotaev's user avatar
2 votes
1 answer
866 views

How to maintain Database audits?

I am using MySQL database (with Django ORM). I want to maintain the database audits similar to StackOverflow, Quora, Wikipedia, etc. These websites maintain the revisions of the changes in the ...
Ashish Gupta's user avatar
2 votes
1 answer
62 views

Class- and instance- type rows

I have a problem, which I expect is very common, but I am not sure what to search for, as it can be explained in many different ways. I am designing a database structure for managing invoices, ...
Eldamir's user avatar
  • 297
2 votes
0 answers
320 views

Keeping version history / Slowly changing dimension

We're creating a new version of a 20 years old database, in which we used to keep version history by creating new rows in each table (SCD Type 2). SCD Type 2 is not that hard when you need to keep ...
Midas's user avatar
  • 123
1 vote
1 answer
287 views

Catalogue versioning

Apologies if this is not the right SE site to ask. I'm designing an application for catalogue management. Any element in the hierarchy has to be versioned, i.e. when I change a property of it, a new ...
algiogia's user avatar
  • 113
0 votes
1 answer
335 views

How to track changes to a record that spans multipe tables

I have the challenge to design a new database using a RDBMS, Oracle, to be precise. In this database there will be one specific record/document which may span multiple tables. With as little ...
lordvlad's user avatar
  • 147
4 votes
1 answer
879 views

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 ...
Alessandro Cappello's user avatar
3 votes
1 answer
555 views

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 ...
The user with no hat's user avatar
1 vote
1 answer
521 views

In SQLCMD is there any way of shelling out to run arbitrary programs?

In the past I have dropped json into a table in a nvarchar(Max) column. This is structured data but it needed serving back to the client as json and came in from the client as json so it seemed a bit ...
JonnyRaa's user avatar
  • 279
3 votes
1 answer
3k views

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 (...
danpelota's user avatar
  • 133
2 votes
0 answers
837 views

Are there any databases with out of the box Point-in-Time-Architecture support?

I am looking for the descriptive term of a database system (available in the cloud, or to be deployed on a local server) that has build-in support for a point-in-time (PTA) architecture. I am not sure ...
Rabarberski's user avatar
0 votes
2 answers
49 views

Set of datetime versioned rows, how to build the most up to date row

I created this SQL fiddle with the sample data The sample data is this: -- -- Table structure for table `dogs` -- CREATE TABLE IF NOT EXISTS `dogs` ( `version_stamp` datetime NOT NULL, `dog_id`...
Toskan's user avatar
  • 211
6 votes
3 answers
2k views

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 ...
Nipheris's user avatar
  • 161
2 votes
1 answer
1k views

How do I add versioning to this CMS I'm designing?

As you can see, my CMS consists of several widgets (for example an Articles widget), of which you can create several instances, which I call blocks, and those blocks are linked to pages and the ...
Evert's user avatar
  • 121
3 votes
1 answer
542 views

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 ...
cecilli0n's user avatar
  • 305
29 votes
4 answers
18k views

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) ...
magnattic's user avatar
  • 469
4 votes
1 answer
8k views

Data History Table vs Using a Current Record Flag [closed]

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 ...
AnotherDeveloper's user avatar
0 votes
1 answer
107 views

Running several versions of a MySQL database in parallel?

We have a central read-only MySQL database that contains geo data, etc. It is used by various projects. As part of a refactoring, I am thinking about setting up a system for versioning the database. ...
feklee's user avatar
  • 127
6 votes
2 answers
4k views

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, ...
leeand00's user avatar
  • 1,712
2 votes
2 answers
1k views

Where to store versioned/historized records?

In our application we are using versioning records using triggers. The application is supposed to handle millions of records in the future and the history/versioning tables are there to ensure that ...
Roland Mai's user avatar
0 votes
0 answers
32 views

keeping track of certain related field changes [duplicate]

Possible Duplicate: Save history editable data RDBMS I am totally new to databases and MySQL. I am working on a project in which I have a few related fields such as question and multiple choice ...
mu_sa's user avatar
  • 101
4 votes
2 answers
3k views

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 ...
Ray's user avatar
  • 143