Base de données - Versioning des données

Question

J'ai lu quelques questions sur SI (comme cela on) en ce qui concerne la gestion des versions de vos données dans une base de données.

J'ai aimé certaines des suggestions qui ont été mentionnés. J'ai pendant longtemps voulu (nécessaire) à la révision de plusieurs de mes tables mais n'a jamais eu autour d'elle. D'être un programmeur avec seulement d'un simple travail de base de données sous ma ceinture, je me demandais comment serait en fait aller à ce sujet.

Je ne demande pas de la solution réelle dans la syntaxe SQL. Je peux éventuellement comprendre cela pour moi-même (ou post ALORS, quand vient le temps). Je suis juste de demander aux gens de commenter comme la façon dont ils vont le faire et d'éventuels problèmes de performances, il peut être si j'étais à la "révision" des centaines de millions d'enregistrements. Ou toutes autres suggestions en tant qu'il est basé sur l'exemple ci-dessous.

Étant donné un exemple simple:

Person
------------------------------------------------
ID                UINT NOT NULL,
PersonID          UINT NOT NULL,
Name              VARCHAR(200) NOT NULL,
DOB               DATE NOT NULL,
Email             VARCHAR(100) NOT NULL

Audit
------------------------------------------------
ID                UINT NOT NULL,
UserID            UINT NOT NULL,               -- Who
TableName         VARCHAR(50) NOT NULL,        -- What
OldRecID          UINT NOT NULL,               -- Where
NewRecID          UINT NOT NULL,
AffectedOn        DATE NOT NULL,               -- When
Comment           VARCHAR(500) NOT NULL        -- Why

Je ne sais pas comment on pourrait relier la table d'Audit pour toutes les autres tables (comme Personne) si le nom de la table est une chaîne de caractères?

Aussi, en supposant que j'ai trois GUI est à renseigner:

1. Un dossier complet pour une personne spécifique id
2. Une vue de la table d'inscription de toutes les personnes (id)
3. Une vue montrant chaque personne avec leur révision info en dessous de chaque entrée (nombre de révisions par personne, les dates de révisions, de la révision des commentaires, etc), commandé par les révisions les plus récentes.

Pour accomplir 1 et 2, il serait mieux à la requête de la Personne de la table ou la table d'Audit?

Pour accomplir 3, serait un soi-disant expert base de données simplement obtenir tous les dossiers et de les transmettre à un logiciel de traitement, ou d'un groupe par PersonID et dates Concernées? Est-ce généralement traités dans une même requête, ou plusieurs?

Answer 1

J'ai fait différents systèmes d'audit au fil des ans et je suis actuellement à mettre en place quelque chose comme ceci:

Person
------------------------------------------------
ID                UINT NOT NULL,
PersonID          UINT NOT NULL,
Name              VARCHAR(200) NOT NULL,
DOB               DATE NOT NULL,
Email             VARCHAR(100) NOT NULL


Person_History
------------------------------------------------
ID                UINT NOT NULL,
PersonID          UINT NOT NULL,
Name              VARCHAR(200) NOT NULL,
DOB               DATE NOT NULL,
Email             VARCHAR(100) NOT NULL
AuditID           UINT NOT NULL


Audit
------------------------------------------------
ID                UINT NOT NULL,
UserID            UINT NOT NULL,               -- Who
AffectedOn        DATE NOT NULL,               -- When
Comment           VARCHAR(500) NOT NULL        -- Why

Les dossiers en cours sont toujours dans la table Personne. Si il y a un changement d'un enregistrement d'audit est créé et l'ancien record est copié dans le Person_History table (notez l'ID ne change pas et il ne peut y avoir de multiples versions)

La Vérification de l'ID est dans la *_History tables de sorte que vous pouvez lier plusieurs enregistrement des modifications apportées à un enregistrement d'audit si vous le souhaitez.

EDIT:
Si vous n'avez pas de table d'historique pour chaque table de la base et que vous voulez utiliser la même table pour contenir vieux et "supprimé" les dossiers, alors vous devez marquer les enregistrements avec un indicateur d'état. Le problème avec ça c'est une vraie douleur lors de l'interrogation des archives courantes - moi confiance, je l'ai fait.

