Conception de firebase database pour les révisions?

Dans le projet, nous avons besoin de stocker toutes les révisions (Historique des modifications) pour les entités de la firebase database. Actuellement, nous avons 2 propositions conçues pour cela:

par exemple pour l’entité “employé”

Design 1:

-- Holds Employee Entity "Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)" -- Holds the Employee Revisions in Xml. The RevisionXML will contain -- all data of that particular EmployeeId "EmployeeHistories (EmployeeId, DateModified, RevisionXML)" 

Design 2:

 -- Holds Employee Entity "Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)" -- In this approach we have basically duplicated all the fields on Employees -- in the EmployeeHistories and storing the revision data. "EmployeeHistories (EmployeeId, RevisionId, DateModified, FirstName, LastName, DepartmentId, .., ..)" 

Y a-t-il une autre façon de faire cette chose?

Le problème avec le “Design 1” est que nous devons parsingr XML chaque fois que vous avez besoin d’accéder aux données. Cela ralentira le processus et appenda également des limitations, car nous ne pouvons pas append de jointures dans les champs de données des révisions.

Et le problème avec le “Design 2” est que nous devons dupliquer chaque domaine de toutes les entités (nous avons environ 70-80 entités pour lesquelles nous voulons maintenir des révisions).

  1. Ne le mettez pas tous dans une table avec un atsortingbut de discriminateur IsCurrent. Cela ne pose que des problèmes ultérieurs, nécessite des clés de substitution et toutes sortes d’autres problèmes.
  2. Design 2 a des problèmes avec les modifications de schéma. Si vous modifiez la table Employees, vous devez modifier la table EmployeeHistories et tous les sprocs associés. Vous doublez potentiellement l’effort de changement de schéma.
  3. La conception 1 fonctionne bien et si elle est faite correctement, elle ne coûte pas cher en termes de performances. Vous pouvez utiliser un schéma xml et même des index pour résoudre les éventuels problèmes de performances. Votre commentaire sur l’parsing syntaxique du fichier XML est valide, mais vous pouvez facilement créer une vue à l’aide de xquery – que vous pouvez inclure dans les requêtes et y joindre. Quelque chose comme ça…
 CREATE VIEW EmployeeHistory AS , FirstName, , DepartmentId SELECT EmployeeId, RevisionXML.value('(/employee/FirstName)[1]', 'varchar(50)') AS FirstName, RevisionXML.value('(/employee/LastName)[1]', 'varchar(100)') AS LastName, RevisionXML.value('(/employee/DepartmentId)[1]', 'integer') AS DepartmentId, FROM EmployeeHistories 

Je pense que la question clé à poser ici est «Qui / Qu’est-ce qui va utiliser l’histoire»?

Si ce sera principalement pour le reporting / l’histoire lisible par l’homme, nous avons mis en place ce système dans le passé …

Créez une table appelée ‘AuditTrail’ ou quelque chose qui comporte les champs suivants …

 [ID] [int] IDENTITY(1,1) NOT NULL, [UserID] [int] NULL, [EventDate] [datetime] NOT NULL, [TableName] [varchar](50) NOT NULL, [RecordID] [varchar](20) NOT NULL, [FieldName] [varchar](50) NULL, [OldValue] [varchar](5000) NULL, [NewValue] [varchar](5000) NULL 

Vous pouvez ensuite append une colonne ‘LastUpdatedByUserID’ à toutes vos tables qui doivent être définies chaque fois que vous effectuez une mise à jour / insertion sur la table.

Vous pouvez ensuite append un déclencheur à chaque table pour détecter toute insertion / mise à jour qui se produit et créer une entrée dans cette table pour chaque champ modifié. Comme la table est également fournie avec le ‘LastUpdateByUserID’ pour chaque mise à jour / insertion, vous pouvez accéder à cette valeur dans le déclencheur et l’utiliser lors de l’ajout à la table d’audit.

Nous utilisons le champ RecordID pour stocker la valeur du champ clé de la table en cours de mise à jour. Si c’est une clé combinée, nous faisons simplement une concaténation de chaîne avec un «~» entre les champs.

Je suis sûr que ce système peut avoir des inconvénients – pour les bases de données fortement mises à jour, les performances peuvent être atteintes, mais pour mon application Web, nous obtenons beaucoup plus de lectures que d’écritures et les performances semblent plutôt bonnes. Nous avons même écrit un petit utilitaire VB.NET pour écrire automatiquement les déclencheurs en fonction des définitions de la table.

