Table to record record history (Best form)?

Question

Table to record record history (Best form)?

Navigation

#1 by (3 votes)
#2 by (0 votes)

6

I have a question about how best to create a table so that every action in a record is saved, thus creating a history of it. I have the following table, but I do not know if it is the best one.

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 registry undergoes, a new registry is created in this table, practically changing the situation of the process, who changed it and the date / time.

Thank you.

php mysql sql

asked by anonymous 19.05.2015 / 17:57

2 answers

How to serve files with access control in Django? Retrieve digital certificate information

score 3 · Answer 1

As William mentioned I make a "copy" of the original table 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 provider tab looks 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 stay more organized.

Querying 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 for not stressing these inserts in the log table, make the triggers for this service to be automated:

    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

score 0 · Answer 2

I think you're looking for something like Java's Hibernate Envers .

For PHP, you can choose to use Propel , which supports versioning of the records .

Once enabled on a table, the versionable behavior stores a copy of the ActiveRecord object in a separate table each time it is saved. This allows to keep track of changes made on an object, whether to review modifications, or revert to a previous state.

Free translation:

Once enabled in a table, the versioning behavior saves a copy of each ActiveRecord object in a separate table every one that is saved. This allows you to monitor changes in an object, evaluate changes or revert to an earlier state

This link explains in more detail how to use it.