Questions tagged [data-versioning]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
0 answers
36 views

What would be a best practice for modelling a table with versioning?

I am trying to build an application that students can use to register for different courses at school. Currently I am stuck on the database modeling. Many schools offer different courses, but the ...
user avatar
0 votes
0 answers
28 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",...
user avatar
  • 266
4 votes
0 answers
57 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, ...
user avatar
0 votes
1 answer
213 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, ...
user avatar
  • 123
0 votes
0 answers
27 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 ...
user avatar
  • 113
3 votes
0 answers
108 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 ...
user avatar
  • 141
1 vote
1 answer
26 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. ...
user avatar
  • 41
0 votes
1 answer
680 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: ...
user avatar
1 vote
1 answer
48 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 ...
user avatar
0 votes
2 answers
79 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 ...
user avatar
  • 1
2 votes
1 answer
325 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 ...
user avatar
1 vote
0 answers
44 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 ...
user avatar
2 votes
1 answer
955 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 ...
user avatar
  • 123
9 votes
2 answers
6k 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 , ...
user avatar
2 votes
0 answers
1k 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 ...
user avatar
  • 163
0 votes
0 answers
228 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 ...
user avatar
  • 101
2 votes
0 answers
348 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 ...
user avatar
1 vote
0 answers
430 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 ...
user avatar
  • 177
5 votes
3 answers
712 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 ...
user avatar
  • 1,387
2 votes
1 answer
105 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. ...
user avatar
0 votes
1 answer
153 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 ...
user avatar
  • 573
3 votes
2 answers
878 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, ...
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 ...
user avatar
2 votes
1 answer
144 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 ...
user avatar
2 votes
1 answer
679 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 ...
user avatar
2 votes
1 answer
56 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, ...
user avatar
  • 297
2 votes
0 answers
308 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 ...
user avatar
  • 123
1 vote
1 answer
175 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 ...
user avatar
  • 113
0 votes
1 answer
308 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 ...
user avatar
  • 147
3 votes
1 answer
726 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 ...
user avatar
3 votes
1 answer
517 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 ...
user avatar
1 vote
1 answer
447 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 ...
user avatar
  • 259
3 votes
1 answer
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 (...
user avatar
  • 133
2 votes
0 answers
797 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 ...
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`...
user avatar
  • 191
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 ...
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 ...
user avatar
  • 121
3 votes
1 answer
442 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 ...
user avatar
  • 309
27 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) ...
user avatar
  • 429
3 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 ...
user avatar
0 votes
1 answer
84 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. ...
user avatar
  • 127
5 votes
2 answers
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, ...
user avatar
  • 1,672
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 ...
user avatar
  • 121
0 votes
0 answers
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 ...
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 ...
user avatar
  • 143