Juste une pensée!

L’article des tables d’historique du blog Database Programmer peut être utile – il couvre certains des points soulevés ici et traite du stockage des deltas.

modifier

Dans l’essai des tableaux d’histoire , l’auteur ( Kenneth Downs ) recommande de conserver un tableau d’historique d’au moins sept colonnes:

  1. Horodatage du changement,
  2. Utilisateur qui a apporté la modification,
  3. Un jeton pour identifier l’enregistrement qui a été modifié (où l’historique est conservé séparément de l’état actuel),
  4. Si la modification était une insertion, mise à jour ou suppression,
  5. L’ancienne valeur,
  6. La nouvelle valeur,
  7. Le delta (pour les changements de valeurs numériques).

Les colonnes qui ne changent jamais ou dont l’historique n’est pas requirejs ne doivent pas être suivies dans la table d’historique pour éviter les ballonnements. Le stockage du delta pour des valeurs numériques peut faciliter les requêtes ultérieures, même si elles peuvent être dérivées des anciennes et des nouvelles valeurs.

La table d’historique doit être sécurisée, les utilisateurs non système étant empêchés d’insérer, de mettre à jour ou de supprimer des lignes. Seules les purges périodiques doivent être sockets en charge pour réduire la taille globale (et si cela est autorisé par le cas d’utilisation).

Nous avons mis en place une solution très similaire à la solution proposée par Chris Roberts, et cela fonctionne plutôt bien pour nous.

La seule différence est que nous stockons uniquement la nouvelle valeur. L’ancienne valeur est après tout stockée dans la ligne d’historique précédente

 [ID] [int] IDENTITY(1,1) NOT NULL, [UserID] [int] NULL, [EventDate] [datetime] NOT NULL, [TableName] [varchar](50) NOT NULL, [RecordID] [varchar](20) NOT NULL, [FieldName] [varchar](50) NULL, [NewValue] [varchar](5000) NULL 

Disons que vous avez une table avec 20 colonnes. De cette manière, il vous suffit de stocker la colonne exacte qui a été modifiée au lieu de stocker la ligne entière.

Si vous devez stocker l’historique, créez un tableau fantôme avec le même schéma que la table que vous suivez et une colonne “Date de révision” et “Type de révision” (par exemple, “Supprimer”, “Mettre à jour”). Écrivez (ou générez – voir ci-dessous) un ensemble de déclencheurs pour remplir la table d’audit.

Il est assez simple de créer un outil qui lit le dictionnaire de données système d’une table et génère un script qui crée la table shadow et un ensemble de déclencheurs pour le remplir.

N’essayez pas d’utiliser XML pour cela, le stockage XML est beaucoup moins efficace que le stockage de table de firebase database natif utilisé par ce type de déclencheur.

Éviter la conception 1; ce n’est pas très pratique une fois que vous aurez par exemple besoin de revenir aux anciennes versions des enregistrements – automatiquement ou “manuellement” à l’aide de la console des administrateurs.

Je ne vois pas vraiment les inconvénients de Design 2. Je pense que la deuxième table History devrait contenir toutes les colonnes présentes dans le premier tableau Records. Par exemple, dans mysql, vous pouvez facilement créer une table avec la même structure qu’une autre table ( create table X like Y ). Et, lorsque vous êtes sur le sharepoint modifier la structure de la table Records dans votre firebase database en direct, vous devez quand même utiliser alter table commandes alter table – et ces commandes ne sont pas trop sollicitées pour votre table History.

Remarques

  • Le tableau des enregistrements ne contient que la dernière révision;
  • La table d’historique contient toutes les révisions précédentes des enregistrements dans la table des enregistrements;
  • La clé primaire de la table d’historique est une clé primaire de la table des enregistrements avec la colonne RevisionId ajoutée;
  • Pensez à d’autres champs auxiliaires tels que ModifiedBy – l’utilisateur qui a créé une révision particulière. Vous pouvez également souhaiter avoir un champ DeletedBy pour savoir qui a supprimé une révision particulière.
  • Pensez à ce que DateModified devrait signifier – soit cela signifie où cette révision particulière a été créée, soit cela signifie que cette révision particulière a été remplacée par une autre. Le premier nécessite que le champ soit dans le tableau Records et semble plus intuitif à première vue; la seconde solution semble toutefois être plus pratique pour les enregistrements supprimés (date à laquelle cette révision particulière a été supprimée). Si vous DateDeleted pour la première solution, vous aurez probablement besoin d’un deuxième champ DateDeleted (uniquement si vous en avez bien sûr besoin). Cela dépend de vous et de ce que vous voulez réellement enregistrer.

