-2

In this question i want to figureout, what is the best practice to control versions of data in sql. We are useing a relational database (Sybase SAP Sql Anywhere). The problem is, we don't know in which layer of our software we should implement a version control system. We want to write a generic system, so that version control is available for all types of data with a small amout of work for every type (Types: Contacts, Appointments, ...).

Here are the options we figured out: 1. Using an entity framework and calculating the difference of two models. Then saving the difference to the database 2. Using triggers and comapre old and new data and save them in separate table 3. Using procedures which proof for changes and save them also in a separate table

I know it's a very general question, but maybe some one has a good idea and solution for our problem.

Edit

Important: I want to create versions of the data itself, not of the sql schema or some sql code.

EDIT2

Lets use the following simple example. I have a tiny contact table (not our real contact table):

CREATE TABLE Contact
(
    "GUID" Uniqueidentifier NOT NULL UNIQUE,
    "ContactId" BIGINT NOT NULL Identity(1,1),
    "Version" INTEGER NOT NULL,
    "FirstName" VARCHAR(100),
    "LastName" VARCHAR(200),
    "Address" VARCHAR(400),
    PRIMARY KEY (ContactId, Version)
);

No, every time some one made changes to the contact object, i want to save a new version of it. But im am looking for a general solution. This must be implemented for every type.

Thank you!

BendEg
  • 20,098
  • 17
  • 57
  • 131
  • Four of the first five questions in the **Related** list ===>>> seem to discuss this very question. None of them contained information that was useful? Your question as asked is very broad, and seems like more of a discussion question than a specific one. – Ken White May 29 '15 at 15:17
  • No, this questions are about the structure and sql-code. I want to versioning my datas itself. – BendEg May 29 '15 at 15:27
  • The data? I believe doing that is called *doing a backup*. :-) – Ken White May 29 '15 at 15:30
  • No, we want to log, which person made changes to the data. For example change a phone number must be tracket. So the version control will look like a kind of log, or data history. The customer wants to move to older data, or restore versions if some employee made mistakes. It's not like a backup. – BendEg May 29 '15 at 15:32
  • 1
    That's an audit table. A Google search on *database audit table* should turn up some information. You should put the details from your last comment into the question as well; it's a totally different meaning from what you've asked in the question. – Ken White May 29 '15 at 15:36
  • It's not very clear at all what you're asking. It really looks like you want a SVN-like tool, but in fact, you want a wikipedia-diff-like tool. Have you looked at the way wikis do diffs of their articles ? – thomasb May 29 '15 at 15:56
  • Ok guys, what is so hard to understand? I don't want some thing like SVN. *database audit table* comes my solution very near, but not exactly what i'm looking for. Please look at my **EDIT 2** – BendEg May 29 '15 at 20:09

2 Answers2

1

As someone who live and breathe database source control (part of amazing team at DBmaestro), I can recommend on combination of 2 methods, depending on how you run the delta.

  1. Using triggers you should save all information you need for the deployment, if it by using slow change dimension or entire table content
  2. Using procedure that analyze difference and knows to generate the relevant delta script
Uri
  • 190
  • 1
  • 3
1

we have the same issue and try to solve it by storing a version and a branch id with every entity we want to follow. In a different table we store the versions with their predecessor version id, so we can trace where branches meet each other. Seperatly we have an audit trace with the version number.

I wonder if this has the same elements you need(ed) and whether you advanced since your question and your last edit.

Thanks for the suggestion to combine the unique id and the version number

Gert-Jan Biersteker
  • 11
  • 1