Design di database temporali, con una svolta (righe in diretta o di bozza)

Sto cercando di implementare il controllo delle versioni degli oggetti con il touch in più del bisogno di avere sia oggetti live che bozze, e potrei usare le intuizioni di qualcuno in questo, poiché sto cominciando a chiedermi se è ansible anche senza hack orribili.

Scoprirò i post con i tag per l’esempio, ma il mio caso d’uso è un po ‘più generico (che coinvolge dimensioni che cambiano lentamente – http://en.wikipedia.org/wiki/Slowly_changing_dimension ).

Supponi di avere una tabella dei post, una tabella dei tag e una tabella post2tag:

posts ( id ) tags ( id ) post2tag ( post_id fkey posts(id), tag_id fkey tags(id) ) 

Ho bisogno di un paio di cose:

  1. Essere in grado di mostrare esattamente l’aspetto di un post in un tempo determinato arbitrario, incluse le righe eliminate.
  2. Tieni traccia di chi sta modificando cosa, per un audit trail completo.
  3. Ha bisogno di una serie di viste materializzate (tabelle “live”) al fine di mantenere l’integrità referenziale (cioè la registrazione dovrebbe essere trasparente per gli sviluppatori).
  4. Deve essere adeguatamente veloce per vivere e le ultime righe di progetto.
  5. Essere in grado di avere una bozza di post coesistere con un post in diretta.

Ho indagato su varie opzioni. Finora, il meglio che ho trovato (senza punti # 4 / # 5) assomiglia un po ‘all’impostazione ibrida SCD type6, ma invece di avere un booleano corrente c’è una vista materializzata per la riga corrente. A tutti gli effetti, assomiglia a questo:

 posts ( id pkey, public, created_at, updated_at, updated_by ) post_revs ( id, rev pkey, public, created_at, created_by, deleted_at ) tags ( id pkey, public, created_at, updated_at, updated_by ) tag_revs ( id, public, rev pkey, created_at, created_by, deleted_at ) post2tag ( post_id fkey posts(id), tag_id fkey tags(id), public, created_at, updated_at, updated_by ) post2tag_revs ( post_id, tag_id, post_rev fkey post_revs(rev), -- the rev when the relation started tag_rev fkey tag_revs(rev), -- the rev when the relation started public, created_at, created_by, deleted_at, pkey (post_rev, tag_rev) ) 

Sto usando pg_temporal per mantenere gli indici sul periodo (created_at, deleted_at). E mantengo le varie tabelle sincronizzate usando i trigger. Yada yada yada … Ho creato i trigger che consentono di cancellare una modifica di post / tag in modo tale che la bozza venga salvata nei revs senza essere pubblicata. Funziona alla grande.

Tranne quando devo preoccuparmi delle relazioni relative alla bozza di riga su post2tag. In quel caso, si scatena l’inferno, e questo mi suggerisce che ho un qualche tipo di problema di progettazione. Ma sono a corto di idee …

Ho preso in considerazione l’introduzione della duplicazione dei dati (cioè n file post2tag introdotte per ogni revisione di bozza). Questo tipo di opere, ma tende ad essere molto più lento di quanto mi piacerebbe essere.

Ho preso in considerazione l’introduzione di tabelle delle bozze per l’ultima bozza, ma questo tende rapidamente a diventare molto molto brutto.

Ho considerato tutti i tipi di bandiere …

Quindi domanda: esiste un mezzo generalmente accettato per la gestione delle righe live vs non live in un ambiente controllato dalla versione di riga? E se no, con cosa hai provato e hai avuto ragionevolmente successo?

La modellazione di ancoraggio è un buon modo per implementare un dB temporale – vedi anche l’ articolo di Wikipedia . Prende un po ‘di tempo per abituarsi, ma funziona bene. C’è uno strumento di modellazione online e se carichi il file XML fornito [File -> Load Model from Local File] dovresti vedere qualcosa di simile – usa anche [Layout --> Togle Names] .

inserisci la descrizione dell'immagine qui

Il [Generate --> SQL Code] produrrà DDL per tabelle, viste e funzioni point-in-time. Il codice è piuttosto lungo, quindi non lo sto postando qui. Controlla il codice – potrebbe essere necessario modificarlo per il tuo DB.

Ecco il file da caricare nello strumento di modellazione.

                                                              

Ho implementato un database temporale utilizzando SCD type 2 e PostgreSQL Rules e Triggers, e lo ho avvolto in un pacchetto autonomo per ActiveRecord: http://github.com/ifad/chronomodel

Il design è indipendente dalla lingua / framework, tuttavia è ansible creare regole e trigger manualmente e il database si prenderà cura di tutto il resto. Dai un’occhiata a https://github.com/ifad/chronomodel/blob/master/README.sql .

Anche l’indicizzazione e l’interrogazione efficienti dei dati temporali mediante operatori geometrici sono inclusi come bonus. 🙂

post2tag_revs ha un problema in quanto sta cercando di esprimere 2 concetti fondamentalmente diversi.

Un tag applicato a una bozza di revisione del post si applica solo a quella revisione, a meno che la revisione non venga mai pubblicata.

Una volta pubblicato un tag (ovvero associato a una revisione post pubblicata), si applica a ogni revisione futura del post fino a quando non viene revocato.

E l’associazione con una revisione pubblicata, o non esplicativa, non è necessariamente simultanea con la pubblicazione di una revisione, a meno che non si imponga artificialmente questo clonando una revisione solo così è ansible associare aggiunte o rimozioni di tag …

Modificherei il modello rendendo post2tag_revs.post_rev rilevante solo per i tag draft. Una volta pubblicata la revisione (e il tag è attivo), utilizzerei una colonna temporale per contrassegnare l’inizio e la fine della validità pubblicata. Puoi o non vuoi una nuova voce post2tag_revs per rappresentare questa modifica.

Come fai notare, questo rende questa relazione bi-temporale . Potresti migliorare le prestazioni nel caso “normale” aggiungendo un valore booleano a post2tag per indicare che il tag è attualmente associato al post.

Penso di averlo inchiodato. Fondamentalmente, aggiungi un campo di bozze (univoco) alle tabelle pertinenti e lavori sulle bozze come se fossero un nuovo post / tag / etc .:

 posts ( id pkey, public, created_at stamptz, updated_at stamptz, updated_by int, draft int fkey posts (id) unique ) post_revs ( id, public, created_at, created_by, deleted_at, pkey (id, created_at) ) tags ( id pkey, public, created_at, updated_at, updated_by, draft fkey tags (id) unique ) tag_revs ( id, public, created_at, created_by, deleted_at, pkey (id, created_at) ) post2tag ( post_id fkey posts(id), tag_id fkey tags(id), public, created_at, updated_at, updated_by, pkey (post_id, tag_id) ) post2tag_revs ( post_id, tag_id, public, created_at, created_by, deleted_at, pkey (post_id, tag_id, created_at) ) 

Utilizza solo 3 tabelle: post, tag e post2tag.

Aggiungi le colonne start_time e end_time a tutte le tabelle. Aggiungi un indice univoco per chiave, start_time e end_time. Aggiungi indice univoco per la chiave dove end_time è nullo. Aggiungi i trigger.

Per corrente:

 SELECT ... WHERE end_time IS NULL 

Alla volta:

 WHERE (SELECT CASE WHEN end_time IS NULL THEN (start_time <= at_time) ELSE (start_time <= at_time AND end_time > at_time) END) 

La ricerca dei dati attuali non è lenta a causa dell’indice funzionale.

Modificare:

 CREATE UNIQUE INDEX ... ON post2tag (post_id, tag_id) WHERE end_time IS NULL; CREATE UNIQUE INDEX ... ON post2tag (post_id, tag_id, start_time, end_time); FOREIGN KEY (post_id, start_time, end_time) REFERENCES posts (post_id, start_time, end_time) ON DELETE CASCADE ON UPDATE CASCADE; FOREIGN KEY (tag_id, start_time, end_time) REFERENCES tags (tag_id, start_time, end_time) ON DELETE CASCADE ON UPDATE CASCADE;