Answer 2

Que diriez-vous de créer la table comme d'habitude, ont un ModifiedDate colonne sur chaque enregistrement (et ModifiedBy si vous le souhaitez), et de faire toutes vos données d'accès par le biais d'une vue matérialisée qui regroupe les données par Id et ensuite n'AVOIR ModifiedDate = MAX(ModifiedDate)?

De cette façon, l'ajout d'un nouvel enregistrement avec le même Id que l'autre va supprimer l'ancien enregistrement de la vue. Si vous souhaitez interroger l'histoire, ne passent pas par la vue

J'ai toujours trouvé que le maintien de tables différentes avec les mêmes colonnes complexes et sujettes à erreur.

Answer 3

Suivant DJ post dans aide d'un historique d'une table par table de base et un commentaire par Karl sur d'éventuels problèmes de performances, j'ai fait un peu de SQL à la recherche afin de déterminer la méthode la plus rapide pour transférer un enregistrement d'une table à l'autre.

Je viens de relater ce que j'ai trouvé:

J'ai pensé que j'aurais à faire un SQL fetch pour charger l'enregistrement de la table de base, suivie d'un SQL pousser pour mettre l'enregistrement dans le tableau de l'historique, suivie par une mise à jour de la table de base pour insérer les données modifiées. Total de 3 transactions.

Mais à ma grande surprise, j'ai réalisé que vous pouvez faire les deux premières transactions à l'aide d'une instruction SQL à l'aide de la SÉLECTIONNER DANS la syntaxe. Je suis le pari de la performance serait d'une centaine de fois plus rapide de faire cela.

Alors que nous laisserait tout simplement de mettre à JOUR le dossier avec les nouvelles données dans la table de base.

Je n'ai toujours pas trouvé une instruction SQL à faire tous les 3 opérations en une seule fois (je doute).

Answer 4

J'aime votre table d'audit, c'est un bon début. Vous avez une cardinalité d'un problème avec votre table d'audit, et je voudrais donc buste comme les deux tableaux suivants:

Person
------------------------------------------------
ID                UINT NOT NULL,
PersonID          UINT NOT NULL,
Name              VARCHAR(200) NOT NULL,
DOB               DATE NOT NULL,
Email             VARCHAR(100) NOT NULL,
AuditID           UINT NOT NULL 

Audit
------------------------------------------------
ID                UINT NOT NULL,
TableName         VARCHAR(50) NOT NULL,        -- What
TableKey          UINT NOT NULL,
CreateDate        DATETIME NOT NULL  DEFAULT(NOW),
CreateUserID      UINT NOT NULL,
ChangeDate        DATETIME NOT NULL  DEFAULT(NOW),
ChangeUserID      UINT NOT NULL

Audit_Item
------------------------------------------------
ID                UINT NOT NULL,
AuditID           UINT NOT NULL,               -- Which audit record
UserID            UINT NOT NULL,               -- Who
OldRecID          UINT NOT NULL,               -- Where
NewRecID          UINT NOT NULL,
AffectedOn        DATE NOT NULL,               -- When
Comment           VARCHAR(500) NOT NULL        -- Why

La mise en page initiale proposée a un seul enregistrement d'Audit qui renvoie à (je suppose) deux enregistrements de personnes. Les défis de cette conception sont les suivants:

• Les enregistrements dans votre table de personne les "réel", les dossiers?
• Comment représenter l'ensemble de l'histoire des modifications apportées à l'enregistrement de Personne? Si vous pointent à deux enregistrements dans la Personne de la table, puis voir le point #1: celui qui est de l'enregistrement en cours?
• Créer*,* les champs sont roulés à partir d'une collection de Audit_Item les enregistrements. Ils ne sont là que pour la facilité d'accès.
• Le AuditID clé dans la table Personne permet de pointer retour à la table d'Audit et d'apprendre à l'histoire de la Personne, sans avoir besoin d'interroger la Vérification table avec la clause WHERE TableName='Person'