Qual è il miglior design per una tabella di controllo/storia?

L'opzione #3 è quasi sempre la migliore, almeno per i database strutturati tradizionali. Ci sono diverse ragioni sul perché:

- Le opzioni #1 e #2 possono non duplicare l'intera riga, ma hanno un considerevole sovraccarico di memoria che può superare la semplice duplicazione della riga. Ricordate, state duplicando le intestazioni delle righe, i metadati (tabella, colonna, ecc.) per ogni valore che cambia in una singola riga aggiornata. Per molti modelli di dati, non risparmierete molto spazio in questo modo e l'archiviazione per cose come le tabelle di controllo è poco costosa perché vi si accede raramente, tranne che per aggiungere.

- Se le colonne in una tabella hanno diversi tipi di dati, come stai memorizzando tutti i valori nella stessa tabella? Potresti fare qualcosa come tradurli in testo, ma questo non sarà efficiente né per la memorizzazione né per le query.

- Le opzioni #1 e #2 sono più flessibili in termini di modifiche allo schema, ma per la #3 molti database permetteranno di applicare le modifiche allo schema in modo transazionale sia alla tabella principale che alla tabella di revisione ombra contemporaneamente. Tuttavia, è necessario decidere cosa dovrebbe accadere alla tabella di verifica se, per esempio, si elimina una colonna nella tabella principale.

- In un certo numero di database (per esempio PostgreSQL), ci sono modi veloci e generali per copiare una riga in una tabella ombra con modelli di dati comuni. Questo è il modo in cui l'auditing è spesso implementato nella pratica. L'opzione #3 può essere anche più veloce di quanto sembri.

Tuttavia, la ragione più importante è quella che hai menzionato sopra:

- L'opzione #3 rende molto facile interrogare o fare il rollback della storia perché ha lo stesso modello di dati della tabella originale. Si possono fare semplici query SQL sull'unione della tabella live e della tabella di revisione. Quesiti come "mostrami tutte le modifiche applicate a questo record nell'ultimo mese" sono semplici ed efficienti sia da scrivere che da eseguire. Non è necessaria alcuna ricostruzione delle righe dai singoli cambiamenti di valore. Se avrete mai bisogno di usare la tabella di controllo, lo apprezzerete.

Nota che quanto sopra presuppone un database tradizionale di tipo SQL con uno schema ben definito. Se il database è più orientato ai documenti (ad esempio Mongo) allora un meccanismo come l'opzione #2 ha più senso.