Versioning of data in MySQL

In 1C there is a very useful feature - periodic details, with the help of them you can store not only the value, but also the entire history of its changes. For example, if we store the value of the dollar exchange rate in relation to the ruble, then not only the current value is stored, but also the value of yesterday, the day before yesterday, etc. (the storage period can be any). In MySQL, unfortunately, there is no such feature (probably not necessary). So let's do it ourselves;).

Consider an example. Suppose we have a simple filing system based on the TTroubleticket table.

Table TTroubleticket
fkey	request number
fcontact	application contact information
ftext	application text

In addition to contact information and the text of the application, you must keep the status of the application with the entire history of its changes. Such a solution immediately suggests itself: store the status in another table with the entire history of changes.

TStatusHistory table
fkey
f_troubleticket	link to entry in TTroubleticket
fvalue	value
fsdate	date of assignment

Then to get the current value we need to run the following query:

     SET @f_troubleticket = 10;
     SELECT
         tt.fkey, tt.fcontact, tt.ftext,
         (SELECT 
             fvalue 
          FROM 
             TStatusHistory 
          WHERE 
             f_troubleticket = tt.fkey 
          ORDER BY 
             fsdate DESC 
          LIMIT 0.1
         ) AS fstatus
     FROM 
         TTroubleticket AS tt
     WHERE
         fkey = @f_troubleticket
     LIMIT 0, 1 

Everything is smooth, everything is good, nothing superfluous, but if you wish, we can always view the entire history of status changes. The first difficulties will begin when we try to get the value on some date. Therefore, we will add another fedate field - the date when the value action ends. Let redundant information be stored in this field, but it will make life very useful for us.

TStatusHistory table
fkey
f_troubleticket	link to entry in TTroubleticket
fvalue	value
fsdate	date of assignment
fedate date	completion action values

Now, to get the value for the date, we need to change the query a little:

    
     SET @f_troubleticket = 10;
     SET @dt = 20090401000000;  # April 01, 2009 00:00
     SELECT
         tt.fkey, tt.fcontact, tt.ftext,
         (SELECT 
             fvalue 
          FROM 
             TStatusHistory 
          WHERE 
             f_troubleticket = tt.fkey 
             AND @dt BETWEEN fsdate AND fedate 
          ORDER BY 
             fsdate DESC 
          LIMIT 0.1
         ) as fstatus
     FROM 
         TTroubleticket AS tt
     WHERE
         fkey = @f_troubleticket
     LIMIT 0, 1

')
With this complexity handled. But time goes on, and the system is improving: we needed such a parameter as the type of application and also periodic. Bullshit! -Add more table.

TTypeHistory table
fkey
f_troubleticket	link to entry in TTroubleticket
fvalue	value
fsdate	date of assignment
fedate	expiration date of the value

Zaros converted:

    
     SET @f_troubleticket = 10;
     SET @dt = 20090401000000;  # April 01, 2009 00:00
     SELECT
         tt.fkey, tt.fcontact, tt.ftext,
         (SELECT 
             fvalue 
           FROM 
             TStatusHistory 
           WHERE 
             f_troubleticket = tt.fkey 
             AND @dt BETWEEN fsdate AND fedate 
           ORDER BY 
             fsdate DESC 
           LIMIT 0.1
         ) AS fstatus,
         (SELECT 
             fvalue 
           FROM 
             TTypeHistory 
           WHERE 
             f_troubleticket = tt.fkey 
             AND @dt BETWEEN fsdate AND fedate 
           ORDER BY 
             fsdate DESC 
           LIMIT 0.1
         ) AS ftype
     FROM 
         TTroubleticket AS tt
     WHERE
         fkey = @f_troubleticket 
     LIMIT 0, 1 

And over time, we may receive a bunch of additional tables, i.e. the system is complicated, and there is nothing good about it.
We will improve our approach - we will not create separate tables for each periodic variable, we will store all periodic variables in one table.

Table thistory
fkey
f_troubleticket	link to entry in TTroubleticket
fstatus	status
ftype	type of
fsdate date	value assignments
fedate date	completion action values

Also, all current values ​​will be stored in the main table.

Table TTroubleticket
fkey	request number
fcontact	application contact information
ftext	application text
fstatus	current status
ftype	current type

And now when we need to get the actual values, we just need to perform a simple query:

    
     SET @f_troubleticket = 10;
     SELECT
         fkey, fcontact, ftext,
         fstatus
         ftype
     FROM 
         TTroubleticket
     WHERE
         fkey = @f_troubleticket
     LIMIT 0, 1 

Well, if we need to get the values ​​for the date, we use the LEFT JOIN construction:

    
     SET @f_troubleticket = 10;
     SET @dt = 20090401000000;  # April 01, 2009 00:00
     SELECT
         tt.fkey, tt.fcontact, tt.ftext,
         his.fstatus
         his.ftype
     FROM 
         TTroubleticket AS tt
             LEFT JOIN TTroubleticketHistory as his 
                 ON his.f_troubleticket = tt.fkey 
                      AND @dt BETWEEN his.fsdate AND his.fedate
     WHERE
         tt.fkey = @f_troubleticket
     LIMIT 0, 1 

That's all. It seems to be a very difficult decision. And how do you store periodic values?

ps Thanks for the idea A. Matveyev.