Les opérations dans Design 2 sont très sortingviales:

Modifier

  • copier l’enregistrement de la table Records dans la table History, lui donner un nouveau RevisionId (s’il n’est pas déjà présent dans la table Records), gérer DateModified (dépend de la façon dont vous l’interprétez, voir les notes ci-dessus)
  • continuer avec la mise à jour normale de l’enregistrement dans le tableau des enregistrements

Effacer

  • Faites exactement la même chose que dans la première étape de l’opération Modifier. Gérez DateModified / DateDeleted en fonction de l’interprétation que vous avez choisie.

Undelete (ou rollback)

  • prendre la révision la plus élevée (ou quelque chose en particulier?) de la table History et la copier dans la table Records

Historique des révisions de liste pour un enregistrement particulier

  • sélectionner à partir de la table d’historique et du tableau des enregistrements
  • Pensez exactement ce que vous attendez de cette opération. il va probablement déterminer quelles informations vous avez besoin des champs DateModified / DateDeleted (voir les notes ci-dessus)

Si vous optez pour Design 2, toutes les commandes SQL nécessaires à cette fin seront très simples, tout comme la maintenance! Peut-être, ce sera beaucoup plus facile si vous utilisez les colonnes auxiliaires ( RevisionId , DateModified ) également dans la table Records – pour garder les deux tables exactement à la même structure (sauf pour les clés uniques)! Cela permettra des commandes SQL simples, qui seront tolérantes à tout changement de structure de données:

 insert into EmployeeHistory select * from Employe where ID = XX 

N’oubliez pas d’utiliser les transactions!

En ce qui concerne la mise à l’échelle , cette solution est très efficace, car vous ne transformez aucune donnée à partir de XML, simplement en copiant des lignes entières – des requêtes très simples, en utilisant des index – très efficaces!

Ramesh, j’ai été impliqué dans le développement d’un système basé sur la première approche.
Il s’est avéré que le stockage de révisions au format XML entraînait une énorme croissance de la firebase database et ralentissait considérablement les choses.
Mon approche serait d’avoir une table par entité:

 Employee (Id, Name, ... , IsActive) 

IsActive est un signe de la dernière version

Si vous souhaitez associer des informations supplémentaires à des révisions, vous pouvez créer une table séparée contenant cette information et la lier à des tables d’entités en utilisant la relation PK \ FK.

De cette façon, vous pouvez stocker toutes les versions des employés dans une seule table. Avantages de cette approche:

  • Structure de firebase database simple
  • Aucun conflit puisque la table devient uniquement annexe
  • Vous pouvez revenir à la version précédente en modifiant simplement le drapeau IsActive
  • Pas besoin de jointures pour obtenir l’historique des objects

Notez que vous devez autoriser la clé primaire à ne pas être unique.

La façon dont j’ai vu cela fait dans le passé est d’avoir

 Employees (EmployeeId, DateModified, < Employee Fields > , boolean isCurrent ); 

Vous ne “mettez jamais à jour” sur cette table (sauf pour changer la valeur de isCurrent), insérez simplement de nouvelles lignes. Pour un EmployeeId donné, une seule ligne peut avoir isCurrent == 1.

La complexité de la maintenance peut être masquée par des vues et des déclencheurs “(dans oracle, je suppose des choses similaires aux autres SGBDR), vous pouvez même aller à des vues matérialisées si les tables sont trop grandes et ne peuvent pas être manipulées par des index) .

Cette méthode est correcte, mais vous pouvez vous retrouver avec des requêtes complexes.

Personnellement, je suis assez friand de votre façon de le concevoir, comme je l’ai déjà fait par le passé. C’est simple à comprendre, simple à mettre en œuvre et simple à entretenir.

Il crée également très peu de frais généraux pour la firebase database et l’application, en particulier lorsque vous effectuez des requêtes de lecture, ce que vous ferez probablement 99% du temps.

Il serait également très facile d’automatiser la création des tables d’historique et des déclencheurs à maintenir (en supposant que cela se fasse via des déclencheurs).

