Publicité
+ Répondre à la discussion
Affichage des résultats 1 à 11 sur 11
  1. #1
    Expert Confirmé
    Avatar de Kropernic
    Homme Profil pro
    Analyste / Programmeur / DBA
    Inscrit en
    juillet 2006
    Messages
    3 019
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 31
    Localisation : Belgique

    Informations professionnelles :
    Activité : Analyste / Programmeur / DBA
    Secteur : Distribution

    Informations forums :
    Inscription : juillet 2006
    Messages : 3 019
    Points : 2 869
    Points
    2 869

    Par défaut Sauvegarde des lignes supprimées

    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.
    Kropernic (anciennement Griftou).

  2. #2
    Modérateur
    Avatar de CinePhil
    Homme Profil pro Philippe Leménager
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    13 820
    Détails du profil
    Informations personnelles :
    Nom : Homme Philippe Leménager
    Âge : 51
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : août 2006
    Messages : 13 820
    Points : 24 813
    Points
    24 813

    Par défaut

    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--|
    Philippe Leménager. Ingénieur d'étude à l'École Nationale de Formation Agronomique. Autoentrepreneur.
    Mon blog sur la conception des BDD, le langage SQL, le PHP avec Zend Framework...
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  3. #3
    Expert Confirmé
    Avatar de Kropernic
    Homme Profil pro
    Analyste / Programmeur / DBA
    Inscrit en
    juillet 2006
    Messages
    3 019
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 31
    Localisation : Belgique

    Informations professionnelles :
    Activité : Analyste / Programmeur / DBA
    Secteur : Distribution

    Informations forums :
    Inscription : juillet 2006
    Messages : 3 019
    Points : 2 869
    Points
    2 869

    Par défaut

    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 ?
    Kropernic (anciennement Griftou).

  4. #4
    Modérateur
    Avatar de CinePhil
    Homme Profil pro Philippe Leménager
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    13 820
    Détails du profil
    Informations personnelles :
    Nom : Homme Philippe Leménager
    Âge : 51
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : août 2006
    Messages : 13 820
    Points : 24 813
    Points
    24 813

    Par défaut

    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.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale de Formation Agronomique. Autoentrepreneur.
    Mon blog sur la conception des BDD, le langage SQL, le PHP avec Zend Framework...
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  5. #5
    Expert Confirmé
    Avatar de Kropernic
    Homme Profil pro
    Analyste / Programmeur / DBA
    Inscrit en
    juillet 2006
    Messages
    3 019
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 31
    Localisation : Belgique

    Informations professionnelles :
    Activité : Analyste / Programmeur / DBA
    Secteur : Distribution

    Informations forums :
    Inscription : juillet 2006
    Messages : 3 019
    Points : 2 869
    Points
    2 869

    Par défaut

    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 ?
    Kropernic (anciennement Griftou).

  6. #6
    Modérateur
    Avatar de CinePhil
    Homme Profil pro Philippe Leménager
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    13 820
    Détails du profil
    Informations personnelles :
    Nom : Homme Philippe Leménager
    Âge : 51
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : août 2006
    Messages : 13 820
    Points : 24 813
    Points
    24 813

    Par défaut

    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.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale de Formation Agronomique. Autoentrepreneur.
    Mon blog sur la conception des BDD, le langage SQL, le PHP avec Zend Framework...
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  7. #7
    Expert Confirmé
    Avatar de Kropernic
    Homme Profil pro
    Analyste / Programmeur / DBA
    Inscrit en
    juillet 2006
    Messages
    3 019
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 31
    Localisation : Belgique

    Informations professionnelles :
    Activité : Analyste / Programmeur / DBA
    Secteur : Distribution

    Informations forums :
    Inscription : juillet 2006
    Messages : 3 019
    Points : 2 869
    Points
    2 869

    Par défaut

    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 )
    Kropernic (anciennement Griftou).

  8. #8
    Modérateur
    Avatar de CinePhil
    Homme Profil pro Philippe Leménager
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    13 820
    Détails du profil
    Informations personnelles :
    Nom : Homme Philippe Leménager
    Âge : 51
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : août 2006
    Messages : 13 820
    Points : 24 813
    Points
    24 813

    Par défaut

    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.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale de Formation Agronomique. Autoentrepreneur.
    Mon blog sur la conception des BDD, le langage SQL, le PHP avec Zend Framework...
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  9. #9
    Expert Confirmé
    Avatar de Kropernic
    Homme Profil pro
    Analyste / Programmeur / DBA
    Inscrit en
    juillet 2006
    Messages
    3 019
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 31
    Localisation : Belgique

    Informations professionnelles :
    Activité : Analyste / Programmeur / DBA
    Secteur : Distribution

    Informations forums :
    Inscription : juillet 2006
    Messages : 3 019
    Points : 2 869
    Points
    2 869

    Par défaut

    Citation Envoyé par CinePhil Voir le message
    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...
    Kropernic (anciennement Griftou).

  10. #10
    Modérateur
    Avatar de pachot
    Homme Profil pro Franck Pachot
    Consultant DBA en Suisse (dbi services) OCM 11g
    Inscrit en
    novembre 2007
    Messages
    1 369
    Détails du profil
    Informations personnelles :
    Nom : Homme Franck Pachot
    Âge : 43
    Localisation : Suisse

    Informations professionnelles :
    Activité : Consultant DBA en Suisse (dbi services) OCM 11g
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : novembre 2007
    Messages : 1 369
    Points : 4 405
    Points
    4 405

    Par défaut

    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.
    Franck Pachot - Consultant en Suisse Romande (dbi services) - Oracle Certified Master (OCP 12c et OCM 11g) - twitter: @FranckPachot


  11. #11
    Expert Confirmé
    Avatar de Kropernic
    Homme Profil pro
    Analyste / Programmeur / DBA
    Inscrit en
    juillet 2006
    Messages
    3 019
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 31
    Localisation : Belgique

    Informations professionnelles :
    Activité : Analyste / Programmeur / DBA
    Secteur : Distribution

    Informations forums :
    Inscription : juillet 2006
    Messages : 3 019
    Points : 2 869
    Points
    2 869

    Par défaut

    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
    Kropernic (anciennement Griftou).

Liens sociaux

Règles de messages

  • Vous ne pouvez pas créer de nouvelles discussions
  • Vous ne pouvez pas envoyer des réponses
  • Vous ne pouvez pas envoyer des pièces jointes
  • Vous ne pouvez pas modifier vos messages
  •