Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

  1. #1
    Membre habitué
    Clefs primaires ISO VS clefs primaires techniques autoincrémentées.
    Bonjour à tous,

    Je me demande à quels moments je dois préférer les clefs ISO aux clefs autoincrémentées.
    Je pense notamment aux tables de référence,
    pour lesquelles une clef primaire ISO est systématiquement préférée à une clef primaire technique autoincrémentée ?
    table de pays exemple



    Nom en français CodeISO3166
    Albanie AL
    Algérie DZ
    Allemagne DE





    Focaliser le commentaire sur vos choix de clefs, pour chaque table de votre projet;

    • 2018/09/DOS001 , destiné au gestionnaire, pour ouvrir un dossier en cours. Quelle est la recette pour fabriquer ce genre de clef ?
    • DZ: référence de pays code ISO 3166, pour identifier un pays
    • 1 : clef primaire technique autoincrémentée à masquer au gestionnaire (secondant une clef candidate porteuse d'un index unique à révéler au gestionnaire)



    Etude issue de cas réels de MPD :
    • Comment raffermir son choix d'ériger en clef primaire : clef naturelle, clef ISO ou clef technique ?
    • Dans quels cas la clef naturelle, même composée, est elle exceptionnellement élevée au rang de clef primaire ?
    • Peut on confier exceptionnellement le droit de création d'une clef naturelle dans une table de référence, par le gestionnaire lui-même, ou doit-on systématiquement confier ce rôle à un trigger ?
    • Dans le cas de création d'une clef naturelle, par le gestionnaire lui-même dans une table de référence,la table doit-elle systématiquement contenir, par sécurité, une clef primaire technique autoincrémentée ?
    • Dans un tableau, peut on lister les clefs d'un gros projet qui tourne comme une horloge ? (nom_de_la_table,usage_de_la_table,une_valeur_de_la_clef_primaire,une_valeur_de_la_clef_alternative,observation)


    A la lecture de la règle d'or, d'Yves TABOURIER, doit on proclamer sans hésiter,
    "Les clefs primaires de nos tables ne sont que : code iso, clef technique autoincrémentée et clef technique hachée"

    Merci beaucoup pour votre aide éventuelle, et vos retours d'expériences !
    Merci à vous, épris de sagesse , qui n'oubliez pas le passé, et ne condamnez pas autrui à le revivre...
    Nous offrirez-vous synthétiquement, en 2 pages, un guide de survie à partir d'un gros projet réalisé ?

    A chaque fois que je choisis mes clefs de table, je suis victime de la pesanteur, pendant un décollage sur la planète mars.

    Newbee qui se morfond avec son trousseau de clefs tout neuf !!

  2. #2
    Expert éminent sénior
    Les clefs primaires servent non seulement à identifier de façon unique une occurrence de la table mais aussi à propager cet identifiant dans les tables liées via des contraintes de type "reference".
    Dans certains cas, notamment en cas d'identification relative mais pas seulement, l'identifiant primaire d'une table se retrouve ainsi dans de nombreuses tables, et, via les cardinalités, dans de très nombreuses lignes (plusieurs millions parfois)
    C'est la raison pour laquelle, le choix d'une valeur stable est préconisé, sinon toute modification de la valeur risque de faire s'effondrer la BDD à cause de la propagation dans les entités filles.

    Seul un identifiant technique asémantique permet de garantir cette stabilité.

    Comme en plus ce type d'identifiant technique peut être attribué automatiquement par le SGBD, on n'a même pas besoin de s'en préoccuper
    Comme enfin ce type d'identifiant est particulièrement concis (type integer) il permet un très grand nombre de valeurs pour un encombrement réduit, ce qui est propice aux performances.

    Un référentiel externe, même ISO, est sujet à des changements potentiels. Il faut donc bien vérifier les éventuelles contraintes d'intégrité avant de choisir la PK.

  3. #3
    Membre habitué
    cas des tables de références familles et sous-familles
    Bonjour escartefigue !
    Merci pour la mise à plat du grand principe !


    Voici une situation particuliere que j'aimerai évoquer :
    J'ai 3 tables
    • tm_article,
    • tr_couleur,
    • tr_FamilleCouleur



    Après avoir fait deux cercles chromatiques, je me rend vite compte que toutes les couleurs appartiennent à des familles.
    En considérant un cercle chromatique resserré de 6 couleurs :
    les trois couleurs primaires, le jaune, le rouge, le bleu
    + le vert, le violet et l'orange ( les secondaires) cela donne 6 familles de couleurs.

    Mais j'aime autant essayer de classer les couleurs en 3 familles seulement :
    - la famille des jaunes
    - la famille des rouges
    - la familles des bleus

    Bien sûr certaines couleurs peuvent être " limites" mais la répartition ainsi est assez pratique


    Exemple :
    les ocre jaune, terre de sienne naturelle, terres d'ombre ... sont dans la famille jaune ;
    les bleu de prusse, bleu indigo, gris de payne... sont dans la famille bleu
    les terre de sienne brûlée, rouge indien, brun rouge, marron de pérylène... sont dans la famille rouge

    EXTRAIT DE LA TABLE tr_couleur

    pk_couleur nom_couleur fk_FamilleCouleur
    jau001 ocre jaune jau
    jau002 terre de sienne naturelle jau
    jau003 terre d'ombre jau


    EXTRAIT DE LA TABLE tr_FamilleCouleur

    pk_famillecouleur nom_famille
    jau jaune
    rou rouge
    ble bleu




    Je peux utiliser tr_nuancecouleur, qui contient 12 nuances (Ce qui justifie davantage la création d'une table)
    Je ne sais pas si il est nécessaire de créer une table de référence, lorsque celle-ci contient uniquement 3 valeurs distinctes tr_famillecouleur(nom_famillecouleur{jaune,rouge,bleu})
    Je sais que je n'aurais aucune autre nuance dans ma table tr_nuancecouleur (règle de gestion)
    1 - BLEU CHAUD : évoquant les ciels d'été, tirant sur le violet (outremer français)
    2 - BLEU VERITABLE : bleu sans nuance violette, ni verte (bleu de cobalt)
    3 - BLEU FROID : bleu intense, tirant sur le vert (bleu Winsor, nuance verte)
    4 - BLEU-VERT : vert froid (vert émeraude)
    5 - JAUNE-VERT : vert chaud (vert de vessie permanent)
    6 - JAUNE FROID : jaune citron, tirant sur le vert (jaune citron Winsor)
    7 - JAUNE CHAUD : jaune beurre, tirant sur l'orange (jaune de cadmium pâle)
    8 - JAUNE IMPUR : jaune sombre ou moutarde (terre de sienne naturelle)
    9 - ROUGE CHAUD : rouge clair,vif, tirant sur l'orange (laque écarlate)
    10 - ROUGE FROID : lie de vin ou cramoisi, tirant sur le violet (alizarine cramoisie)
    11 - ROUGE IMPUR : orange sombre ou brun rouge (terre de sienne brûlée)
    12 - MAGENTA : couleur vive de fleur, plus rose que pourpre (magenta)


    Comment construire les cles de mes deux tables de référence tr_couleur et tr_famillecouleur, ou tr_couleur et tr_nuancecouleur
    sachant que je veux saisir l'article et lui affecter sa couleur précise,
    et que je veux rechercher l'article par sa famille de couleur, ou sa nuance de de couleur ?


    Quant aux clefs ISO, ne sont elles pas destinées à aider les systèmes à communiquer entre eux ?
    Ai-je intérêt à utiliser une clef "à ma façon", pour avoir astucieusement accès à ma famille de couleur,
    et simplifier ma requête de recherche des familles de couleurs, avec une seule jointure entre tm_article, et tr_couleur ?

    Ai-je raison, dans les tables de référence, de préférer intuitivement une clef "parlante" à une clef auto-incrémentée ?
    Dois-je arrêter immédiatement avec mon mauvais réflexe de "faire parler des clefs", à partir d'éléments de l'information stockée dans ma table de référence ?
    La clef auto-incrémentée, vide de sens, est-elle largement préférable ?

    Prenons 4 tables de références imbriquées, sur 3 niveaux d'arborescence.
    Existe-t-il une recette qui fonctionne particulièrement bien ?

  4. #4
    Modérateur

    Comment construire les cles de mes deux tables de référence tr_couleur et tr_famillecouleur, ou tr_couleur et tr_nuancecouleur
    Table de référence => clé primaire auto-incrémentée + colonne accueillant le code sémantique.
    tr_famille_couleur (fcl_id, fcl_code, fcl_libelle...)
    tr_couleur (col_id, col_id famille, col_code, col_libelle...)

    sachant que je veux saisir l'article et lui affecter sa couleur précise,
    te_article (art_id, art_id_couleur, art_reference, art_nom...)

    et que je veux rechercher l'article par sa famille de couleur
    Code SQL :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    SELECT a.art_id, a.art_code, a.art_libelle
    FROM te_article a
    INNER JOIN tr_couleur c ON c.col_id = a.art_id_couleur
        INNER JOIN tr_famille_coueur f ON f.fcl_id = c.col_id_famille
    WHERE f.fcl_code = 'jaune'


    Code SQL :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    SELECT a.art_id, a.art_code, a.art_libelle
    FROM te_article a
    INNER JOIN tr_couleur c ON c.col_id = a.art_id_couleur
    WHERE c.col_code = 'jau001'


    Voilà le principe ; je vous laisse réfléchir à la modélisation des 12 nuances de couleur.

    Comme toujours :
    1) écrivez vos règles de gestion ;
    2) dessinez votre MCD ;
    3) générez le MLD ;
    4) générez le code SQL et corrigez-le si besoin.

    Dois-je arrêter immédiatement avec mon mauvais réflexe de "faire parler des clefs"
    OUI !

    Un exemple...
    Aujourd'hui, vous décidez de coder vos familles d'articles sur 3 caractères alpha-numériques.
    Demain, vous fusionnez avec une autre entreprise qui a les mêmes familles d'articles mais codées sur 5 caractères et il est décidé que ce sont les codes de la nouvelle entreprise qui seront conservés mais ce sera votre base de données qui sera conservée.

    Si les codes sont aussi clé primaire, le changement va devoir se reporter sur la table des articles et potentiellement sur d'autres tables qui référencent ces codes.
    Si le code est seulement clé alternative (colonne code munie d'une contrainte d'unicité), vous ne modifiez que la table des codes. Les clés étrangères référençant la table des codes ne changent pas.

    Pour revenir à votre exemple de couleurs codées aujourd'hui "jau001", "jau002"...
    Demain, vous décidez finalement d'adopter le code des couleurs Pantone. Idem, si vos codes constituent la clé primaire, la modification impactera toutes les tables qui référencent la couleur. Si c'est seulement une clé alternative, vous ne modifierez que la table des couleurs.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « 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
    Membre habitué
    Bonjour CinePhil,
    De retour, et très heureux de trouver ici,
    votre réponse vraiment explicite.

    Je reste malgré tout perplexe, à l'idée
    de ne pas donner aux codes ISO, le rôle de clef primaire,
    mais de leur donner le rôle de clef alternative.

    Pourquoi les développeurs ne devraient ils pas élever les
    Code ISO au rang de clefs primaires universelles ?

    Pourquoi les développeurs devraient-ils reléguer sans débat, les codes ISO,
    au rang de clef naturelle, de clef alternative ?

    En fin de compte, à qui les Code ISO sont ils censés rendre service ?

    Par exemple, l'INSEE aurait-il dû se passer de code INSEE pour les 36000 communes de France ?
    code INSEE alpes-maritimes
    Un simple numéro autoincrémenté au pas de 1 ne suffisait il pas, également à l'INSEE, pour identifier chacune de ses villes ?

    A contrario, le développeur doit-il IMPERATIVEMENT doubler les clefs primaires autoincrémentées de ses tables de références,
    avec des clefs métiers universelles au format ISO ?

    Synthétiquement,
    • Quels rôles doit on faire jouer à une clef ISO ?
    • Que doit-on attendre de la clef ISO, qu'on n'attend pas de la clef primaire ?
    • Quels rôles doit on éviter de faire jouer à une clef ISO ?
    • Que ne doit-on pas attendre de la clef ISO, qu'on attend de la clef primaire?

  6. #6
    Modérateur

    Ce message n'a pas pu être affiché car il comporte des erreurs.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « 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
    Membre habitué
    Merci beaucoup CinePhil,
    je crois maintenant que tout est bien clair !

  8. #8
    Expert éminent sénior
    Martin,

    N'oubliez pas de voter pour CinePhil qui se décarcasse !
    Faites simple, mais pas plus simple ! (A. Einstein)
    E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    Je ne réponds pas aux questions techniques par MP. Les forums sont là pout ça.
    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench

  9. #9
    Membre habitué
    Bonjour Fsmrel, Cinephil, qui éclairez complètement la route !
    M E R C I !

    Dernière question très importante pour mettre les points sur les I
    et les barres sur les T :

    Parlons du bureau d'études, avec le MCD :
    Voici ma petite conclusion, en l'état de mes piètres connaissances, gonflées de vos très précieux conseils :
    1. Sur le MCD, créer uniquement les clés alternatives (METIER).
    2. Sur le MCD, ne JAMAIS CREER les clés primaires techniques autoincrémentées.
    3. Sur les MCD, toujours tracer les liaisons entre les clefs alternatives monovaluées (sur 1 champ) ou multivaluées (sur plusieurs champs)

    '---------------------------------------------------------------------------
    Parlons du bureau du développeur, avec le MPD :
    1. Sur le MPD, ajouter SYSTEMATIQUEMENT à chaque table, une clef autoincrémentée.
    2. Sur le MPD, supprimer toutes les liaisons entre les clefs alternatives (METIER)
    3. Sur le MPD, creer des liaisons, entre toutes les clefs primaires autoincrémentées de chaque table.
    4. Sur le MPD, programmer par triggers, les contraintes qui étaient dessinées avec des liens
      sur le MCD entre les clefs alternatives.



    Une fois en service, l'usage veut que le programmeur construise la majorité de ses requêtes,
    en posant des jointures, de sa propre initiative, entre les clefs alternatives.

    EST CE BIEN CORRECT MAINTENANT ???

  10. #10
    Modérateur

    Sur le MCD, créer uniquement les cles alternatives (METIER).
    Sur le MCD, ne JAMAIS CREER les clés primaires techniques autoincrémentées.
    En théorie, pourquoi pas ! En pratique, les logiciels de modélisation permettant de décliner le MCD en MLD puis en programme SQL de création de la BDD, il vaut mieux prévoir d'entrée les clés primaires auto-incrémentées.

    D'ailleurs...
    Sur les MCD, toujours tracer les liaisons entre les clefs alternatives monovaluées (sur 1 champ) ou multivaluées (sur plusieurs champs)
    Non ! Les "liaisons", ou plutôt les associations-types, se tracent entre les entités-types. Dans la déclinaison en MLD, cela entraînera la création automatique par le logiciel de modélisation de clés étrangères référençant les clés primaires des entités-types participant à l'association-type.
    => Il faut donc prévoir de suite la clé primaire de l'entité-type... qui sera une clé auto-incrémentée.

    Et au passage, rappel : Les champs sont à la campagne ou dans les formulaires, pas dans les tables SQL qui ne sont composées que de colonnes et de lignes.

    Sur le MPD, ajouter SYSTEMATIQUEMENT à chaque table, une clef autoincrémentée.
    Non ! Pour rappel, les tables associatives n'ont pas de clé auto-incrémentée mais une clé primaire composée des clés étrangères référençant les clés primaires des tables impliquées dans l'association-type.

    Sur le MPD, supprimer toutes les liaisons entre les clefs alternatives (METIER)
    Un peu stupide de supprimer ce qui a été créé non ?
    => Autant ne pas créer ce qui est inutile !

    Sur le MPD, creer des liaisons, entre toutes les clefs primaires autoincrémentées de chaque table.
    Non ! Les "liaisons" ne se font qu'entre les clés primaires impliquées dans les associations types. Et d'ailleurs, là encore, sur un MLD classique ou même sur un Entity/Relationship Diagram de MySQL Workbench, qui est assez proche du MLD, les "liaisons" sont tracées non pas entre les colonnes des tables mais entre les tables elles-mêmes.

    Sur le MPD, programmer par triggers, les contraintes qui étaient dessinées avec des liens
    sur le MCD entre les clefs alternatives.
    Non ! Puisque les clés alternatives ne vont pas se retrouver en tant que clés étrangères dans d'autres tables.
    Les contraintes à opérer par trigger sont d'une autre espèce. Par exemple, si j'ajoute des chambres à un hôtel, je peux utiliser un trigger d'incrémentation relative qui numérote automatiquement les chambres d'un hôtel de 1 à n.

    Ou encore, la traduction de la contrainte d'une association-type aux cardinalités 1,n - 1,1...

    MCD :
    A -1,n----associer----1,1- B

    Tables :
    A (A_id, ...)
    B (B_id, B_id_A...)

    D'après le MCD, quand on crée un A, il faut en même temps associer un B à ce A ou créer un B. Ceci peut se faire par un trigger auquel on fournit les valeurs de B en plus de celles de A ou bien dans lequel on spécifie ou calcule des valeurs par défaut pour le B qu'on crée en même temps que le A.

    Une fois en service, l'usage veut que le programmeur construise la majorité de ses requêtes,
    en posant des jointures, de sa propre initiative, entre les clefs alternatives.
    Non !
    Les jointures s'opèrent entre clés primaires des tables, donc pas entre clés alternatives qui n'existent qu'une seule fois dans la BDD.

    Exemple avec les villes françaises :
    te_ville_vil (vil_id, vil_id_pays, vil_nom_francais, vil_nom_original...) => pas de clé alternative car plusieurs villes peuvent avoir le même nom, même dans le même pays. Il faut trouver autre chose pour les différencier, pas présent ici.
    th_ville_francaise_vlf (vlf_id_ville, vlf_id_departement, vlf_code_insee, ...) => vlf_code_insee est une clé alternative
    tr_departement_dpt (dpt_id, dpt_numero, dpt_nom...) => dpt_numero et dpt_nom sont des clés alternatives

    Recherche sur le code INSEE d'une ville :
    Code SQL :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    SELECT vf.vlf_id_ville, d.dpt_numero, v.vil_nom_francais
    FROM th_ville_francaise_vlf vf
    INNER JOIN te_ville_vil v ON v.vil_id = vf.vlf_id_ville
    INNER JOIN tr_departement_dpt d ON d.dpt_id = vf.vlf_id_departement
    WHERE vf.vlf_code_insee = '76351'

    => La recherche se fait sur la clé alternative qu'on aura bien sûr pris le soin d'indexer mais les jointures se font sur les clés primaire et étrangères.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « 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 !

  11. #11
    Modérateur

    Donc, dans la pratique, avec un logiciel de modélisation (JMerise, DB Main ou un autre permettant de dessiner des MCD) :
    1) On crée les entités-types avec leurs propriétés
    2) On crée les associations-types avec leurs éventuelles propriétés et leurs cardinalités
    3) On détermine les clés primaires des entités-types en choisissant la propriété qu'on aura créée de type entier et auto-incrémentée
    4) On crée les éventuelles contraintes d'intégrité fonctionnelle
    5) On décline le MCD en MLD
    6) On vérifie ce dernier en le corrigeant éventuellement, si nécessaire en corrigeant le MCD et re-déclinant celui-ci en MLD
    7) On génère le SQL compatible avec le SGBD qu'on va utiliser
    8) On vérifie qu'il n'y a pas de conneries dans le SQL
    9) On l'implémente sur le SGBD
    10) On crée les vues, les contraintes CHECK, les assertions, les triggers, les procédures et fonctions SQL qui n'ont pas pu être créées via le logiciel de modélisation
    11) On crée les droits sur les schémas, les tables et autres objets de la BDD
    12) On remplit les tables de référence (pays, départements, civilités, listes réduites de codes...), si possible avec des scripts SQL qui importent des référentiels externe (celui de l'INSEE pour les villes françaises et les départements, par exemple)
    13) On met la BDD à disposition des développeurs de l'application qui utilisera la BDD en leur indiquant le catalogue des vues, procédures et fonctions disponibles
    14) En cas de modifications ultérieure de la structure de la BDD, on procède par scripts SQL versionnés.

    Voir le guide de SQLPro sur la création des bases de données. Il est encore plus complet en PDF sur son site personnel. Je n'ai pas encore tout appliqué mais je m'en inspire beaucoup, notamment pour le nommage des objets de la BDD.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « 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 !

  12. #12
    Membre habitué
    Bonjour Cinephil,

    Merci beaucoup, pour tout votre travail pédagogique.
    Je comprends vite mais il faut m'expliquer longtemps...

    Je vais me conformer scrupuleusement, à votre liste d'instructions (1-14).

    Je dois encore lâcher des réflexes de mon inexpérience,
    avant d'appliquer toutes les instructions, systématiquement avec confiance.

    MERCI !

  13. #13
    Modérateur

    Alors cette discussion est ?
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « 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 !

###raw>template_hook.ano_emploi###