Table to record history record (Best way)?

Question

I am doubtful what would be the best way to create a table so that each action of a record is saved thus creating a history of it. I have the following table, but I don’t know if it’s the best.

create table sispro_historico_processo (
    id_historico_processo int(10) UNSIGNED not null primary key auto_increment,
    cd_titular int(10) not null,
    cd_processo int(10) not null,
    in_situacao_processo int(2) not null,
    dh_alteracao_registro datetime default null
);

The idea is that for each action that the record undergoes, a new record is created in this table changing practically the situation of the precession, who changed and the date/time.

thank you.

Answer 1

As William mentioned I make a "copy" of the original table by adding the other fields that inform me about what happened to what was registered type like this:

    CREATE TABLE `log_tab_fornecedor` (
  `id` int(11) NOT NULL AUTO_INCREMENT, -- PK do log
  `user_nome` varchar(30) NOT NULL,     -- nome do usuario que fez a alteraçao
  `data_hora` datetime NOT NULL,        -- DATA/HORA DA ALTERAÇÃO
  `host` varchar(45) NOT NULL,          -- MAQUINA QUE ALTEROU
  `operacao` varchar(2) NOT NULL,       -- OPERACAO I=INSERT, D=DELETE, U=UPDATE
  `for_id` int(11) NOT NULL COMMENT 'pk',   -- ATRIBUTO DA TABELA ORIGINAL
  `for_nome` varchar(45) NOT NULL COMMENT 'nome',   -- ATRIBUTO DA TABELA ORIGINAL
  `for_cnpj` varchar(18) DEFAULT NULL COMMENT 'cnpj',-- ATRIBUTO DA TABELA ORIGINAL
  `for_tipo` int(11) DEFAULT NULL COMMENT 'tipo',-- ATRIBUTO DA TABELA ORIGINAL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

and the supplier table would be like this:

    CREATE TABLE `tab_fornecedor` (
  `for_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'pk',
  `for_nome` varchar(255) NOT NULL COMMENT 'nome',
  `for_cnpj` varchar(18) DEFAULT NULL COMMENT 'cnpj',
  `for_tipo` int(11) DEFAULT NULL COMMENT 'tipo',
  `for_cpf` varchar(14) DEFAULT NULL COMMENT 'CPF',
  `for_tipo_pes` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`for_id`)
) ENGINE=InnoDB AUTO_INCREMENT=502 DEFAULT CHARSET=latin1 COMMENT='Fornecedores';

Even I separate the tables that are log’s everything in another database to get more organized.

Making a query in the log:

    mysql> select * from log_tab_fornecedor where for_id=305;
+----+-----------+---------------------+---------------+----------+--------+-----------------------+----------+----------+
| id | user_nome | data_hora           | host          | operacao | for_id | for_nome              | for_cnpj | for_tipo |
+----+-----------+---------------------+---------------+----------+--------+-----------------------+----------+----------+
| 14 | root      | 2014-07-23 09:25:28 | 192.168.1.100 | I        |    305 | MISTERIO DA FAZENDA   |          |        3 |
| 14 | root      | 2014-07-23 09:24:28 | 192.168.1.100 | U        |    305 | MINISTERIO DA FAZENDA |          |        3 |
| 15 | root      | 2014-07-23 09:25:03 | 192.168.1.100 | U        |    305 | MINISTÉRIO DA FAZENDA | NULL     |        3 |
+----+-----------+---------------------+---------------+----------+--------+-----------------------+----------+----------+

Another important detail not to stress making these Insert’s in the log table, make the triggers so that this service is automatic:

    CREATE DEFINER=`root`@`127.0.0.1` TRIGGER `local`.`tg_tab_fornecedor_after_u`
AFTER UPDATE ON `local`.`tab_fornecedor`
FOR EACH ROW
BEGIN

    DECLARE EXIT HANDLER FOR SQLEXCEPTION /*DECLARAR EXECEÇÃO*/ 
    RESIGNAL SQLSTATE '21S01' SET MESSAGE_TEXT = 'TRIGGER tg_tab_fornecedor_after_u';/*RETORNA O NOME DA TRIGGER EM CASO DE ERRO*/
    /*LOG - INSERIR NOVO REGISTRO DE LOG */
    set @id = (select ifnull(max(id)+1,1) from local_log.log_tab_fornecedor);
    insert into local_log.log_tab_fornecedor
        values(         
            @id,
            substring_index(session_user(),'@',1),
            now(),
            substring_index(session_user(),'@',-1),
            'U',
            NEW.`for_id`,
            NEW.`for_nome`,
            NEW.`for_cnpj`,
            NEW.`for_tipo`                              
        );
END

CREATE DEFINER=`root`@`127.0.0.1` TRIGGER `local`.`tg_tab_fornecedor_after_i`
AFTER DELETE ON `local`.`tab_fornecedor`
FOR EACH ROW
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION /*DECLARAR EXECEÇÃO*/ 
    RESIGNAL SQLSTATE '21S01' SET MESSAGE_TEXT = 'TRIGGER tg_tab_fornecedor_after_i';/*RETORNA O NOME DA TRIGGER EM CASO DE ERRO*/
    /*LOG - INSERIR NOVO REGISTRO DE LOG */
    set @id = (select ifnull(max(id)+1,1) from local_log.log_tab_fornecedor);
    insert into local_log.log_tab_fornecedor
        values(         
            @id,
            substring_index(session_user(),'@',1),
            now(),
            substring_index(session_user(),'@',-1),
            'D',
            OLD.`for_id`,
            OLD.`for_nome`,
            OLD.`for_cnpj`,
            OLD.`for_tipo`      
        );
END

Answer 2

I believe you’re looking for something similar to the Hibernate Envers java.

For PHP, you can choose to use Propel, that has support to versioning of records.

Once enabled on a table, the versionable behavior Activerecord Object in a Separate table each time it is saved. This Allows to Keep track of the changes made on an Object, whether to review modifications, or revert to a Previous state.

In free translation:

Once enabled in a table, the versioning behavior stores a copy of each Activerecord object in a separate table each saved. This allows monitoring changes in an object, evaluate the modifications or revert to a previous state

This link explains in more detail how to use it.