La vérité sur les id
Démarrez la déclaration de toutes vos tables relationnelles par une colonne identité, nommez-la comme bon vous semble mais faîtes-en la clé primaire. Si vous êtes convaincu.e du bien fondé de cette phrase, pas besoin de lire cet article.
Je vais vous parler ici d’une difficulté assez spécifique aux profs, coachs, formateurs, enseignants et autres partageurs de connaissances qui officient, même ponctuellement, dans le domaine des données et plus spécifiquement de SQL.
En algèbre relationelle, la notion d’identité est primordiale. Mais elle est souvent mal interprétée car le profil le plus répandu parmi toutes les personnes qui se targuent de connaître SQL est celui de développeur (web ou logiciel, peu importe ici). Et un développeur, ça a beaucoup plus l’habitude de travailler avec un langage de programmation qu’avec SQL. Heureusement.
Tant qu’on traite de généralités, un développeur, en 2021, connaît forcément au moins un langage orienté objet ou assimilé : C++, Java, C#, JS, Python, PHP etc. Tous ces langages partagent des grands principes dont, devinez quoi, celui de l’identité. Mais contrairement aux enregistrements d’une base de données, dans tous ces langages, un objet a une identité propre, qu’on distingue de son état. Pour faire court, 2 objets en tous points similaires (dont toutes les propriétés ont des valeurs identiques) sont tout de même distincts et peuvent donc évoluer chacun de leur côté. L’origine du fameux débat identique vs équivalent.
C’est d’ailleurs également ce qu’on constate dans la vraie vie :
- Il est fort probable qu’il existe en ce moment même 2 personnes s’appelant Pierre Martin ;
- Ces 2 personnes ont certainement le même genre (elle est facile, celle là) ;
- Parce qu’il en existe beaucoup plus que deux, on peut sûrement en trouver 2 résidant dans le même pays, ayant la même couleur d’yeux, nés la même année, peut-être même ayant le même parfum de glace préféré.
Bref, trève de probabilités, vous voyez le tableau. Ces 2 Pierre vont paraître similaires si on se contente de lister ce qui les rassemble. Pourtant, ils évoluent chacun de leur côté, ne se situent pas au même endroit géographiquement, n’ont peut-être pas le même métier ni le même salaire. Ils sont équivalents mais pas identiques.
En SQL, si vous créez une table (sans id) ne contenant strictement que des colonnes pour lesquelles les valeurs de ces 2 individus sont identiques (nom, prénom, genre, pays de résidence, couleur d’yeux, année de naissance et parfum de glace préféré), vous allez alors créer, en insérant les 2 enregistrements correspondants, quelque chose qu’on ne peut pas reproduire IRL : un doublon.
Si vous souhaitez creuser le sujet et comprendre en quoi cela peut être gênant, je vous recommande chaudement la lecture d’un article de Ted Neward : http://blogs.tedneward.com/post/the-vietnam-of-computer-science/
Identifier, c’est la clé
C’est là qu’intervient l’inventeur du modèle relationnel qu’on connaît tous aujourd’hui (le modèle, pas son inventeur), Edgar F. Codd. C’est à ce monsieur qu’on doit la notion de clé candidate. Afin d’offrir une identité propre à chaque Pierre Martin au sein d’une base de données relationnelle, il suffit de leur ajouter (ou de désigner, si elle est déjà là) une clé (=une donnée) qui leur est propre. On en fait alors la clé primaire de la table. On attribue parfois des pouvoirs mystiques à cette contrainte, mais ce n’est en fait que le produit de 2 à 3 contraintes basiques : l’unicité (personne n’a la même), la non-nullité (tout le monde en a une) et, en option, la création d’un index associé (on connaît, à un instant t, toutes les valeurs distinctes de cette clé).
Cette clé candidate peut être de 2 natures :
- naturelle (de nature naturelle, donc 🤡) : une clé naturelle est une donnée préexistante, ayant un caractère unique, qu’il suffit de collecter et de renseigner dans la base de données. En France, on pourrait prendre le numéro de carte d’identité ou de sécurité sociale pour identifier des personnes, par exemple ;
- artificielle (ou synthétique, si on traduit littéralement l’anglais) : il s’agit là d’une donnée créée de toutes pièces par le SGBD et apposée à chaque enregistrement. Ici, on parle invariablement de l’id qu’on trouve dans toutes les tables qui se respectent.
Comment choisir ?
Parce qu’en général, on passe par une phase de conception avant de créer une base de données, il arrive couramment qu’on identifie à l’avance une ou plusieurs clés naturelles dans les entités d’un MCD, par exemple. La méthode Merise est assez stricte sur ce point : toute entité devrait théoriquement posséder un attribut discriminant. Même dans le cas d’une entité faible, on s’assure toujours que chaque instance soit identifiable par quelque chose. Mais ces clés naturelles font de piètres candidats pour une clé primaire.
Aujourd’hui, tous les profs (je regroupe ici tous les individus que j’ai cités au début de cet article) sont d’accord pour dire que ces discriminants, ces clés candidates, se manifesteront plutôt sous la forme d’une contrainte d’unicité sur les colonnes correspondantes, tandis qu’on apposera une colonne id, une clé synthétique, à chaque table en guise de clé primaire.
Quelque chose comme ça :
CREATE TABLE person (
id int PRIMARY KEY,
licence_number text NOT NULL UNIQUE,
first_name text NOT NULL,
last_name text NOT NULL,
birthdate date
);CREATE TABLE vehicle (
id int PRIMARY KEY,
licence_plate text NOT NULL UNIQUE,
brand text NOT NULL,
model text NOT NULL,
color text DEFAULT ‘grey’,
owner_id int NOT NULL REFERENCES person(id),
date_of_purchase date
);
Le chant des sirènes
Pourtant, certaines fonctionnalités du langage SQL et de nombreux exemples sur Internet semblent narrer une autre réalité : l’utilisation d’une clé naturelle comme clé primaire. Avec pour conséquence que les clés étrangères qui en découlent reprennent cette clé primaire. Ce qui donnerait plutôt ceci :
CREATE TABLE person (
licence_number text PRIMARY KEY,
first_name text NOT NULL,
last_name text NOT NULL,
birthdate date
);CREATE TABLE vehicle (
licence_plate text PRIMARY KEY,
brand text NOT NULL,
model text NOT NULL,
color text DEFAULT ‘grey’,
owner_number text NOT NULL REFERENCES person(licence_number),
date_of_purchase date
);
Bizarre, non ? Pourtant, c’est totalement valide. Cette base pourrait être celle d’un garagiste, par exemple :
- Ses clients seraient référencés dans la table person qui contient quelques infos administratives.
- Les voitures qu’il répare pour ses clients se trouveraient logiquement dans la table vehicle. Elle contiendrait sûrement pas mal d’autres champs parce qu’il y en a, des choses à noter sur un véhicule quand on est garagiste (mais moi, je ne suis pas garagiste donc je ne les connais pas).
Dans la table vehicle, il y a une clé étrangère vers la table person. Forcément. Parce qu’un client peut lui présenter plusieurs véhicules à réparer, mais un véhicule ne peut être rattaché qu’à un seul client. Sinon, pour facturer, c’est plus compliqué. Puisqu’il n’y a pas d’id (=de clé synthétique) dans la table client, on va naturellement utiliser le numéro de permis comme clé étrangère. Problem solved !
On peut trouver sans mal ce genre d’exemple sur les internets. En fait, n’allez pas chercher loin, rien que la page Wikipedia des formes normales utilise un exemple de clé naturelle primaire. Et ça n’a rien de faux.
L’autre élément dans l’apprentissage de SQL qui laisse souvent les étudiants perplexes, c’est l’existence d’une clause ON UPDATE lors de la déclaration d’une clé étrangère. Cette clause permet de déterminer la réaction d’une ligne contenant une clé étrangère lorsqu’on modifie la valeur de la clé primaire correspondante. Exemple : si la clé étrangère de vehicle est configurée sur ON UPDATE CASCADE et qu’un véhicule a pour propriétaire 012345ABCD, la modification du numéro de permis de ce client en 002244ABCDentraînera la modification de la clé étrangère.
Mais si on utilise une clé synthétique, pourquoi irait-on modifier la valeur d’un id généré par le SGBD ? Alors qu’on nous rabâche que la gestion de la clé synthétique doit être entièrement laissée au SGBD, pas touche ! La réponse, c’est qu’avec une clé synthétique, on ne va effectivement jamais toucher sa valeur, et donc ne jamais déclencher d’exécution de la clause ON UPDATE d’une clé étrangère.
Toujours est-il qu’il y a bel et bien des preuves formelles (et pas que sur Wikipedia, dans la doc officielle de tout bon SGBD) qu’on peut utiliser autre chose qu’une clé synthétique en guise de clé étrangère !
Oui, mais.
Ces pratiques doivent être remises dans un contexte temporel bien différent du nôtre : les années 70. Si SQL n’a été normalisé qu’en 1986, il date bel et bien de 1974 et l’algèbre sur laquelle il se base date de 1970, tout pile. En 1970, je n’étais pas né et je pourrais vous raconter des anecdotes toutes plus incroyables les unes que les autres sur cette période, ça ne serait que très moyennement crédible. Je vais me contenter de vous parler de ce que je sais : le coût du stockage des données.
En 1970, la plupart des bases de données était de taille extrêmement faible (en considérant nos normes actuelles) parce que le stockage des données était très coûteux. Si vous avez plus d’une vingtaine d’années, vous devez vous rappeler, dans votre jeunesse, des premières clés USB 8Mo, des disques durs 30Go qui coûtaient presque un SMIC. Imaginez ce que c’était encore 20–30 ans plus tôt. En fait, arrêtez d’imaginer et regardez plutôt ce graphique :
En 1970, un unique mégaoctet de données sur un big drive coûtait approximativement 250 dollars. Je ne suis pas tout à fait sûr de ce qu‘est un big drive exactement, mais c’était probablement un gros machin inamovible produisant plus de bruit qu’un Boeing qui décolle et plus de chaleur… qu’un Boeing qui décolle (pratique, cette comparaison). Pour cette raison, l’utilisation d’une clé synthétique n’était même pas considérée à l’époque.
Puisqu’en général, on pouvait facilement identifier une ou deux clés candidates parmi les données à stocker, il était naturel d’en choisir une pour la promouvoir au rang de clé primaire. Et une fois qu’on a fait ce choix, ça limite les options quant à la mise en place de clés étrangères.
Le retour du garagiste (mais dans les seventies)
Même si un garagiste de l’époque ne pouvait certainement pas se payer le moindre ordinateur, s’il avait pu, il aurait à coup sûr été muni d’un SGBD respectant le schéma décrit plus haut. Eh oui, pourquoi s’embêter à :
- ajouter une colonne id à une table, et ainsi consommer quelques octets de plus pour stocker son nom, son type et ses caractéristiques dans les métadonnées du SGBD ?
- réserver, pour chaque enregistrement, 4 octets supplémentaires (le calcul est ci-dessous, patience) pour stocker chaque valeur ?
- et développer un algorithme générant une valeur unique pour chaque insertion d’un nouvel enregistrement dans la base, consommant d’autres ressources précieuses (mémoire, temps de calcul) ?
Pure folie !
Paradoxalement, une clé étrangère était alors plus coûteuse à l’époque. Seulement, comme ce coût est indispensable (si on veut une clé étrangère, il faut forcément une colonne pour l’accueillir), on réfléchissait moins au montant de la dépense. Pourtant, pour notre garagiste des seventies, si on considère une base de 3000 véhicules (ce qui est peu), voilà ce que lui aurait coûté chacune des 2 solutions :
Avec une clé naturelle, ici le numéro de permis de conduire (12 caractères alphanumériques), il fallait consommer, pour chaque clé étrangère, 24 octets. Unicode datant de 1993, il y a fort à parier qu’à l’époque, on stockait chaque caractère comme un mot de 16 bits (l’architecture des processeurs de l’époque). Pour 3000 valeurs, cela représente 72ko de stockage pour cette seule colonne. 18 dollars, juste pour retrouver à qui appartient quelle voiture.
Côté clé synthétique, prenons un bigint pour voir les choses en grand. Toute architecture confondue, un int est stocké comme un mot et un bigint comme deux. En 16 bits, un bigint prend donc 4 octets pour son stockage. Pour 3000 valeurs, on monte à 12ko de stockage pour l’incroyable prix de 3 dollars.
Ok, les calculs sont naïfs, mais l’ordre de grandeur est bien là. C’était un mauvais calcul depuis le début, mais on a mis très longtemps avant de s’en rendre compte. Tant et si bien qu’aujourd’hui, tout ce qui concerne de près ou de loin le modèle relationnel ou qui a été pensé à cette époque souffre de ce biais. De l’existence de la clause ON UPDATE jusqu’à la page Wikipedia sur les formes normales.
Retour vers le présent
Aujourd’hui, un mégaoctet sur le support le plus cher qui soit (de la mémoire vive) coûte une fraction de centime. Et aujourd’hui, une base composée de 2 tables, l’une comptant 3000 lignes et l’autre un peu moins, ce n’est plus un signe ostentatoire de richesse de garagiste mais bien quelque chose de commun, voire désuet. Par voie de conséquence, on se pose beaucoup moins de questions sur l’utilisation d’une clé synthétique en guise de clé primaire.
Ce n’est pas une histoire de gâcher de la place parce qu’elle coûte 250 000 fois moins chère, mais plutôt de l’économiser car aujourd’hui, les bases comptent beaucoup plus de tables et les tables beaucoup plus de clés étrangères. Et c’est bien ici qu’on fait des économies en utilisant de simples entiers.
Le cas des tables intermédiaires
Je fais partie de ces extrêmistes qui appliquent la règle de la clé synthétique de façon automatique et systématique. Mais pas hydromatique, parce que je n’ai pas encore trouvé comment. Remarquez, pour ceux qui ont la référence, qu’on en revient une fois de plus aux voitures et aux années 70.
Mais il existe une frange plus raisonnable de la population qui ne l’applique pas à un cas bien particulier : les tables intermédiaires. Ou tables de jointure. Ou tables de liaison. Bref, ces tables qui permettent de matérialiser une relation de plusieurs à plusieurs dans une base relationnelle. Je m’apprête à vous démontrer que c’est moi qui ai raison. Mais qu’ils n’ont pas si tort que ça.
Prenons l’exemple classique d’une bibliothèque. Dans les années 80. Non, je plaisante, une bibliothèque d’aujourd’hui fera très bien l’affaire. Sa base de données doit logiquement contenir une table usager et une table livre. Et parce qu’on est en 2021 et que le concepteur de cette base est quelqu’un de raisonnable, il a déclaré un entier autoincrémenté en tant que clé primaire. Il a même utilisé le nouveau mécanisme standard, nommé colonne identité, pour faire ça.
CREATE TABLE book (
id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL,
[...]
);CREATE TABLE reader (
id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
card_number int NOT NULL UNIQUE,
[...]
);
Et comme un livre est emprunté successivement par plusieurs usagers, et qu’un usager peut emprunter plusieurs livres (et même plusieurs fois le même livre, sur des périodes différentes), on a besoin d’une table intermédiaire, nommée emprunt. Elle contient les champs id_livre, id_usager, date_emprunt, date_retour.
CREATE TABLE loan (
book_id int NOT NULL REFERENCES book(id),
reader_id int NOT NULL REFERENCES user(id),
borrow_date date NOT NULL,
return_date date NOT NULL,
PRIMARY KEY (book_id, reader_id, borrow_date)
);Cette table n’étant qu’une façon de traverser les modèles de données de l’application qui utilise cette base, le concepteur ne lui a pas donné de clé synthétique. Mais puisqu’il lui faut une clé primaire quand même, son concepteur a fait le choix d’une clé composite (id_livre, id_usager, date_emprunt). Pas grave, ça remplit les fonctions d’une clé primaire : chaque emprunt a sa propre clé.
NB : Il faut aussi la date d’emprunt dans la clé composite à cause de Sylvie qui emprunte L’Amant de la Chine du Nord au moins trois fois par an. Sans cette date, chacun des emprunts de Sylvie aurait la même clé primaire.
Où est l’os ?
Jusque là, ça semble être plutôt une bonne idée, d’économiser une clé synthétique pour une table qui n’a pas d’intérêt propre. Mais la bibliothèque recontacte le concepteur quelques mois plus tard car on constate que l’état des livres se dégrade progressivement et il n’existe aucun indicateur permettant d’identifier les ignobles coupables parmi les usagers.
Le concepteur prévoit alors un système de rapport optionnel au retour des livres : l’employé qui récupère les livres les vérifie brièvement et peut, à sa discrétion, remplir un rapport lié à un emprunt de livre, détaillant les dégradations constatées. La table intermédiaire emprunt prend du galon, elle devient une source de données dont l’intérêt n’est plus seulement de faire correspondre des éléments de droite et de gauche mais bien d’identifier chaque emprunt de façon formelle pour le lier à un éventuel rapport de détérioration.
On pourrait lier le rapport de détérioration directement à la table livre, mais il faudrait alors renseigner dans le rapport la date de constat de la détérioration pour mesurer l’évolution de l’état du livre, ce qui fait clairement doublon avec la date de retour d’un emprunt. Et pour retrouver le coupable, il faudrait croiser les données avec la table emprunt, en espérant qu’il n’y a pas eu d’erreur de saisie d’un côté ou de l’autre au niveau de ces dates.
On pourrait lier le rapport au coupable et au livre, avec 2 clés étrangères, mais on ouvre alors la porte aux incohérences. Un rapport pourrait désigner Boris comme le sauvage qui a déchiré 4 pages aux Misérables, alors que la table emprunt affirme qu’il ne l’a jamais emprunté. Et aucun mécanisme natif ne pourrait assurer ce contrôle de cohérence.
Non, il faut s’y résoudre, la table rapport doit être liée à la table, plus si intermédiaire que ça, emprunt. Et la clé étrangère va donc être une vilaine clé composite, dont une partie n’est même pas une clé synthétique à l’origine mais une date, susceptible d’être modifiée dans le cas d’une correction d’erreur (bug logiciel, rattrapage de saisies manuelles etc.). Comme disait Jean-Paul Sartre, “l’enfer, c’est les autres”, mais les clés étrangères composites sont pas loin derrière.
Et dans les cas non-capillotractés ?
Le cas simple d’une table intermédiaire vraiment destinée à ne rester qu’un moyen de traverser des modèles de données ? S’il n’y a pas de clé étrangère vers cette table, il n’y a pas de problème, non ? Je conclurai sur ce point en disant que ce n’est que mon expérience mais un client finit toujours par avoir un besoin incongru que personne d’autre n’avait prévu et qui remet en question tout ce qu’on a déjà établi. Alors autant la jouer safe dès le début et utiliser une colonne identité partout, tout le temps, peu importe la taille ou l’utilité apparente de la table. Vous n’êtes pas à un centime près, si ?
