Discussion :

[martinbrait]

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 !!

[escartefigue]

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.

[martinbrait]

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 ?

[CinePhil]

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.

[martinbrait]

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?

[CinePhil]

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 ?

La raison essentielle pour laquelle tout code sémantique est à proscrire comme clé primaire d'une table est la possibilité de changement de valeur.
Prenons le cas de la République Démocratique du Congo. Son code ISO actuel est COD ou CD ; son suffixe de domaine internet est .cd.
De 1971 à 1997, ce pays s'est appelé le Zaïre. Wikipedia ne nous dit pas quel était son code ISO mais son domaine internet était .zr.
Imaginons que ce pays instable change de nouveau de nom, il est fort possible que son code ISO (plutôt mnémotechnique) change, ainsi que son domaine internet. En utilisant l'un de ces codes en clé primaire, il faudrait changer la valeur des toutes les clés étrangères référençant ce pays. C'est évidemment faisable, surtout si la clause ON UPDATE CASCADE a été mise sur ces clés mais cela peut prendre du temps sur de gros volumes de données. Il faudra aussi potentiellement reconstruire tous les index sur ces clés. Ça peut être lourd sur une grosse BDD et bloquer la production un certain temps.
Alors que si le pays actuel a pour clé primaire le numéro technique et a-sémantique 225, même si le pays change de nom, cette clé ne change pas et il suffit de modifier seulement la table des pays. Ça prend le temps considérable d'environ... 30 secondes pour l'opérateur humain et une fraction de seconde pour le SGBD.
Bon, pour être exact, il faudra peut-être créer une nouvelle ligne avec le nouveau nom et marquer la ligne actuelle comme obsolète mais c'est tout.

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

C'est un code mnémotechnique. Il peut servir à préfixer un classement par pays (par exemple pour une multinationale ayant des filiales dans beaucoup de pays), à simplifier l'affichage d'une liste déroulante courte au lieu d'afficher le nom des pays en entier, si les opérateurs ont l'habitude des codes utilisés...

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 ?

La nomenclature INSEE est décomposée en régions, départements, cantons et communes. Le code INSEE d'une commune dépend de son département. Le code de ma commune de naissance, Le Havre, est 76351, c'est à dire la 351ème commune du département 76, au moment où a été établi la liste triée par ordre alphabétique.
Des communes changent. Elles fusionnent, se séparent, disparaissent ou naissent. Certaines en absorbent d'autres. Par exemple, la commune de Rouelles a été absorbée par Le Havre dans les années 1970.
Il n'est pas impossible qu'un jour, l'INSEE décide de revoir sa nomenclature. La commune physique existera toujours mais son code changera.
=> Là encore, une clé auto-incrémentée est préférable.

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 ?

Non. Si la conception détermine que le code ISO est inutilisé, il n'est pas indispensable de l'implémenter dans la base de données. Mais il faudra quand même une clé primaire et c'est bien la clé auto-incrémentée qui est la plus pratique. Quitte à ce que le besoin du code ISO apparaisse plus tard : il suffit d'ajouter la colonne à la table de pays et/ou des villes/départements/régions.

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 ?

C'est une clé mnémotechnique officielle qui peut être utile selon l'organisation mais qui n'est pas systématiquement indispensable.

• 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?

Ne pas l'utiliser comme clé primaire parce qu'elle est susceptible de changer de valeur, contrairement à la clé auto-incrémentée qu'ON NE DOIT PAS MODIFIER.

[martinbrait]

Merci beaucoup CinePhil,
je crois maintenant que tout est bien clair !

[fsmrel]

Martin,

N'oubliez pas de voter pour CinePhil qui se décarcasse !

[martinbrait]

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 ???

[CinePhil]

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.

[CinePhil]

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.

[martinbrait]

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 !

[CinePhil]

Alors cette discussion est ?