Questions tagged [data-versioning]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0
votes
1answer
97 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, ...
0
votes
0answers
21 views

PostgreSQL cascading triggers based on the condition of which other table triggered it or a value given by the other trigger?

I want to keep (slightly different) tables synced to one another and with only 2 tables, this is fine. Because of pg_trigger_depth() = 0 in the WHEN clause they will not trigger each other. I ...
0
votes
0answers
23 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 ...
3
votes
0answers
37 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 ...
1
vote
1answer
18 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. ...
0
votes
1answer
185 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: ...
1
vote
1answer
29 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 ...
0
votes
2answers
61 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 ...
2
votes
1answer
201 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 ...
1
vote
0answers
32 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 ...
2
votes
1answer
716 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 ...
8
votes
2answers
3k 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 , ...
2
votes
0answers
665 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 ...
0
votes
0answers
158 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 ...
2
votes
0answers
315 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 ...
1
vote
0answers
370 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 ...
5
votes
3answers
663 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 ...
2
votes
1answer
81 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. ...
0
votes
1answer
132 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 ...
3
votes
2answers
754 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, ...
2
votes
1answer
945 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 ...
2
votes
1answer
128 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 ...
2
votes
1answer
615 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 ...
2
votes
1answer
55 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, ...
2
votes
0answers
301 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 ...
1
vote
1answer
123 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 ...
0
votes
1answer
260 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 ...
3
votes
1answer
666 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 ...
3
votes
1answer
497 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 ...
1
vote
1answer
428 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 ...
2
votes
1answer
2k 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 (...
2
votes
0answers
768 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 ...
0
votes
2answers
48 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`...
5
votes
3answers
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 ...
2
votes
1answer
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 ...
3
votes
1answer
313 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 ...
27
votes
4answers
17k 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) ...
3
votes
1answer
7k 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 ...
0
votes
1answer
65 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. ...
5
votes
2answers
3k 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, ...
2
votes
2answers
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 ...
0
votes
0answers
31 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 ...
4
votes
2answers
2k 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 ...