Discussion :

[PhilippeGibault]

Bonjour,

Je me permet de poser une question pour les professionnels de la BDD.

Normalement, il est coutume d'utiliser une clé fonctionnelle. Numéro de sécurité sociale pour une personne, immatriculation pour un véhicule, SIREN pour une entreprise...

Or, pour des raisons de performance, on utilise souvent une clé technique, généralement en entier (un Long en Java), qui est autoincrémenté.

J'aimerais savoir ce que les pros des BDD en pense.

Je précise que je suis développeur Java, et je pose juste la question pour ma culture.

Cordialement.

[CosmoKnacki]

Je ne me considère pas comme pro des bdds, juste pratiquant, mais déjà je peux te dire que tous les exemples que tu as cités (SIREN, plaque minéralogique, numéro de sécu...) ont un gros désavantage pour servir de clef primaire: aucun d'entre eux ne sont immuables.
L'avantage d'une clef technique par rapport à cet aspect, est qu'elle n'est porteuse d'aucune information. Elle n'aura donc pas à être changée si le numéro de plaque, le SIREN ou le numéro de sécu change (et ce non seulement dans sa table, mais aussi dans toutes celles qui lui sont liées).
D'autre part, le fait que celle-ci soit un entier permet de bien meilleures performances étant donné qu'une comparaison d'entiers est une des opérations les plus basiques pour un processeur.

[PhilippeGibault]

Bonjour, je te remercie de ta réponse.

Néanmoins, je reste dubitatif.

D'autre part, le fait que celle-ci soit un entier permet de bien meilleures performances étant donné qu'une comparaison d'entiers est une des opérations les plus basiques pour un processeur.

C'est effectivement un argument que j'entends. Je n'ai rien à dire dessus.

mais déjà je peux te dire que tous les exemples que tu as cités (SIREN, plaque minéralogique, numéro de sécu...) ont un gros désavantage pour servir de clef primaire: aucun d'entre eux ne sont immuables.
L'avantage d'une clef technique par rapport à cet aspect, est qu'elle n'est porteuse d'aucune information. Elle n'aura donc pas à être changée si le numéro de plaque, le SIREN ou le numéro de sécu change (et ce non seulement dans sa table, mais aussi dans toutes celles qui lui sont liées).

Là, je suis moins d'accord.

Il est hors de question d'avoir 2 personne ayant le même numéro de sécurité social. C'est fonctionnellement pas possible.

Si je te donne un numéro de sécurité social, j'identifie automatiquement la personne, même si elle a changé de nom. Il en est de même pour la plaque d'immatriculation (Voiture, utilisé entre autre pour payer le parcmètre ou pire, donner des contraventions), le SIREN, l'ISBN (livre), le code postal ...

De fait, dans la pratique Java, si on part sur une clé auto-incrémenté, et que l'on a un numéro de sécurité social (par exemple), on ajoute de fait une contrainte d'unicité sur le numéro de sécurité social. Car il se doit d'être unique. C'est fonctionnel.

De fait, on a généralement 2 points de vues:

Celui (universitaire généralement) des gens de la base de donné (ou plus exactement de certains professeurs enseignant la BDD)
Qui estiment que un tuple est défini par son identifiant unique et que ce dernier est fonctionnel.

Ca va dans la continuité du modèle Entité-Association:
https://laurent-audibert.developpez....odele-a#L2-2-3

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
Celui des codeurs Java

On a une clé technique (comme la clé auto générée) et la clé fonctionnelle avec contrainte unique.

C'est pour ça que je pose la question plutôt aux DBA pour savoir ce qu'ils en pensent.

[fred1599]

Hello,

Les identifiants cités comme exemples de stabilité, SSN, SIREN, Immatriculation, sont en réalité soumis aux aléas des réformes administratives, des erreurs humaines et des évolutions sociétales. Utiliser ces données comme clé de voûte de l'intégrité référentielle expose la base de données à des risques de refonte structurelle majeurs.

En France, la structure même du NIR (Sexe, Année, Mois, Département...) porte de l'information. Si une erreur est commise à l'état civil lors de la déclaration (erreur de sexe ou de lieu de naissance) et que le NIR est corrigé a posteriori, la clé primaire change. Techniquement, cela oblige le SGBD à propager cette modification vers toutes les tables dépendantes (dossiers médicaux, salaires, impôts). Si le NIR est la clé primaire, cette opération de mise à jour en cascade (ON UPDATE CASCADE) est extrêmement coûteuse en ressources et peut bloquer l'accès aux données pendant la transaction.

