Discussion :

[Erwinner]

Bonjour,

Étant à la conception du modèle logique de la base de données d’une future application, je bute sur obstacle dont je ne trouve solution.

Voici ci-dessous un extrait du MPD en cours de réalisation. Celui-ci définira la structure d’un jeu de données relatif à la gestion de sortie d’un club de plongée. Trois des tables visibles disposent des mêmes attributs ; si ce n’est la longueur de ceux-ci qui diffère. Or, les données qu’elles contiendront auront le même rôle : définir l’état d’une caractéristique d’une table représentant un objet (ici, Utilisateur et Sortie). Donc voici mon interrogation : est-il préférable de définir des tables similaires avec un nommage différent pour une lecture plus confortable (peut-être) ou garder une seule table et mettre en place une vue pour chacun des « synonymes fonctionnel » (je ne sais qu’écrire de plus correct) (rang — status — Section) ?



Autre question, dans la table Utilisateur, serait-il plus judicieux de définir une autre table « Compétence » qui contiendrait « D.P., Gonfleur, Encadrant » et de la joindre à la table Utilisateur avec une table « Capacité » ?

En vous remerciant,
Erwinner

[fsmrel]

Bonjour Erwinner,

Des 3 entités-types n’en faire qu’une impose la mise en oeuvre d’une 4e table, nommons-la par exemple TYPE_ROLE, pour qu’on puisse définir le type de ce à quoi il est fait référence : rang, status, section. Par ailleurs, il faudra être vigilant dans la mise à jour des tables, que pour l’utilisateur U1 on n’aille pas faire référence par mégarde à une section ou à un statut, etc.

Je pense que conserver les 3 tables est préférable.

[fsmrel]

serait-il plus judicieux de définir une autre table « Compétence » qui contiendrait « D.P., Gonfleur, Encadrant » et de la joindre à la table Utilisateur avec une table « Capacité » ?

Oui, il serait bon de mettre en oeuvre une entité-type COMPETENCE, en relation avec UTILISATEUR via une association CAPACITE. En procédant ainsi, votre modèle peut évoluer : affecter une note à une capacité d’un utilisateur, pouvoir définir à quelle date il a acquis cette capacité, etc.
Qui plus est, s’il faut un jour définir de nouvelles compétences, avec votre système actuel, au stade SQL vous serez coincé, il faudra changer la structure de la table UTILISATEUR, opération toujours délicate, sinon périlleuse.

[escartefigue]

Bonjour,

D'accord avec ce qui précède.

Quelques remarques :

• je suis toujours surpris de voir des modèles dans lesquels le mot de passe autorise jusqu'à 255 caractères , mémoire d'éléphant requise ;
• à l'inverse, une adresse courriel peut aller jusqu'à 254 caractères. Cf. https://www.ipgirl.com/817/quelle-es...il-valide.html ;
• il serait également préférable de modéliser une entité-type pour les lieux : plusieurs sorties peuvent concerner le même lieu ;
• et surtout, il est préférable d'utiliser pour identifiants primaires des attributs de type integer plutôt que de type (var)char(n). Privilégiez les identifiants attribués par le SGBD, c'est un gage de stabilité et de performance.

[Erwinner]

@fsmrel
Merci pour vos réponses : elles me conviennent et j'effectue les modifications.

@escartefigue
Soyez remercié pour vos précisions : je vais m'en servir pour affiner le choix des variables

Pour le mot de passe, je ne sais encore qu'elle sera la longueur de clé de hachage du mot de passe (il me semble qu'elle fera 60 caractères, mais j'attends les précisions du développeur)

Puisque vous abordez le sujet des clés primaires, je me permets de vous donner la raison du choix d'une chaîne de caractère de longueur invariante : celles-ci sont définie de cette manière [Trigramme de la table]([0-9]x 'longueur de la clé primaire' - 3). Exemple: pour les utilisateurs, cela donnerait UTL000000001,UTL000000002. Cela est-il vraiment problématique ? Une paire ID (int) [primaire] et REF (char) [Unique] serait plus efficace ? (Désolé pour l'écriture des expressions techniques hasardeuse).

===

