How to recover records that have been updated?

Question

How to keep a record history when it suffers update?

I have a form where it performs update data in Mysql. What I want to do is: When performing update of a record, it keeps a history of the previous.

I’m using the framework Codeigniter, do you have any function that I can perform this procedure? So far I know the $this->db->insert_id() which takes the last record inserted in the database.

Answer 1

When you have an update, the data is modified then the previous version is lost. From this, everything depends on what you consider as "history". It can be the totality of the data, only part of the data etc... This will change your strategy. Here little ideas:

Option 1: I want to memorize everything, and I have no space problem. In this case, instead of "update" the record, I will create a new one. I will put an id to know that the 2 records (old and new) are actually the "same" and a field "creation date".

 Registro 1:
 Nome: Julia Dos Santos
 Codigo: 4587
 Data de criaçao: 10/02/2015


 Registro 2:
 Nome: Julia Dos Santos De Oliveira
 Codigo: 4587
 Data de criaçao: 13/02/2015

In this case, I know, by code case, that the 2 records belong to the same person. It simply changes its name.

Option 2: I will memorize only a part of the information, and I have no space problem. In this case, I will create a new table, only for history. For example, if the person’s name can change, in contrast the date and city of birth cannot change!

 Tabela principal:
 codigo,nome, data_nascimento,cidade nascimento

 Tabela histórico:
 codigo, data, nome

In the historical table, I will put only the fields that can change (o the fields I want to memorize)

Option 3: If you have a lot of fields, the problem of solutions 1 and 2 and they take up a lot of space. For example in the case of option 1, even if you modify only one letter, you will create a second record. Even in case 2. The third option would be to save only the differentials. But, if you have 10 fields, we can imagine that once only the contents of fields 1 and 4 will change, the second time only of field 12 etc... An idea to solve this: create a "string" with the modified data, in the same order as the fields. Example:

 Registro 1:
 Nome: Julia Dos Santos
 Cidade: Brasília
 Codigo: 4587

 Registro 2:
 Nome: Julia Dos Santos De Oliveira
 Cidade: Brasília
 Codigo: 4587

 Registro 3:
 Nome: Julia Dos Santos De Oliveira
 Cidade: Bela Vista
 Codigo: 4587

Between the record 1 and 2, Julia changes name, between the 2 and 3 she changes city.

Step 1: creation of record 1. And the child then has no history.

Step 2: Julia changes her name -> old name != new name, but city stays the same. I will put only the old name in my "string", for example with the name of the field, like "TAB Name Julia Dos Santos". If you have multiple fields with modified, the "string" will have several "double "TAB value name" (you need to choose the separator correctly, but it depends on the data of vc). As I will only have one string, I land compress it (using, for example gzdeflate()) and then save to a table that will have only the date and the string.

Option 1 and but easy. It takes up space, but you can in one read, read the previous version of a record. With option 2, you need to know the fields you will save Option 3 takes up little space. It is more complicated. Also, to "rebuild" a previous record, you need to read all versions from but "previous", and record after record, rebuild the full value.

Hoping this will help you.