Si, en effet, une piste d’audit est tout ce dont vous avez besoin, je me pencherai sur la solution de la table d’audit (complète avec des copies dénormalisées de la colonne importante sur d’autres tables, par exemple, UserName ). Gardez cependant à l’esprit que cette expérience amère montre qu’une seule table d’audit constituera un énorme goulot d’étranglement; Cela vaut probablement la peine de créer des tables d’audit individuelles pour toutes vos tables auditées.

Si vous avez besoin de suivre les versions historiques (et / ou futures), la solution standard consiste à suivre la même entité avec plusieurs lignes en utilisant une combinaison de valeurs de début, de fin et de durée. Vous pouvez utiliser une vue pour faciliter l’access aux valeurs actuelles. Si c’est votre approche, vous pouvez rencontrer des problèmes si vos données versionnées font référence à des données mutables mais non versionnées.

La révision des données est un aspect du concept de « validité de temps » d’une firebase database temporelle. Beaucoup de recherches ont été effectuées sur ce sujet et de nombreux modèles et directives ont vu le jour. J’ai écrit une longue réponse avec un tas de références à cette question pour les personnes intéressées.

Si vous voulez faire le premier, vous pouvez aussi utiliser XML pour la table Employees. La plupart des bases de données plus récentes vous permettent d’interroger des champs XML, ce qui ne pose pas toujours problème. Et il pourrait être plus simple d’avoir un moyen d’accéder aux données des employés, qu’il s’agisse de la dernière version ou d’une version antérieure.

J’essaierais la deuxième approche cependant. Vous pouvez simplifier cela en ayant une seule table Employees avec un champ DateModified. EmployeeId + DateModified serait la clé primaire et vous pouvez stocker une nouvelle révision en ajoutant simplement une ligne. De cette manière, l’archivage des anciennes versions et la restauration des versions à partir des archives sont également simplifiés.

Une autre façon de procéder pourrait être le modèle de datavault de Dan Linstedt. J’ai réalisé un projet pour le bureau de statistiques néerlandais qui a utilisé ce modèle et cela fonctionne assez bien. Mais je ne pense pas que cela soit directement utile pour l’utilisation quotidienne des bases de données. Vous pourriez cependant avoir des idées en lisant ses articles.

Je vais partager avec vous mon design et c’est différent de vos deux designs en ce sens qu’il nécessite une table par type d’entité. J’ai trouvé que la meilleure façon de décrire une conception de firebase database est d’utiliser ERD, voici le mien:

entrer la description de l'image ici

Dans cet exemple, nous avons une entité nommée employé . user table contient les enregistrements de vos utilisateurs et entity et entity_revision sont deux tables qui contiennent l’historique des révisions pour tous les types d’entités que vous aurez dans votre système. Voici comment fonctionne cette conception:

Les deux champs de entity_id et revision_id

Chaque entité de votre système aura un identifiant d’entité unique. Votre entité peut passer par des révisions mais son entity_id restra le même. Vous devez conserver cet identifiant d’entité dans votre table des employés (en tant que clé étrangère). Vous devez également stocker le type de votre entité dans la table d’ entités (par exemple, «employé»). Comme pour le fichier revision_id, comme son nom l’indique, il suit les révisions de votre entité. Le meilleur moyen que j’ai trouvé pour cela est d’utiliser le employee_id en tant que révision_id. Cela signifie que vous aurez des identifiants de révision en double pour différents types d’entités, mais ce n’est pas un plaisir pour moi (je ne suis pas sûr de votre cas). La seule remarque importante à faire est que la combinaison de entity_id et revision_id doit être unique.

Il y a aussi un champ d’ état dans la table entity_revision qui indique l’état de la révision. Il peut avoir l’un des trois états: latest , obsolete ou deleted (ne pas se fier à la date des révisions pour vous aider à améliorer vos requêtes).

Une dernière remarque sur revision_id, je n’ai pas créé de clé étrangère connectant employee_id à revision_id car nous ne voulons pas modifier la table entity_revision pour chaque type d’entité que nous pourrions append à l’avenir.

INSERTION

Pour chaque employé que vous souhaitez insérer dans la firebase database, vous appendez également un enregistrement à l’ entité et à la vision de l’ entité . Ces deux derniers enregistrements vous aideront à savoir par qui et quand un enregistrement a été inséré dans la firebase database.

METTRE À JOUR

Chaque mise à jour pour un enregistrement d’employé existant sera implémentée sous la forme de deux insertions, une dans la table des employés et une dans la structure entity_revision. Le second vous aidera à savoir par qui et quand le dossier a été mis à jour.

