Questions tagged [data-versioning]
The data-versioning tag has no usage guidance.
47
questions
0
votes
1
answer
26
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 ...
- 176
1
vote
0
answers
19
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 ...
- 111
0
votes
1
answer
196
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 ...
- 3
0
votes
0
answers
81
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",...
- 265
4
votes
0
answers
157
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, ...
- 141
0
votes
1
answer
982
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,
...
- 143
0
votes
0
answers
34
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 ...
- 113
3
votes
0
answers
293
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 ...
- 141
1
vote
1
answer
29
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. ...
- 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:
...
1
vote
1
answer
76
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 ...
- 125
0
votes
2
answers
110
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 ...
- 1
2
votes
1
answer
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 ...
- 10.6k
1
vote
0
answers
47
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 ...
- 11
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 ...
- 123
13
votes
2
answers
9k
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
, ...
- 131
3
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 ...
- 173
0
votes
0
answers
322
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 ...
- 101
2
votes
0
answers
415
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 ...
- 21
1
vote
0
answers
479
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 ...
- 177
5
votes
3
answers
769
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 ...
- 1,447
2
votes
1
answer
165
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. ...
- 155
0
votes
1
answer
174
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 ...
- 613
3
votes
2
answers
963
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, ...
- 343
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 ...
2
votes
1
answer
170
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
1
answer
829
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 ...
- 175
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, ...
- 297
2
votes
0
answers
316
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 ...
- 123
1
vote
1
answer
276
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 ...
- 113
0
votes
1
answer
332
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 ...
- 147
4
votes
1
answer
843
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
1
answer
539
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
1
answer
502
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 ...
- 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 (...
- 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 ...
- 121
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`...
- 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 ...
- 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 ...
- 121
3
votes
1
answer
515
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 ...
- 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) ...
- 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 ...
- 143
0
votes
1
answer
100
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. ...
- 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, ...
- 1,702
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 ...
- 121
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 ...
- 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 ...
- 143