Mais de manière générale, créer 3 tables analogues (si ce n'est le titre qui change) au lieu d'une table et 3 vues est-il plus judicieux ?

[escartefigue]

Bonsoir Erwinner

Les caractéristiques incontournables d'une clef primaire sont d'être unique, non "nullable" et irréductible.
À partir de là, on a le choix du type de données.
Sachant que la clef primaire est la colonne la plus souvent utilisée comme critère de jointure entre les tables, il est important de choisir le type le plus optimal pour les performances et surtout le plus stable pour éviter qu'une modification de la valeur de cet identifiant implique des modifications en cascade et en masse dans les tables où cette colonne est clef étrangère.

Le meilleur moyen de ne pas avoir à modifier cet identifiant primaire est de ne pas choisir une valeur fonctionnelle : à partir du moment où c'est un identifiant purement technique, dénué de sens, il n'y a aucune raison d'en changer la valeur.
De ce point de vue concaténer un mnémonique de la table ("UTL" dans votre cas), avec un chrono technique répond au besoin (sauf si ce mnémonique table devait changer).
Mais, ce choix est inutile, encombrant et contre performant.
Inclure l'identifiant de la table dans la PK ne présente aucun intérêt et consomme dans votre cas 3 octets, qui seront transportés sur le réseau à chaque fois que la colonne sera utilisée, donc très souvent puisque c'est le critère de jointure avec les autres tables.
De plus, le type char prend beaucoup plus de place qu'un type integer, par exemple, un integer de 4 octets peut contenir un peu plus de 4 millions de valeurs distinctes alors qu'un type char de 4 octets ne prendra que 9999 valeurs possibles si on considère que ce sont des valeurs numériques, un peu plus si on autorise les caractères alpha, ce qui n'est pas votre choix.
Enfin, les types caractères (char ou varchar) sont sensibles à la collation contrairement aux types numériques (et notamment au type integer). Ce qui signifie que toutes les opérations de tri (induites par les clauses DISTINCT, GROUP BY, ORDER BY, PARTITION BY) n'auront pas les mêmes résultats pour une même valeur si la collation est différente. Attention donc !

[fsmrel]

Bonsoir Erwinner,

Puisque vous abordez le sujet des clés primaires, je me permets de vous donner la raison du choix d'une chaîne de caractère de longueur invariante : celles-ci sont définie de cette manière [Trigramme de la table]([0-9]x 'longueur de la clé primaire' - 3).

Au stade relationnel (et SQL), une clé primaire est un cas particulier de ce qu’on appelle une clé candidate. Le concept de clé primaire existe pour des raisons historiques, mais pourrait très bien passer au rasoir d’Ockham (ne pas empiler les concepts au-delà du nécessaire).
Disons qu’une clé candidate (une clé primaire pour faire court) peut servir de référence dans le contexte de l’intégrité référentielle (clés étrangères) et, comme le souligne Escartefigue, c’est là qu’il faut faire très attention.

En remontant au niveau MCD, suivons ce que dit Yves Tabourier (cf. De l’invariance des clés primaires).

Autrement dit, votre clé primaire ne devrait être porteuse d’aucune information et ses valeurs doivent être cachées aux utilisateurs et aux requêtes. Par contre votre montage Trigramme de la table, longueur de la clé primaire peut faire l’objet d’une clé alternative « publique », même si votre montage est pour le moins étrange.

Pour définir un identifiant alternatif avec PowerAMC : PowerAMC : définir un identifiant alternatif

Concernant les anecdotes au sujet des clés primaires porteuses de sens :

D’une manière générale, un identifiant (par voie de conséquence une clé primaire en SQL) doit être non significatif et invariant. Par exemple si un login est utilisé pour une clé primaire, il n’en demeure pas moins qu’il peut être modifié (ça arrive même chez Developpez.com...) et alors bonjour les problèmes si des clés étrangères y font référence.

Remontons dans le passé et situons-nous par exemple en 2005. Imaginez une application dans laquelle la clé primaire de la table VEHICULE est {NoImmat} où NoImmat représente le numéro d’immatriculation des véhicules en France à cette époque : chaque véhicule est identifié par son immatriculation. Comme par hasard, un développeur livre des programmes dans lesquels il se sert des deux chiffres de droite pour tirer des conclusions sur la localisation du véhicule dans quelque département. En 2009, un nouveau système d’immatriculation fut mis en place, celui qu’on utilise aujourd’hui en France. Quelles conséquences sur les programmes existants ? Sur la base de données ? (En effet, il se peut que plusieurs tables référencent la table VEHICULE par le mécanisme des clés étrangères...)

Dans le même ordre d’idées, je me souviens des affres vécus par les informaticiens chez les opérateurs téléphoniques lors du passage à la numérotation de 8 à 10 chiffres...

Un exemple dont je me sers de temps en temps : il s’agit du système préconisé par les concepteurs dans une très grande banque, consistant à identifier les entreprises par leur numéro Siren, fourni par l’INSEE. Par voie de conséquence, au niveau du MLD (Modèle Logique de Données selon Merise), puis SQL, ce Siren devait être propagé dans toute la base de données par le jeu des liens clé primaire - clé étrangère. Les concepteurs avaient monté une véritable usine à gaz pour maintenir la cohérence entre les tables, parce que l’INSEE envoyait tous les mois les nombreux correctifs modifiant les numéros de Siren erronés. Tout en leur commentant les écrits d’Yves Tabourier, je leur suggérai que l’identifiant de l’entreprise fît l’objet d’une propriété nouvelle et artificielle, invariante et sans signification, EntrepriseId, le numéro Siren devenant pour sa part identifiant alternatif, c'est-à-dire conservant sa spécificité : être unique et pouvant subir toutes les modifications et tortures, au gré de l’utilisateur. Au niveau MLD et SQL, l’ex clé primaire, le numéro Siren fit donc l’objet d’une clé alternative, point d'entrée dans la table, permettant à l'utilisateur d’accéder aux données de chaque entreprise, la suite des opérations se passant de façon transparente, encapsulée pour utiliser un jargon à la mode, grâce à la nouvelle clé primaire {EntrepriseId} prenant le relais de sa copine clé alternative : du point de vue de l’utilisateur et des règles fonctionnelles, rien de changé, mais l’usine à gaz disparut de facto en fumée, des économies importantes furent réalisées, et tout le monde fut content.

[Erwinner]

Bonsoir escartefigue et bonsoir fsmrel,

Vous m’avez tous deux bien fait comprendre l’utilité et surtout la nécessité de l’utilisation uniquement fonctionnelle de la PK au sein du schéma relationnel. Et dire que j’étais personnellement satisfait d’une telle gestion des PK…

Je ne vous remercierai jamais assez pour le temps que vous avez consacré à cette « petite » discussion et surtout pour ce retour dans le droit chemin