Eu e alguns colegas de trabalho entramos em um debate sobre a melhor maneira de armazenar dados históricos. Atualmente, para alguns sistemas, uso uma tabela separada para armazenar dados históricos e mantenho uma tabela original para o registro ativo atual. Então, digamos que eu tenho a tabela FOO. No meu sistema, todos os registros ativos entrarão no FOO e todos os registros históricos entrarão no FOO_Hist. Muitos campos diferentes no FOO podem ser atualizados pelo usuário, por isso quero manter uma conta precisa de tudo atualizado. FOO_Hist contém exatamente os mesmos campos que FOO, com exceção de um HIST_ID de incremento automático. Toda vez que o FOO é atualizado, eu executo uma instrução de inserção no FOO_Hist semelhante a:insert into FOO_HIST select * from FOO where id = @id.

Meu colega de trabalho diz que esse design é ruim porque não devo ter uma cópia exata de uma tabela por motivos históricos e apenas inserir outro registro na tabela ativa com um sinalizador indicando que é para fins históricos.

Existe um padrão para lidar com o armazenamento de dados históricos? Parece-me que não quero desorganizar meus registros ativos com todos os meus registros históricos na mesma tabela, considerando que pode haver mais de um milhão de registros (estou pensando a longo prazo).

Como você ou sua empresa lida com isso?

Estou usando o MS SQL Server 2008, mas gostaria de manter a resposta genérica e arbitrária de qualquer DBMS.