L'histoire de l'immatriculation française offre un cas d'école sur les dangers de lier une structure de base de données à une nomenclature administrative. Jusqu'en 2009, la France utilisait le Fichier National des Immatriculations (FNI), où le numéro (ex: 1234 XY 75) était géographiquement lié au département. La règle métier "naturelle" était : "Un véhicule change d'immatriculation lorsqu'il change de propriétaire dans un autre département".

Pour un architecte de base de données des années 1990, utiliser l'immatriculation comme clé primaire de la table VEHICULES semblait pertinent. Cependant, en 2009, l'État a introduit le Système d'Immatriculation des Véhicules (SIV) au format AA-123-AA, attribué "à vie" au véhicule, indépendamment de sa localisation. Ce changement de paradigme a eu des conséquences dévastatrices pour les systèmes rigides :

1. Changement de format : Les champs clés devaient être élargis et le format de validation modifié.
2. Changement de logique : La clé, qui était mutable (changement de propriétaire), devenait immuable.
3. Coexistence : Les bases devaient gérer simultanément les deux formats pendant des décennies (les vieux véhicules gardant leur FNI tant qu'ils ne changent pas de carte grise).

Les systèmes qui avaient opté pour une clé technique (ex: VehicleID = 405932) ont traversé cette réforme sans impact structurel majeur. La colonne Immatriculation n'était pour eux qu'un attribut descriptif à mettre à jour, sans impact sur les liens relationnels entre le véhicule, ses contrats d'assurance et ses contraventions.

https://www.eplaque.fr/plaque-immatr...mmatriculation
https://liambx.com/blog/why-surrogate-keys-win

Le piège classique consiste à utiliser le SIRET comme clé primaire dans une table CLIENTS ou FOURNISSEURS. Or, le SIRET n'est pas stable. Si une entreprise déménage son siège social, même dans la même rue, son SIRET change. Si ce SIRET sert de clé de jointure pour l'historique des factures, le changement de clé rompt le lien avec le passé, sauf à effectuer des migrations de données complexes. Plus subtil encore, la continuité du SIREN lui-même n'est pas garantie lors de certaines transformations juridiques. Le passage d'une entreprise individuelle à une société, ou certains types de fusions-absorptions, peuvent entraîner la radiation d'un SIREN et la création d'un nouveau, alors que pour l'activité commerciale réelle, il s'agit de la même entité économique. Une clé technique interne (CompanyID) permet de modéliser cette continuité en liant plusieurs SIREN successifs à une même entité "Client" dans le système.

https://www.legalvision.fr/guides-ju...mero-de-siret/

La stabilité des clés naturelles est une illusion maintenue par la constance relative des règles administratives à un instant T. Dès lors que l'on projette le système sur 10 ou 15 ans, la probabilité d'un changement de format (passage à l'IBAN pour les comptes bancaires, réforme des plaques, fusion de régions modifiant les codes postaux) tend vers 1. Les clés techniques agissent comme une couche d'abstraction indispensable, isolant le modèle de données interne des turbulences du monde extérieur.

https://agiledata.org/essays/keys.html

[PhilippeGibault]

Je te remercie. Ta réponse m'éclaire grandement dans ma réflexion.

Cordialement;

[escartefigue]

Fred1599 a quasiment tout dit

Une PK doit être stable, concise, irréductible et "non nullable"

Or la stabilité d'une donnée externe n'est jamais garantie, ni en contenu, ni en structure.

Autres exemples que ceux cités par Fred :

• le code département, initialement exclusivement numérique sur 2 caractères, puis la Corse (initialement 20) est devenue 2A et 2B, puis les DOM ont porté des codes sur 3 caractères...
• le code NAF qui a remplacé le code API et dont le contenu change périodiquement
• le NIR ou NNI, le plus souvent stable, mais qui peut changer à la marge (cas des changements de sexe)
  de plus, j'ai connu des S.I. dans lesquels le NIR n'était pas unique : cas des enfants n'ayant jamais cotisé qui héritent du NIR de l'un des parents.

Liste non exhaustive.

Autre argument en faveur d'un identifiant technique attribué par le SGBD : non seulement les types integer sont plus concis et plus performants que les types char ou varchar, mais il sont également insensibles à la collation, contrairement aux (var)char.

Et une précision : les identifiants attribués par le SGBD ne sont pas forcément incrémentaux, certains SGBD autorisent aussi le décrément

[SQLpro]

...
Il est hors de question d'avoir 2 personne ayant le même numéro de sécurité social. C'est fonctionnellement pas possible.


Si je te donne un numéro de sécurité social, j'identifie automatiquement la personne, même si elle a changé de nom. Il en est de même pour la plaque d'immatriculation (Voiture, utilisé entre autre pour payer le parcmètre ou pire, donner des contraventions), le SIREN, l'ISBN (livre), le code postal ...

De fait, dans la pratique Java, si on part sur une clé auto-incrémenté, et que l'on a un numéro de sécurité social (par exemple), on ajoute de fait une contrainte d'unicité sur le numéro de sécurité social. Car il se doit d'être unique. C'est fonctionnel.

Vous avez raison et le but d'une clé primaire est d'être unique donc de rejeter l'information avant même de l'avoir insérée dans la base (contraintes de clé primaire = UNIQUE et NOT NULL).
Sauf que cela c'est la théorie.... En pratique avec le changement de siècle il y a eu plusieurs personnes dont on a attribué le même numéro de sécurité sociale... https://chrysalide-asso.fr/nir/ De plus, les enfant utilisent le même numéro de sécurité sociale que leurs parents jusqu'à leur majorité ou avant s'ils sont couvert par des cotisations car employés...

Pour une immatriculation, vous pouvez avoir strictement la même valeurs pour deux véhicules différents, s'ils sont immatriculés dans deux pays différents...
Pour l'immatriculation, des entreprises il en est de même...
Dans ces deux derniers cas, il faudrait rajouter la pays en sus de l’immatriculation... Et donc la lourdeur qui va avec...

Pour l'ISBN pas de souci car international... Sauf qu'il y a eu des erreurs d'impression sur certains livres !

Dans tous les cas si vous utilisez une clé fonctionnelles c'est beaucoup plus complexe et plus lourd à gérer. Un seul exemple pour les immatriculations. Avant les n° était du style AA-NNN-AA (notez le tiret qui est obligatoire et important en France pour ne pas faire confusion avec l’Italie ou l’Albanie !
Donc 9 caractères... Si vous êtes en UFT8 avec MySQmerde cela est codé par 27 octets...
A contrario, un entier 2 bits, c'est 4 octets donc 7 fois plus concis et 7 fois plus performant...
En sus, les chaines de caractères nécessite une gestion de collations (confusion ou non des majuscules/minuscule, caractères diacritiques ou non), ce qui entraine une surcharge d'exécution du processeur...

Et puis vous n'êtes jamais à 'abri d'une erreur de saisie qui obligera toutes les tables liées à être mise à jour en correspondances !!!

D’où la proscription de l'utilisation d'une clé fonctionnelle dans tout modèle de données sans exception...

mais cela n'empêche pas d'avoir une double clé dans certaines tables :
1) la clé primaire en autoincrément => NOT NULL et UNIQUE (contrainte PRIMARY KEY)
2) une clé alternative (aussi appelée subrogée) au contenu fonctionnel => NULLable et UNIQUE (contrainte d'unicité)

A +

[fsmrel]

Ave,

cela n'empêche pas d'avoir une double clé dans certaines tables :
1) la clé primaire en autoincrément => NOT NULL et UNIQUE (contrainte PRIMARY KEY)
2) une clé alternative (aussi appelée subrogée) au contenu fonctionnel => NULLable et UNIQUE (contrainte d'unicité)

Pour une table donnée, en principe le nombre de clés alternatives n’est bien entendu pas limité.

« Subrogée » : quel horrible adjectif relevant du droit civil… faisons comme Chris Date, utilisons plutôt « candidate »…

En aparté :
Quand on a mal à la tête, prendre de l’aspro.
Quand une base de données ne va pas bien, la soigner avec du SQLpro...

[PhilippeGibault]

Je vous remercie tous.

J'ai une idée plus claire sur la question.

[SQLpro]

....
« Subrogée » : quel horrible adjectif relevant du droit civil… faisons comme Chris Date, utilisons plutôt « candidate »…

ha non, clé candidate c'est l'ensemble des clés avant le choix de la PK!

A +

[fsmrel]

ha non, clé candidate c'est l'ensemble des clés avant le choix de la PK!

Où as-tu lu ça ? Certainement pas chez Date. Consulte son bouquin « SQL and Relational Theory » (3e édition).

A la page 149 (je traduis) :

Une clé candidate (clé pour abréger) est un sous-ensemble d’attributs K de l’en-tête H d’une variable relationnelle R, respectant les deux contraintes suivantes :

― Unicité. Deux n-uplets distincts de R ne peuvent pas avoir simultanément même valeur de K.

― Irréductibilité. Il n’existe pas de sous-ensemble strict de K garantissant la contrainte d’unicité.

Chaque variable relationnelle est obligatoirement dotée d’au moins une clé candidate.

-------------
Point barre. Pas de PK.

En descendant au ras des pâquerettes, c’est-à-dire chez Quasimodo, avec son Sorry Query Language, on peut évidemment utiliser des PK.

[fred1599]

Sur le débat entre @SQLpro et @fsmrel, je vois trois points,

1. Sur le nombre de clés alternatives : @fsmrel a théoriquement raison. Le modèle relationnel ne limite pas le nombre de clés candidates. Les limites ne sont qu'une conséquence des implémentations physiques des SGBDR.
2. Sur la terminologie « subrogée » : @fsmrel souligne avec raison l'incongruité du terme dans le contexte du droit français, bien que le terme anglais surrogate soit standard dans la littérature de Chris Date. L'usage de « candidate » ou « alternée » est effectivement plus conforme à l'esprit de la théorie de Codd.
3. Sur la définition de la clé candidate : @fsmrel est en accord avec les textes fondateurs de 1971. Une clé candidate est une propriété structurelle de la relation qui persiste après le choix de la clé primaire. @SQLpro utilise une définition « processus » qui, bien que pratique pour l'enseignement, manque de précision formelle.

En conclusion, la maîtrise de la base de données relationnelle exige de comprendre que derrière chaque clé primaire artificielle (ou subrogée) se cachent souvent plusieurs clés candidates naturelles qu'il est impératif d'identifier et de contraindre pour préserver l'intégrité sémantique du système. Le débat entre vous deux n'est pas une simple querelle de mots, mais le reflet de deux visions de l'informatique : l'une attachée à la pérennité des concepts mathématiques, l'autre aux nécessités de la construction logicielle. Dans l'idéal de la conception, ces deux approches doivent converger pour produire des modèles à la fois performants et logiquement irréprochables.

[fsmrel]

Le débat entre vous deux n'est pas une simple querelle de mots, mais le reflet de deux visions de l'informatique

Pas d’inquiétude, ça fait près de 20 ans que, tel un vieux couple, nous chipotons sur des points secondaires. Quant aux fondamentaux nous sommes en phase.

[fsmrel]

bien que le terme anglais surrogate soit standard dans la littérature de Chris Date

Au sens d’entité ou d’identifiant d’entité, mais les surrogate keys ne font pas partie du modèle relationnel.

Le concept de "surrogate" apparaît d’abord en 1976 dans "Relations and Entities" de Hall, Owlett et Todd. Un surrogate y est un identifiant affecté par le système à une entité (cf. [1] page 176).

Dans RM/T (cf. [2]), Codd achète le concept de surrogate, mais Date fait observer (cf. [3]) qu’un surrogate n’étant pas visible par l’utilisateur, il y a là un viol patent des règles auxquelles souscrit le Modèle Relationnel :  

“hiding surrogates constitutes a violation of Codd's own Information Principle, which states that all information in the database must be cast explicitly in terms of values in relations and in no other way.”

Dans son dernier ouvrage, RM/v2 (cf. [4]), Codd a manifestement tenu compte de cet avertissement, exeunt les surrogate keys...

Je rappelle que les surrogate keys sont exclues du modèle relationnel, vous ne les trouverez pas dans l’ouvrage de référence [5].


[1] C. J. Date - An Introduction to Database Systems, 2e édition.
[2] E. F. Codd - Extending the Relational Database Model to Capture More Meaning.
[3] C. J. Date - Thirty Years of Relational: Extending the Relational Model (1999)
[4] E. F. Codd - The Relational Model for Database Management, version 2.
[5] C. J. Date & Hugh Darwen - Databases, Types and the Relational Mddel, The Third Manifesto.