I want to keep records of the history of changes, by Google I stumbled upon the Method of a single audit table ,

CREATE TABLE dbo.History ( TableName VARCHAR(50) NOT NULL ,ColumnName VARCHAR(50) NOT NULL ,RowId INT NOT NULL ,ActionType VARCHAR(50) NOT NULL ,Author INT NOT NULL ,StartDate DATETIME NOT NULL ,EndDate DATETIME NOT NULL ,IsCurrent BIT NOT NULL ) GO

In this table, I lack at least two fields, the previous one ( PreviousValue ) and the current value ( CurrentValue ), the hitch is that the values ​​will be of different types, and I don’t know what to do correctly.

For each type of value, create a separate pair, i.e. PreviousInt, CurrentInt, PreviousDecimal CurrentDecimal, etc.

Or in the appendix, before recording, cast the value to a string, and then two columns will suffice: the previous one and the current one.

Tell me how to do better, what difficulties may arise in this or that case?

    2 answers 2

    To store simple types, you can translate everything into a string type. But if you want to keep all previous values ​​in their types. So you can add two columns "start_date", "end_date" to the table with important data.

    For example:

     CREATE TABLE agreements ( id integer, start_date date, end_date date, created_user_id integer, updated_user_id integer, number string, org_name string, agreement_date date, );

    User 12 entered into a contract on the first of January:

     {'1', '2018-01-01', null, 12, 12, 'XX-001', 'Копыта и рога', '2018-01-01'}

    On January 2, it turned out that the name was entered incorrectly, and user 13 changes the name, the end_date filled in in the old record and a new line is end_date :

     {'1', '2018-01-01', '2018-01-02', 12, 12, 'XX-001', 'Копыта и рога', '2018-01-01'} {'1', '2018-01-02', null, 12, 13, 'XX-001', 'Рога и копыта', '2018-01-01'}

    Data at the moment can be obtained as follows:

     select * from agreements where start_date <= GETDATE() and isNull(end_date,GETDATE()) >= GETDATE()

    To get information for another date, you can replace all the GETDATE (), on it. For example, the data for 2018-01-01 :

     select * from agreements where start_date <= '2018-01-01' and isNull(end_date,'2018-01-01') >= '2018-01-01'

    Sorting the lines by start_date for one id we get the chronology of changes:

     select * from agreements where id = 1 order by start_date;
    • This option was also considered, and to be honest, I like it, but there are some questions: the history should be stored in the table with the original data or it’s better to put it in a separate table, if in one then there is not a sign of relevance, but what about the key? after all other tables can refer to it? - Bald
    • @Bald; No need to put in a separate table. This is a very convenient method. For actual data, end_date always null . Key id , Unique key id, start_date, end_date . Not relevant changes for the previous year, if necessary, you can delete with a simple request. delete from agreements where end_date is not null and end_date < '2018-01-01'; - Max
    • yes you are right, we must try ... - Bald
    • @ Maxim I think for actual data you need a flag, something like isActive and an index on it, then there will be no problems with sampling. - Nick Proskuryakov
    • @NickProskuryakov actual data can really be obtained through EndDate is null , IsActive allow to get the same, only faster? - Bald

    Solving a similar problem, we made a separate scheme, for example LogSchema, where we made a log table for each of the tracked ones. Thus, several problems were solved:

    1. We did not have one huge table
    2. We had no problems with data storage (dilemma with field types and method)
    3. It was enough to work with tables simply when historical data was required.

    I want to notice even that moment, it was just convenient for us to do so. You also recommend to see how you prefer.

    sql_variant to storing your value - in theory, you can try sql_variant (or better - not worth it, so there will be just a bunch of problems with it, you can read it here ) or nvarchar , as is theoretically common for everything.

    But, using the "non-native" type, you can, in theory, catch a cast error, a precision error, etc. - and you need it?

    • one
      We had no problems with data storage (dilemma with field types and method) - i.e. in fact you, the method used tables doubles ?! - Bald
    • @Bald, yes, I had to write right away. The method is still, of course, but there it was possible to afford it. Well, the data growth is not big, because change there was rarely needed. - Alex Kuznetsov