Esta pergunta está relacionada ao esquema que pode ser encontrado em uma das minhas outras perguntasaqui. Basicamente, no meu banco de dados, guardo usuários, locais, sensores, entre outras coisas. Todas essas coisas são editáveis no sistema pelos usuários e excluídas.

No entanto - quando um item é editado ou excluído, preciso armazenar os dados antigos; Eu preciso ser capaz de ver quais eram os dados antes da alteração.

Também existem itens não editáveis no banco de dados, como "leituras". Eles são realmente mais um registro. As leituras são registradas em relação aos sensores, porque é a leitura para um sensor específico.

Se eu gerar um relatório de leituras, preciso ver quais são os atributos de um local ou sensorno momento da leitura.

Basicamente, eu deveria ser capaz de reconstruir os dados para qualquer ponto no tempo.

Agora, eu já fiz isso antes e o fiz funcionar bem adicionando as seguintes colunas a cada tabela editável:

valid_from
valid_to
edited_by

Se valid_to = 9999-12-31 23:59:59, esse é o registro atual. Se valid_to for igual a valid_from, o registro será excluído.

No entanto, nunca fiquei satisfeito com os gatilhos que precisava usar para reforçar a consistência da chave estrangeira.

Eu posso evitar gatilhos usando a extensão do banco de dados "PostgreSQL". Isso fornece um tipo de coluna chamado "período", que permite armazenar um período de tempo entre duas datas e, em seguida, permite que você faça restrições de CHECK para evitar períodos sobrepostos. Isso pode ser uma resposta.

Gostaria de saber se existe outra maneira.

Já vi pessoas mencionando o uso de tabelas históricas especiais, mas não gosto muito de manter duas tabelas para quase todas as tabelas (embora ainda possa ser uma possibilidade).

Talvez eu possa reduzir minha implementação inicial para não me incomodar em verificar a consistência dos registros que não são "atuais" - ou seja, apenas em verificar restrições em registros onde o valid_to é 9999-12-31 23:59:59. Afinal, as pessoas que usam tabelas históricas parecem não ter verificações de restrição nessas tabelas (pelo mesmo motivo, você precisaria de gatilhos).

Alguém tem alguma opinião sobre isso?

PS - o título também menciona banco de dados auditável. No sistema anterior que mencionei, sempre há o campo editado_por. Isso permitiu que todas as alterações fossem rastreadas, para que pudéssemos sempre ver quem alterou um registro. Não sei quanta diferença isso pode fazer.

Obrigado.