[MPD] Sauvegarde des lignes supprimées

[Kropernic]

Hello,

Une petite question de conception pour savoir comment vous feriez histoire de voir si je suis dans le bon ou non.

Plantage du décor :
J'ai donc une DB avec un certain nombre de tables (53 pour être précis) et j'ai besoin de mettre en place un système de suppression logique histoire de ne pas perdre de données si jamais un utilisateur dans une application fait de la merde (malgré plusieurs messages de confirmation, ça arrive )


Une solution couramment utilisée jusqu'ici chez nous était d'avoir, dans chaque table, une colonne nommée "DELETED" de type bit qui est à 0 par défaut et passe à 1 pour marquée une ligne comme supprimée (via un trigger INSTEAD OF DELETE).
Cette solution me dérange pour 2 raisons :

1 - il va falloir modifier toutes les tables pour ajouter cette colonnes (mais bon, un ordre sql et c'est fait en un coup)
2 - il ne devrait pas y avoir des masses de lignes supprimées d'après le business process (pas sûr que ce soit le bon terme).

A cause de la raison n°2, j'ai l'impression que je vais consommer "plein" d'espace pour rien vu que le bit sera quasi toujours à 0.

Du coup, j'ai pensé à créer un schéma "DELETED" dans lequel j'aurais une copie de chaque table du schéma dbo. Et j'irais donc mettre les lignes supprimées dans la table adéquate. J'aurais donc ainsi toujours les données quelque part et les "bonnes" tables ne seraient pas polluées par des données obsolètes.

Qu'en pensez-vous ?

N.B. : Ce qui me fait vous demandez votre avis, c'est que, dans certaines tables, je ne pourrai plus avoir de clef primaire à moins d'y ajouter un nouvel ID spécifique. Pareil au niveau des relations. Si une ligne du table fille est supprimée mais pas la ligne qu'elle référence dans la table parente, si je mets des relations dans les tables du schéma DELETED, je vais avoir une erreur à l'insertion de la ligne supprimée dans la table d'archive.

[CinePhil]

Peut-être n'y a t-il pas besoin de duppliquer tout le schéma ?

Exemple...

personne -1,1----habiter----0,n- ville

Je peux être amené à supprimer des personnes (pas physiquement quand même ! ) mais pas les villes. La table des villes est plutôt ce que SQLPro appelle une "table de référence" (préfixée "TR" dans son standard de nommage) et la table des personnes une "table d'entité" (préfixée "TE").

Il conviendrait, dans cet exemple, de créer une table histo_personne pour y transférer les personnes supprimées mais qui référencerait toujours la table des villes du schéma principal.

personne -1,1----habiter-----------0,n- ville
histo_personne -1,1----habiter----0,n--|

[Kropernic]

Oui pour ce genre de cas, évidemment, pas besoin de dupliquer la table des villes

Mais si on prend le cas suivant, c'est plus ennuyeux :

Code :

1
2
CONTACT-0,N----Posséder----(1,1)-TELEPHONE

La table téléphone faisant l'objet d'une identification relative, je ne peux pas garder la même clef primaire que la table de base dans la table d'archive car :
- je crée 1 téléphone (id=1) pour le contact 10
- je crée 1 téléphone (id=2) pour le contact 10
- je crée 1 téléphone (id=3) pour le contact 10
- je supprime le téléphone 3 du client 10 (il va donc dans la table d'archive avec ctc_id = 10 et tel_id = 3)
- je crée 1 téléphone (id=3) pour le contact 10
- je supprime le téléphone 3 du client 10 --> violation de clef primaire.


Aussi, dans la table de base, j'ai la colonne ctc_id de la table TELEPHONE qui référence la clef primaire de la table CONTACT. Dans la table d'archive, je fais quoi ? Je référence la table CONTACT de base ou d'archive ?

[CinePhil]

Attention ! Ce n'est pas parce qu'on procède à une identification relative qu'il faut pour autant oublier le principe de l'auto-incrémentation ou de la séquence ! Une bonne clé primaire est une clé technique, sans signification, non modifiable et non réutilisable.

Si je supprime le téléphone {10, 3} et que j'en ajoute un nouveau, le nouveau ne doit pas prendre l'identifiant {10, 3}.

D'ailleurs, si je supprime le {10, 2} et qu'il y a un {10, 3}, le nouveau aura plutôt le numéro {10, 4}. Il conviendrait qu'il en soit ainsi aussi si je supprime le {10, 3} ; cet identifiant est perdu à jamais ! C'est vrai que mon trigger pour identification relative n'appliquera pas ce principe car il n'est pas basé sur une séquence. Ce n'est pas pour autant qu'il faut l'appliquer tel quel.

[Kropernic]

Même si, d'un point de vue théorique, je suis 100% d'accord, d'un point de vue pratique, je ne vois pas comment faire.

Puisque l'auto-incrément est impossible, il faut bien calculer l'identifiant relatif.

En simplifiant à l'insertion d'une ligne à la fois, on prend donc le MAX()de l'identifiant relatif et on ajoute 1.

--> Si on supprime une ligne autre que celle d'identifiant maximum, pas de souci, l'identifiant ne sera pas réutilisé.
--> Si on supprime la ligne d'identifiant maximum, comment est-ce qu'on peut savoir qu'il y avait une ligne puisqu'elle a disparue ?

[CinePhil]

C'est vrai que c'est plus compliqué mais il faudrait avoir une sorte de séquence à deux colonnes. Je ne sais pas si c'est faisable dans certains SGBD par un outil spécifique ou s'il faut créer une table pour ça.

[Kropernic]

Je crois que la solution (du moins avec SQL SERVER) est de création une table sur le côté qui tient les comptes mais vu mon niveau en SQL et en administration de DB, si je mets cela en place, je vais avoir des merdes au niveau des calculs des identifiants quand il aura des insertions (quasi) simultanées.

Du coup, je réutilise les identifiants relatifs dans le cas que j'ai décris pour les téléphones. Ce n'est peut-être pas "propre" à 100% mais au moins, c'est sûr au niveau de l'intégrité des données (ce qui est plus important je pense).

Et donc pour en revenir à ma question de base :

Quid de la clef primaire de la table d'archive pour les téléphones ?
- Ajout d'un identifiant auto-incrémenté propre à cette table ?
- Pas de clef primaire ? Après tout, c'est juste une table d'archive pour le cas où, donc pas trop grave
- Une autre solution ?

Quid de la relation vers la table des contacts ?
- Relation vers la table originale ? (problème lors de la suppression du contact)
- Relation vers la table d'archive ? (problème si le contact n'est pas supprimé)
- Pas de relation ? (pas de problème )

[CinePhil]

J'en reviens à ta solution actuelle de la colonne de type bit. Je ne sais pas quelle taille réelle est utilisée pour une colonne de type bit selon les différents SGBD et encore moins sur SQL Server que je n'utilise pas, ayant quitté la planète Microsoft depuis des années, mais si le volume de données n'est pas gigantesque, un bit, voire un octet de plus par ligne, ce n'est pas non plus la mère à boire et c'est quand même plus simple à gérer non ?

Si tu tiens à faire ton modèle d'archivage, je dirais :

- Ajout d'un identifiant auto-incrémenté propre à cette table
- Pas de relation

Ou plus exactement, pas de clé étrangère.
L'intégrité des données a été contrôlée par le SGBD lors de l'insertion et/ou de la mise à jour des données dans le schéma principal. On se contente, lors de la suppression, de transférer des lignes qui sont cohérentes entre elles vers des tables d'archive.

Si je cherche tous les numéros de téléphone qui ont été créés pour le contact 10, je peux aller les chercher dans la table du schéma principal et dans la table du schéma archive par une requête union. Ce n'est pas parce qu'il n'y a pas de clé étrangère dans le schéma archive que l'information ne figure pas dans la table : l'identifiant du contact existe bel et bien dans la table d'archive et les numéros de téléphone du contact 10 vont être retournés par la requête.

Le moteur MyISAM de MySQL ne connaît pas les clés étrangères mais ça n'empêche nullement de construire une BDD normalisée avec. C'est juste un risque d'incohérence des données à surveiller à cause de l'absence des clés étrangères mais les colonnes dans les tables sont les mêmes en MyISAM sans clé étrangère et en InnoDB avec clé étrangère. D'ailleurs on peut aussi faire une BDD MySQL en InnoDB et ne pas créer les clés étrangères. On prend un risque mais rien ne l'interdit techniquement, tout comme sur n'importe quel SGBD.

Un schéma d'archive n'a pas besoin des clés étrangères car il ne sert en principe qu'à l'archivage.

[Kropernic]

Envoyé par CinePhil

J'en reviens à ta solution actuelle de la colonne de type bit. Je ne sais pas quelle taille réelle est utilisée pour une colonne de type bit selon les différents SGBD et encore moins sur SQL Server que je n'utilise pas, ayant quitté la planète Microsoft depuis des années, mais si le volume de données n'est pas gigantesque, un bit, voire un octet de plus par ligne, ce n'est pas non plus la mère à boire et c'est quand même plus simple à gérer non ?

Bah... Ca fait quand même un bon paquet de procédure stockée (et peut-être de triggers) à modifier. Et même si ça ne prend pas des tonnes de place sur le disque (pour ça que j'avais mis des guillemets dans mon premier message), Ca laisse quand même des lignes dans la table alors qu'on pourrait l'alléger. Du coup, même si ce n'est peut-être que d'une quantité non mesurable pour l'utilisateur, les requêtes vont être ralenties non ?

Si tu tiens à faire ton modèle d'archivage, je dirais :

Ou plus exactement, pas de clé étrangère.
L'intégrité des données a été contrôlée par le SGBD lors de l'insertion et/ou de la mise à jour des données dans le schéma principal. On se contente, lors de la suppression, de transférer des lignes qui sont cohérentes entre elles vers des tables d'archive.

Si je cherche tous les numéros de téléphone qui ont été créés pour le contact 10, je peux aller les chercher dans la table du schéma principal et dans la table du schéma archive par une requête union. Ce n'est pas parce qu'il n'y a pas de clé étrangère dans le schéma archive que l'information ne figure pas dans la table : l'identifiant du contact existe bel et bien dans la table d'archive et les numéros de téléphone du contact 10 vont être retournés par la requête.

Le moteur MyISAM de MySQL ne connaît pas les clés étrangères mais ça n'empêche nullement de construire une BDD normalisée avec. C'est juste un risque d'incohérence des données à surveiller à cause de l'absence des clés étrangères mais les colonnes dans les tables sont les mêmes en MyISAM sans clé étrangère et en InnoDB avec clé étrangère. D'ailleurs on peut aussi faire une BDD MySQL en InnoDB et ne pas créer les clés étrangères. On prend un risque mais rien ne l'interdit techniquement, tout comme sur n'importe quel SGBD.

Un schéma d'archive n'a pas besoin des clés étrangères car il ne sert en principe qu'à l'archivage.

Je pensais pareil pour les relations. C'est géré en amont donc pas de souci. Niveau clef primaire, est-ce vraiment important ? Au fond, la clef primaire ne sert "qu'à ne pas avoir de doublon". Et cela, comme pour le cas des relations, est géré en amont dans le schéma principal.

Conséquence : pas d'index cluster.

Mais les requêtes sur ces tables devraient être anecdotiques (juste pour récupérer une ligne si l'utilisateur l'a supprimée par erreur), cela ne devrait pas poser de problème non plus je pense...

[pachot]

Bonjour,

Il faudrait d'abord voir si le SGBD sur lequel c'est implémenté n'a pas une fonctionnalité de versioning (comme Total Recall d'Oracle). Bien sûr, ce n'est pas gratuit...
Mais pour moins cher tous les SGBD permettent aussi de faire des backups. Il suffit de restaurer un ancien backup ailleurs et d'aller y chercher l'enregistrement voulu.
Et l'application peut aussi logguer ce quelle fait.

Bref, est-ce vraiment un problème de modélisation ?

A cause de la raison n°2, j'ai l'impression que je vais consommer "plein" d'espace pour rien vu que le bit sera quasi toujours à 0.

En fonction du SGBD, au lieu d'un 0, un NULL bien plaçé (à la fin) peut consommer 0 octets.

Cordialement,
Franck.

[Kropernic]

Bah finalement, j'ai fait un schéma nommé "DELETED". Un petit script pour y "cloner" les tables adéquates en y ajoutant les colonnes "DELETED_ON" et "DELETE_BY" et un autre petit script pour créer les triggers after delete sur les tables concernées du schéma dbo.

Et voilà.

Ce n'est probablement pas la manière de faire la plus optimale mais je n'avais franchement pas envie de modifier toutes les procédures stockées écrites jusqu'ici (et il y en a un paquet ^^).

N.B. : SGDBR = SQL SERVER 2008 R2