EFFACEMENT

Pour supprimer un employé, un enregistrement est inséré dans entity_revision indiquant la suppression et effectué.

Comme vous pouvez le voir dans cette conception, aucune donnée n’est modifiée ou supprimée de la firebase database et, plus important encore, chaque type d’entité ne nécessite qu’une seule table. Personnellement, je trouve cette conception vraiment flexible et facile à travailler. Mais je ne suis pas sûr de vous car vos besoins pourraient être différents.

[METTRE À JOUR]

Ayant pris en charge les partitions dans les nouvelles versions de MySQL, je pense que mon design est également accompagné d’une des meilleures performances. On peut partitionner la table d’ entity utilisant le champ type tout en partitionnant entity_revision utilisant son champ d’ state . Cela augmentera considérablement les requêtes SELECT tout en conservant un design simple et net.

Que diriez-vous:

  • EmployeeID
  • Date modifiée
    • et / ou numéro de révision, en fonction de la manière dont vous souhaitez le suivre
  • ModifiéByUSerId
    • plus toute autre information que vous souhaitez suivre
  • Champs d’employés

Vous créez la clé primaire (EmployeeId, DateModified) et, pour obtenir le ou les enregistrements “actuels”, sélectionnez simplement MAX (DateModified) pour chaque employeeid. Stocker un IsCurrent est une très mauvaise idée, car tout d’abord, il peut être calculé, et deuxièmement, il est trop facile pour les données d’être désynchronisées.

Vous pouvez également créer une vue qui répertorie uniquement les derniers enregistrements, et surtout l’utiliser lorsque vous travaillez dans votre application. La bonne chose à propos de cette approche est que vous n’avez pas de doublons de données et que vous n’avez pas besoin de collecter des données à deux endroits différents (actuels dans Employees et archivés dans EmployeesHistory) pour obtenir l’historique ou la restauration, etc. .

Si vous souhaitez vous fier aux données de l’historique (pour des raisons de reporting), vous devez utiliser la structure comme suit:

 // Holds Employee Entity "Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)" // Holds the Employee revisions in rows. "EmployeeHistories (HistoryId, EmployeeId, DateModified, OldValue, NewValue, FieldName)" 

Ou solution globale pour application:

 // Holds Employee Entity "Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)" // Holds all entities revisions in rows. "EntityChanges (EntityName, EntityId, DateModified, OldValue, NewValue, FieldName)" 

Vous pouvez également enregistrer vos révisions en XML, vous n’avez alors qu’un enregistrement pour une révision. Cela ressemblera à:

 // Holds Employee Entity "Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)" // Holds all entities revisions in rows. "EntityChanges (EntityName, EntityId, DateModified, XMLChanges)" 

Nous avons eu des exigences similaires, et ce que nous avons constaté, c’est que souvent, l’utilisateur veut simplement voir ce qui a été modifié, sans nécessairement annuler les modifications.

Je ne suis pas sûr de votre cas d’utilisation, mais nous avons créé create et Audit table qui est automatiquement mis à jour avec les modifications apscopes à une entité commerciale, y compris le nom convivial de toute référence de clé étrangère et énumérations.

Chaque fois que l’utilisateur enregistre ses modifications, nous rechargeons l’ancien object, exécutons une comparaison, enregistrons les modifications et sauvegardons l’entité (toutes sont effectuées dans une seule transaction de firebase database en cas de problème).

Cela semble très bien fonctionner pour nos utilisateurs et nous évite d’avoir à faire face à une table d’audit complètement séparée avec les mêmes champs que notre entité commerciale.

Il semble que vous vouliez suivre les modifications apscopes à des entités spécifiques au fil du temps, par exemple ID 3, “bob”, “123 rue principale”, puis un autre ID 3, “bob” “234 elm st”, etc. pour lancer un historique de révision montrant chaque adresse “bob” a été à.

La meilleure façon de le faire est d’avoir un champ “is current” sur chaque enregistrement et (probablement) un horodatage ou un FK sur un tableau date / heure.

Les insertions doivent alors définir le “est actuel” et également le “” est “actuel sur l’enregistrement précédent” est actuel “. Les requêtes doivent spécifier “is current”, sauf si vous voulez tout l’historique.

Il y a d’autres modifications à apporter si c’est une très grande table, ou si un grand nombre de révisions sont attendues, mais c’est une approche assez standard.