Progettazione del database per la registrazione di controllo

Ogni volta che ho bisogno di desing un nuovo database spendo un po ‘di tempo a pensare su come dovrei impostare lo schema del database per mantenere un registro di controllo delle modifiche.

Alcune domande sono già state poste qui a riguardo, ma non sono d’accordo sul fatto che esiste un unico approccio migliore per tutti gli scenari:

  • Progettazione di database per revisioni
  • Miglior design per una tabella di database di controllo del log di changelog
  • Idee sulla progettazione di database per l’acquisizione di scie di controllo

Mi sono anche imbattuto in questo interessante articolo sulla gestione di un registro delle modifiche del database che tenta di elencare i pro ei contro di ciascun approccio. È scritto molto bene e ha informazioni interessanti, ma ha reso le mie decisioni ancora più difficili.

La mia domanda è: c’è un riferimento che posso usare, forse un libro o qualcosa di simile a un albero decisionale a cui posso riferire per decidere in quale direzione devo andare basandomi su alcune variabili di input, come:

  • La maturità dello schema del database
  • Come saranno interrogati i registri
  • La probabilità che sia necessario ricreare i record
  • Cosa c’è di più importante: scrivere o leggere le prestazioni
  • Natura dei valori che vengono registrati (stringa, numeri, blob)
  • Spazio di archiviazione disponibile

