Qual é o melhor design para uma mesa de auditoria/história?

Opção #3 é quase sempre a melhor, pelo menos para bases de dados estruturadas tradicionais. Há uma série de razões para que:

- A opção #1 e #2 podem não duplicar a linha inteira, mas têm uma considerável sobrecarga de armazenamento que pode exceder apenas a duplicação da linha. Lembre-se, você está duplicando cabeçalhos de linha, metadados (tabela, coluna, etc.) para cada valor que muda em uma única linha atualizada. Para muitos modelos de dados, o usuário não economizará muito espaço dessa maneira e o armazenamento para coisas como tabelas de auditoria é barato porque raramente é acessado, exceto para anexar.

- Se as colunas em uma tabela têm tipos de dados diferentes, como você está armazenando todos os valores na mesma tabela? Você poderia fazer algo como traduzir o texto deles, mas isso não será eficiente para armazenamento ou consulta.

- As opções #1 e #2 são mais flexíveis em termos de alterações de esquema, mas para #3 muitas bases de dados permitirão que as alterações de esquema sejam aplicadas transaccionalmente tanto à tabela principal como à tabela de auditoria de sombras simultaneamente. No entanto, você precisa decidir o que deve acontecer com a tabela de auditoria se, por exemplo, você soltar uma coluna na tabela principal.

- Em vários bancos de dados (por exemplo, PostgreSQL), há maneiras rápidas e gerais de copiar uma linha para uma tabela sombra com modelos de dados comuns. Isto é como a auditoria é frequentemente implementada na prática. #3 pode ser ainda mais rápido do que parece.

No entanto, a razão mais importante é o que você mencionou acima:

- A opção #3 torna muito fácil consultar ou reverter o histórico porque tem o mesmo modelo de dados que a tabela original. Você pode fazer consultas SQL simples sobre a união da tabela ao vivo e da tabela de auditoria. Consultas como "show me all the changes applied to this record over the last month" são simples e eficientes tanto para escrever como para executar. Nenhuma reconstrução de linha a partir de alterações de valores individuais é necessária. Se você alguma vez precisar usar a tabela de auditoria, você irá apreciar isto.

>br>Nota que o acima assume uma base de dados tradicional do tipo SQL com um esquema bem definido. Se a base de dados é mais orientada para documentos (por exemplo, Mongo) então um mecanismo como a Opção #2 faz mais sentido.