Modello dati: come modellare meglio questo riferimento circolare

Ecco i miei due centesimi.

• Se vuoi conoscere la revisione corrente, devi interrogare la tabella REVISION per max(revision_id) per quel document_id . Lo stesso per PUBLICATION . Puoi avere una vista V_DOCUMENT con quella già risolta tramite un join, quindi ai programmatori sarebbe come se ci fosse una V_DOCUMENT "tabella" con CURR_REV_ID e CURR_PUB_REV_ID .
• Se vuoi veramente de-normalizzare quell'informazione, cioè, averla precalcolata in DOCUMENT , dovresti aggiungere un trigger su REVISION e PUBLICATION per mantenere quelle colonne aggiornate in DOCUMENT , ma quelli non sarebbero FK che puntano a REVISION e PUBLICATION , evitando così riferimenti circolari.
• È una buona pratica separare oggetti binari di grandi dimensioni come immagini o contenuti di testo molto lunghi nella propria tabella (anche quando la relazione concettuale dovrebbe essere uno-a-uno) per motivi di prestazioni e per l'amministrazione dello storage motivi. Il tavolo principale verrà interrogato e letto molto, quindi portare fuori il BLOB è buono. Un DBA può anche assicurarsi che il BLOB sia memorizzato in un tablespace separato, il che significa che la scrittura di oggetti così grandi non influisce sulla velocità dell'accesso transazionale delle altre tabelle. Anche questo tablespace può essere inserito in un archivio più lento ed economico.

concettuale:

fisico:

Primo: denominazione. La tabella denominata "Documenti" sopra memorizza la conoscenza delle revisioni, non i documenti o i contenuti. Forse potrebbe essere chiamato "DocumentRevisions". Secondo: separa il pk dell'indice della tabella dei corpi del documento dall'ID documento e memorizza le variazioni sul corpo del documento nella tabella con lo stesso ID documento , ma indice di tabella diverso. Alla fine ottieni:

Documents
---------
document_id: integer PK
title: text
other_info_common_to_all_revs_of_the_doc: text

DocumentContents
---------
document_contents_id: integer PK
document_id: integer FK references Documents(document_id)
revision: integer
document_body: text

DocumentRevisions
-----------------
document_revisions_id: integer PK
document_id: integer
current_revision: integer 
published_revision: integer
-- table has composite FK (document_id,current_revision) references DocumentContents(document_id,revision)
-- table has composite FK (document_id,published_revision) references DocumentContents(document_id,revision)

Ciò garantisce che tutti i corpi dei documenti siano archiviati con RI al loro ID doc e revisione.

Gli FK compositi assicurano RI su doc / rev.

Ciò che stai facendo sembra ragionevole, l'unico trucco è che dovrai inserire il documento con revisioni nulle, quindi aggiungere le revisioni, quindi aggiornare il documento. Quando aggiungi le revisioni, aggiungi la revisione e aggiorna il documento.

Un approccio alternativo è quello di avere flag "correnti" e "pubblicati" sulle revisioni. Se si dispone del supporto parziale dell'indice univoco, è possibile consentire al database di garantire che solo una revisione sia contrassegnata per documento:

Documents
---------
id: integer PK


DocumentRevisions
-----------------
id: integer PK
documentId: integer FK references Documents(id)
current: boolean not null
published: boolean not null
documentBody: text

-- Example partial unique indexes
create unique index DocumentRevisions_current_unique on DocumentRevisions (documentId) where current;
create unique index DocumentRevisions_published_unique on DocumentRevisions (documentId) where published;

-- You then select for current revision
select * from DocumentRevisions where documentId = ? and current;

Suggerisco di utilizzare lo schema Comando e un archivio eventi.

Invece di completare le revisioni di un documento, spezza ogni modifica in un comando.

Ie InsertTextAt (...)

Memorizza i dati necessari per eseguire il comando nel db.

Mentre l'utente modifica il documento, compili un elenco di questi comandi.

Quando si ricrea il documento, si caricano tutti i comandi dal db e si eseguono nuovamente in sequenza.

Questo ti permette di annullare la funzionalità di stile ecc.