Qual è il modo corretto di salvare le informazioni di controllo in un database

È un modo comune e perfettamente valido per farlo. Alcuni dei principali pacchetti ERP, come ad esempio SAP, fanno esattamente questo.

Ci sono due problemi da affrontare:

• Assicurarsi che questi campi siano gestiti in modo affidabile. Sono inutili se un UPDATE canaglia può cambiare la riga senza che i campi vengano aggiornati (suggerimenti: i trigger db come suggerito nel comando di Robert, o tramite il livello gateway del database se l'accesso db è protetto in modo appropriato).
• Assicurati che questi campi non possano essere manomessi se la pista di controllo è critica e l'accesso a db non può essere completamente controllato. Questo è più delicato, ma potrebbe essere ottenuto utilizzando un hash aggiuntivo (o meglio una firma) calcolato con tutti i dati rilevanti.

C'è un'alternativa all'avere i campi in tutte le tabelle. Puoi mettere tutti i tuoi 4 campi di audit trail insieme a un ID oggetto (tabella + ID univoco) da qualche altra parte. Potrebbe essere un'altra tabella (potresti limitare l'accesso ad essa) o in un file che funge da registro di controllo (che potrebbe essere fuori dal raggio d'azione del dba per garantire la segregazione dei compiti, ma che sarebbe molto meno conveniente da usare), o entrambi .

La tua soluzione non è male. Ecco una soluzione alternativa (non migliore o peggiore in generale, solo diversa). Per molti sistemi, è comune avere una convenzione uniforme per le chiavi primarie del seguente modulo:

• nessun PK composito

• ogni PK ha lo stesso tipo di dati (o almeno si adatta a un tipo di dato noto)

Se il tuo sistema segue questo criterio, puoi memorizzare le informazioni di cui sopra in una tabella audit separata con colonne

• Nome tabella (stringa)

• PKValue (stesso tipo delle chiavi primarie)

• Operazione (valori che rappresentano Create o Update (o forse anche Delete )

• TimeStamp

• OperationBy (tabella da FK a persona )

Alcuni vantaggi di questa soluzione:

• più normalizzato,

• non è necessario aggiungere quattro colonne aggiuntive a ciascuna delle tabelle esistenti

• consente di tenere traccia delle operazioni di cancellazione

• le informazioni di controllo si trovano in un unico punto, il che potrebbe consentire di mettere a punto parametri di archiviazione specifici per questa tabella

• una query sull'intera tabella di controllo diventa più semplice

• i diritti di accesso alle informazioni di controllo sono indipendenti dai diritti di accesso alle tabelle controllate

Svantaggi:

• hai bisogno di questa convenzione per i PK

• ogni aggiornamento o creazione deve ora modificare due record anziché uno

• se i record delle tabelle principali vengono eliminati, le informazioni di controllo non vengono eliminate automaticamente con loro

• una query per l'ultima modifica di un record specifico ora richiede un'operazione join

• i diritti di accesso alle informazioni di controllo sono indipendenti dai diritti di accesso alle tabelle controllate (ho già detto in precedenza: -)?

Quindi, come vedi, entrambi gli approcci differiscono nei dettagli e dovresti verificare quale si adatta meglio alle tue esigenze.

Se hai solo bisogno di vedere la ultima modifica (e chi l'ha creata), allora questo è perfettamente adeguato.

Se è necessario vedere una cronologia delle modifiche, è necessario duplicare questi dati in altre tabelle, preferibilmente utilizzando i trigger in modo che nessuna modifica "scivoli attraverso la rete".