Gli approcci che conosco sono:

    1. Aggiungi le colonne per la data e l’utente creati e modificati

    Esempio di tabella:

    • id
    • value_1
    • value_2
    • VALUE_3
    • Data di Creazione
    • modifed_date
    • creato da
    • modificato da

    Maggiori svantaggi: perdiamo la cronologia delle modifiche. Imansible eseguire il rollback dopo il commit.

    2. Inserisci solo tabelle

    Esempio di tabella :

    • id
    • value_1
    • value_2
    • VALUE_3
    • a partire dal
    • a
    • cancellato (booleano)
    • utente

    Maggiori svantaggi: come mantenere aggiornate le chiavi esterne? Necessario spazio enorme

    3. Creare una tabella della cronologia separata per ogni tabella

    Esempio di tabella di storia:

    • id
    • value_1
    • value_2
    • VALUE_3
    • VALUE_4
    • utente
    • cancellato (booleano)
    • timestamp

    Maggiori svantaggi: è necessario duplicare tutte le tabelle controllate. Se lo schema cambia, sarà necessario migrare anche tutti i log.

    4. Creare una tabella cronologia consolidata per tutte le tabelle

    Esempio di tabella di storia:

    • table_name
    • campo
    • utente
    • NEW_VALUE
    • cancellato (booleano)
    • timestamp

    Svantaggi principali: Sarò in grado di ricreare i record (rollback) se necessario facilmente? La colonna new_value deve essere una stringa enorme in modo che possa supportare tutti i diversi tipi di colonna.

    Un metodo che viene utilizzato da alcune piattaforms wiki è quello di separare i dati identificativi e il contenuto che stai audendo. Aggiunge complessità, ma si finisce con una pista di controllo di record completi, non solo elenchi di campi che sono stati modificati e quindi si devono mescolare per dare all’utente un’idea di come fosse il vecchio record.

    Ad esempio, se disponi di una tabella chiamata Opportunità per monitorare le offerte di vendita, in realtà creerai due tabelle separate:

    Opportunità
    Opportunities_Content (o qualcosa del genere)

    La tabella Opportunità avrebbe le informazioni che utilizzeresti per identificare in modo univoco il record e ospiterà la chiave primaria che avresti utilizzato per le tue relazioni con le chiavi esterne. La tabella Opportunities_Content contiene tutti i campi che gli utenti possono modificare e per i quali desideri tenere traccia di controllo. Ogni record nella tabella Contenuto includerebbe il proprio PK e i dati di modifica e data di modifica. La tabella Opportunità includerebbe un riferimento alla versione corrente e informazioni su quando il record principale è stato originariamente creato e da chi.

    Ecco un semplice esempio:

    CREATE TABLE dbo.Page( ID int PRIMARY KEY, Name nvarchar(200) NOT NULL, CreatedByName nvarchar(100) NOT NULL, CurrentRevision int NOT NULL, CreatedDateTime datetime NOT NULL 

    E i contenuti:

     CREATE TABLE dbo.PageContent( PageID int NOT NULL, Revision int NOT NULL, Title nvarchar(200) NOT NULL, User nvarchar(100) NOT NULL, LastModified datetime NOT NULL, Comment nvarchar(300) NULL, Content nvarchar(max) NOT NULL, Description nvarchar(200) NULL 

    Probabilmente renderei il PK della tabella dei contenuti una chiave a più colonne da PageID e Revision forniti. Revision era un tipo di id quadro. Dovresti usare la colonna Revisione come FK. Quindi estrarre il record consolidato mediante JOINing in questo modo:

     SELECT * FROM Page JOIN PageContent ON CurrentRevision = Revision AND ID = PageID 

    Potrebbero esserci degli errori lassù … questo è fuori di testa. Dovrebbe darti un’idea di un modello alternativo, però.

    Josh

    Se si sta utilizzando SQL Server 2008, è probabilmente necessario prendere in considerazione l’acquisizione di Data Change. Questo è nuovo per il 2008 e potrebbe farti risparmiare una notevole quantità di lavoro.

    Non conosco alcun riferimento, ma sono sicuro che qualcuno ha scritto qualcosa.

    Tuttavia, se lo scopo è semplicemente quello di avere una registrazione di quello che è successo – l’uso più tipico di un registro di controllo – allora perché non tenere semplicemente tutto:

     timestamp username ip_address procedureName (if called from a stored procedure) database table field accesstype (insert, delete, modify) oldvalue newvalue 

    Presumibilmente questo è mantenuto da un grilletto.

    Penso che non ci sia niente come un albero decisionale. Poiché alcuni dei pro e contro (o dei requisiti) non sono realmente numerabili. Come si misura la maturità, ad esempio?

    Quindi, allinea i requisiti aziendali per la registrazione di controllo. Cerca di prevedere come questi requisiti potrebbero cambiare in futuro e generare i tuoi requisiti tecnici. Ora puoi confrontarlo con i pro e i contro e scegliere l’opzione giusta / migliore.

    E sii certo, non importa come tu decida, ci sarà sempre qualcuno che pensa che tu abbia preso la decisione sbagliata. Tuttavia, hai fatto i compiti e hai giustificato la tua decisione.

    Creeremo un piccolo database di esempio per un’applicazione di blogging. Sono necessari due tavoli:

    blog : memorizza un ID post univoco, il titolo, il contenuto e una bandiera cancellata. audit : memorizza una serie di modifiche storiche di base con un ID record, l’ID del post del blog, il tipo di modifica (NEW, EDIT o DELETE) e la data / ora di tale modifica. Il seguente SQL crea il blog e indicizza la colonna eliminata:

     CREATE TABLE `blog` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `title` text, `content` text, `deleted` tinyint(1) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `ix_deleted` (`deleted`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='Blog posts'; 

    Il seguente SQL crea la tabella di audit . Tutte le colonne sono indicizzate e una chiave esterna è definita per audit.blog_id che fa riferimento a blog.id. Pertanto, quando eliminiamo fisicamente un post di blog, viene rimossa anche la cronologia completa del controllo.

     CREATE TABLE `audit` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `blog_id` mediumint(8) unsigned NOT NULL, `changetype` enum('NEW','EDIT','DELETE') NOT NULL, `changetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `ix_blog_id` (`blog_id`), KEY `ix_changetype` (`changetype`), KEY `ix_changetime` (`changetime`), CONSTRAINT `FK_audit_blog_id` FOREIGN KEY (`blog_id`) REFERENCES `blog` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;