How to keep a record of a record when it has update ?
I have a form where it does update of data in MySQL. What I want to do is: When performing a update of a record, it keeps a history of the above.
I'm using the framework Codeigniter, does it have any function that I can perform this procedure? So far I know the $this->db->insert_id() that takes the last record inserted in the database.
When it has an update, the data is modified then the previous and lost version. From this, everything depends on what you consider to be "historical." It may be the totality of the data, only part of the data etc ... This will change your strategy. Here are some small ideas:
Option 1: I want to memorize everything, and I have no space problem. In this case, instead of "update" the registry, I will create a new one. I will put an id to know that the 2 records (the old and the new) are really 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 case of the code, that the 2 records belongs to the same person. It simply changes its name.
Option 2: I will only memorize a part of the information, and I have no space problem. In this case, I'll create a new table, just for history. For example, if the person's name can change, otherwise the date and city of birth can not change!
Tabela principal:
codigo,nome, data_nascimento,cidade nascimento
Tabela histórico:
codigo, data, nome
In the history table, I'll only put the fields that can change (the fields I want to memorize)
Option 3: If you have a lot of fields, the problem of solutions 1 and 2 and that 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 differences. 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 record 1 and 2, Julia changes her name, between 2 and 3 she changes her city.
Step 1: Create the record 1. And the child then has no history.
Step 2: Julia modifies her name - > Old name = new name, but city stays the same. I will only put the old name in my "string", for example with the name of the field, type "Name TAB Julia Dos Santos". If you have multiple fields with modified, the string will have several "double" TAB name value "(you need to choose the tab correctly, but it depends on your data). As I will only have one string, I will crash it (using gzdeflate ()) and then save it to a table that will only have the date and string.
Option 1 is the easiest option. It takes up space, but you can in a single read, read the previous version of a record. With option 2, you need to know the fields that you will save Option 3 takes up little space. She is more complicated. Also, to "rebuild" a previous record, need to read all versions from the "but" previous, and record after record, rebuild the full value.
Hoping this will help you.