Best practice for data version control in sql and c#

Question

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!

Answer 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