Caveat : Ce billet comporte une haute dose d’informatique non pratique mais, malgré les apparences, appliquée plus que théorique.

Clé primaire : késaco ?

Une « clé primaire » est ce qui identifie de manière unique une ligne (un enregistrement) dans une table de base de données (et de simples feuilles Excel ou papier peuvent être considérées comme des tables de base de données ; Oracle c’est pareil en plus lourd et plus gros).

Cette clé primaire n’est pas forcément obligatoire dans une table, cela dépend de ce que l’on en fait. Mais dès qu’il y a besoin de « joindre » deux tables (une liste de commandes ou de factures, avec une liste des clients par exemple), le besoin apparaît. Sur chaque facture ou commande, il faut pouvoir repérer le client pour aller récupérer son adresse par exemple. Pour chaque client il faut pouvoir repérer toutes les factures qui le concernent.

Le système le plus basique se contente de recopier toutes les informations demandées au client sur chaque facture à partir d’une précédente, ce qui devient très vite ingérable ; et même avant la création de l’informatique tout le monde avait son « fichier client » où chacun était identifié, avec nom, adresse(s), téléphone, etc.

La question devient alors est « quelles informations mets-je dans la commande ou la facture pour pouvoir retrouver la fiche client à coup sûr ? ». Cette information doit être unique (il ne faut pas confondre deux clients), et absolument présente (sinon on ne peut identifier le client et on ne peut relier une facture à un payeur !).

Présence et unicité : ce sont les caractéristiques d’une « clé primaire ». Dans notre exemple, c’est la clé primaire de la table des clients.

Et les mêmes informations dans la facture sont une « clé étrangère » ; il peut y avoir d’autres clés étrangères, par exemple la liste des articles vendus qui pointe vers une table des articles, etc.

Donc sur chaque « fiche client » (chaque fiche papier de mon garagiste, chaque ligne de l’onglet « Clients » d’un classeur Excel, ou chaque ligne de la table KNA1 de SAP R/3) existe cette « clé primaire », plus ou moins formalisée, plus ou moins cohérente.

Une clé peut porter sur plusieurs informations : elle peut se résumer à un « numéro » client (numéro FR67-002564), un identifiant arbitraire (identifiant 45896542), ou être composée de plusieurs « champs » : nom client + prénom + code postal + pays + adresse + téléphone + date de naissance.

Deux formes

Concrètement, cette clé peut prendre deux formes :

  • La « clé naturelle » (en anglais : natural key, ou meaninfull key) est la plus intuitive : pour un client, si je prenais son état-civil ? Plus son adresse pour éviter de le confondre.

    Plus intelligemment un identifiant alphanumérique, inventé ou existant préalablement. C’est votre numéro client auprès d’une entreprise, ou votre numéro de Sécurité Sociale.

    La clé naturelle a un sens, elle est visible, parfois compréhensible.
  • La « clé de substitution » ou « clé technique »  ou « clé arbitraire » (surrogate key ou meaningless key) est masquée et arbitraire, c’est en général un numéro attribué par la machine à votre ligne dans la table des clients, aléatoirement ou séquentiellement.

    Elle ne sortira pas de la machine. Elle n’a aucune signification en dehors des tables et des liens qu’elle permet de lier entre elles.

Entre ces deux formes de clés, le débat fait rage depuis longtemps entre informaticiens, développeurs et administrateurs de bases de données, théoriciens et blogueurs, et constitue une des nombreuses guerres de religion informatiques. Des gens très sensés figurent des deux côtés, même si la clé arbitraire a tendance à l’emporter.

Dans ce qui suit, je me concentrerai d’abord sur des applications « classiques »  genre ERP (beaucoup de petites transactions simultanées) et je terminerai par un mot sur d’autres contextes.

Exemples réels

Les deux principaux ERP de la planète (SAP R/3 et Oracle Applications) ont chacun opéré un choix différent. J’avais décrit ici en détail comment les choses se passent et pourquoi je préfère les clés de substitution comme dans Oracle.

Oracle choisit l’identifiant arbitraire (en gros, un compteur incrémenté). Le numéro de client officiel, visible à l’écran, n’existe que dans la table dudit client. C’est l’identifiant masqué qui est copié dans les commandes, factures... pour garder le lien avec le client.

SAP R/3 a choisi la clé fonctionnelle. Le client est identifié par son mandant (KNA1-MANDT, une sorte d’identifiant de la base utilisée) et son numéro (KNA1-KUNNR), lequel numéro est visible à l’écran. La ligne de commande est identifiée par le mandant, le numéro de commande et le numéro de ligne de commande visibles sur l’écran.

SAP CRM, plus récent que R/3, est passé à des clés arbitraires.

Dans le cadre assez spécialisé de la gestion financière pour collectivité française, je pourrais aussi sortir deux exemples d’applications basées l’une sur les clés arbitraires, l’autre sur les clés fonctionnelles.

Bref, on trouve les deux cas dans la vie réelle.

Exemples concrets pour le développeur

Essayons d’afficher en SQL clients, lignes, échéances, articles d’une commande dans deux cas simplifiés. (Des exemples réels de SAP ou Oracle Applications seraient trop lourds, en anglais ou allemand, et je n’ai plus les bases sous la main).

  • Avec une clé naturelle :

Les clés primaires sont basées sur un code entreprise (business_num) qui sépare fonctionnellement ce que les entreprises peuvent vendre ou acheter, et un code finissant par _num, visible par l’utilisateur (convention propre à l’exemple).

SELECT COMMANDES.business_num, CLIENTS.nom, CLIENTS.adresse, COMMANDES.client_num,
COMMANDES.commande_num,
LIGNES_COMMANDES.ligne_num, LIGNES_COMMANDES.article_num, ARTICLES.description,
ECHEANCES.echeance_num, ECHEANCES.date_livraison, ECHEANCES.quantite
FROM (tables)
CLIENTS, COMMANDES, LIGNES_COMMANDE, ARTICLES

WHERE (jointures)
COMMANDES.client_num = CLIENTS.client_num
AND COMMANDES.business_num = CLIENT.business_num

AND LIGNES_COMMANDES.commande_num = COMMANDES.commande_num
AND LIGNES_COMMANDES.business_num = COMMANDES.business_num

AND ARTICLES.business_num = LIGNES_COMMANDES.business_num
AND ARTICLES.article_num = LIGNES_COMMANDES.article_num

AND ECHEANCES.business_num = LIGNES_COMMANDES.business_num
AND ECHEANCES.commande_num = LIGNES_COMMANDES.commande_num
AND ECHEANCES.ligne_num = LIGNES_COMMANDES.ligne_num

AND ... (critères de filtrage divers) ;

  • Avec une clé arbitraire :

Chaque table a un seul identifiant qui par convention ici se termine par _id. Les _num ici ne sont que les champs visibles à l’écran, ils ne font pas partie de la clé primaire (même si des contraintes d’unicité sont en général placées dessus).

SELECT BUSINESS.business_code, CLIENTS.nom, CLIENTS.adresse, COMMANDES.client_num,
COMMANDES.commande_num,
LIGNES_COMMANDES.ligne_num, LIGNES_COMMANDES.article_num, ARTICLES.description,
ECHEANCES.echeance_num, ECHEANCES.date_livraison, ECHEANCES.quantite
FROM (tables)
BUSINESS, CLIENTS, COMMANDES, LIGNES_COMMANDE, ARTICLES

WHERE (jointures)
AND BUSINESS.business_id = COMMANDES.business_id
AND COMMANDES.client_id = CLIENTS.client_id

AND LIGNES_COMMANDES.commande_id = COMMANDES.commande_id

AND ARTICLES.article_id = LIGNES_COMMANDES.article_id

AND ECHEANCES.ligne_id = LIGNES_COMMANDES.ligne_id

AND ... (critères de filtrage divers) ;

Quelles différences ?

  • Les jointures sont plus courtes dans le second cas, avec un seul identifiant à joindre.
    On objectera que le business_num est imposé artificiellement dans le premier cas... mais justement, il s’agit là d’un choix fonctionnel : dans deux business différents, les commandes peuvent avoir des numéros visibles identiques, ce qui impose de rajouter le business_num dans la clé primaire. Dans le second cas, les histoires d’unicité ou pas des commandes est une autre question, gérée à part et sans influence sur la clé primaire et la requête.
  • Dans le second cas, récupérer le code du business a nécessité de joindre la table BUSINESS en plus, car on n’avait que son id sous la main.

De manière plus générale, du point de vue abstrait du DBA, le premier cas se contente d’informations déjà présentes dans les index, alors que le second implique l’accès aux données systématiquement.

La clé naturelle (ou fonctionnelle)

Si la clé naturelle n’a globalement pas ma faveur pour les raisons qui vont suivre, son utilisation ne relève tout de même pas du blasphème. Même mon gourou Tom Kyte reconnaît sa validité dans bien des cas.

Cependant :

  • D’une part, elle doit être effectivement unique, sinon les mécanismes de la base de données rejetteront impitoyablement tout doublon.
  • D’autre part elle ne doit pas changer ; sinon le code à écrire gonfle d’un coup en essayant de mettre à jour la clé primaire et tous les endroits où elle est utilisée comme clé étrangère, ie, les endroits où elle est utilisée ! Imaginez qu’un client change de numéro : il vous faut aller corriger toutes les factures depuis la nuit des temps... Dans l’exemple ci-dessus, changer le code business_num oblige à modifier les tables COMMANDES, LIGNES_COMMANDES, ECHEANCES… Cela a des impacts très lourds en terme de longueur du programme et de bugs supplémentaires (et de la pire race, puisque l’on touche à l’intégrité des données).

Le simple nom d’un client ne peut être une clé primaire, à cause de l’homonymie. Par contre, les codes standardisés comme les codes de lieux INSEE, un code pays ISO... sont plus utilisables.

J’ai cité l’exemple farfelu d’une clé composée de nom client + prénom + code postal + pays + adresse + téléphone + date de naissance. Typiquement, une telle clé se retrouve dans des applications à l’historique chargé : au début on s’est contenté de l’état civil, puis on a distingué des homonymes avec l’adresse, y compris le téléphone, et quand deux personnes du même foyer partageant le même prénom sont apparues (cas réel récent dans ma belle-famille et dans ma généalogie à une époque où cela ne générait pas de problème informatique), la date de naissance a été rajoutée.

La probabilité est faible mais non nulle que l’application explose (ou plutôt sorte un message d’erreur ou confonde les deux personnes) si deux Dominique Martin nés le même jour emménagent ensemble. On rajoutera alors le sexe…

Rendez-vous bien compte du cauchemar que chaque amélioration a été pour le mainteneur de l’application : les nouvelles informations ont dû être copiées dans les tables préexistantes ; les factures, commandes, etc. contiennent à présent l’état-civil et les coordonnées complètes de chaque client !

Exemple plus réaliste dans des bases mieux fichues, celui de données à de nombreux niveaux : continent/pays/état ou région/département/canton/ville/rue : une clé primaire naturelle sur le dernier niveau comprendra six (6) champs, ce qui est lourd. D’un côté on peut accéder directement aux villes d’un continent entier. D’un autre côté, a-t-on besoin d’utiliser toute cette hiérarchie de niveaux d’un coup dans une application de type ERP ? Enfin, si le « continent » est typiquement l’abréviation « EMEA » et que la dernière lubie des top managers est de renommer cela en « Terre du Milieu », la modification doit se faire dans toutes les tables (avec un impact désastreux sur la fragmentation des index).

C’est bien ce dernier point qui pose problème : une modification de la clé naturelle (ajout d’un champ, format de chaîne...) doit se répercuter sur toutes les tables filles du système — et même sans hiérarchies profondes, elles peuvent être nombreuses : pensez au nombre de modules qui tournent autour d’un ERP de bonne taille comme SAP, Peoplesoft, Oracle Application... Un changement devient cauchemardesque.

On pourrait penser être à l’abri avec une clé encore fonctionnelle mais plus arbitraire, comme le numéro client visible (pas forcément numérique), le code de localisation INSEE, ou un numéro de Sécurité Sociale. Ce numéro n’est effectivement pas destiné à changer.

Il faut cependant se méfier de trois choses :

  • Un numéro client a fortement tendance à prendre une signification fonctionnelle, à contenir du sens ; par exemple les clients français ont un numéro qui commence par FR ; ou bien ceux supérieurs à 10000 concernent tel gamme de produits, etc. En soi ce n’est pas un problème, mais cela peut donner aux utilisateurs l’envie de changer un jour ce numéro pour des raisons qui n’ont rien à voir avec la technique, parfois purement esthétiques ! (Et si vous êtes en SSII, le client a toujours raison !)

    À l’inverse, une clé naturelle peut interdire certains changements à cause de mauvais choix initiaux. Dans l’exemple ci-dessus, les clients sont forcément séparés par leur business_num car on avait jugé au départ que les numéros de client devaient être séparés entre deux business... permettant deux numéros identiques pour deux business, mais interdisant ainsi que les clients traitent avec deux business à la fois, sauf à se trouver en double dans la base.
  • En cas de fusion de société ou de service, il y a des chances que ce numéro de client ou commande change ou soit agrandi.
  • Comme dans l’exemple ci-dessus, ce numéro client n’est souvent unique qu’associé à un numéro de société, de business, d’organisation... ce qui donne une clé composée sur au bas mot deux champs, comme dans l’exemple ci-dessus.

Autre exemple de piège : le numéro de Sécurité Sociale, qui semble pourtant un parfait exemple de clé fonctionnelle immuable. Un numéro de Sécu peut changer (transexuels), n’existe pas pour tout le monde (enfants, étrangers...), et ne garantit pas l’unicité (cycle d’un siècle...).

Bref, une clé fonctionnelle ça se défend, SI on est certain qu’elle ne changera jamais !.

Et le concepteur du schéma de données doit se méfier : les spécifications changent quoi qu’en dise le commanditaire, et ce qui est unique et fixe un jour ne l’est plus le lendemain, forçant à des contorsions parfois immondes dans le programme...

De plus, une clé naturelle restreint naturellement le champ des valeurs à ce qui est fonctionnellement pertinent. Un code de Sécurité Sociale en clé primaire implique que vous ne pourrez pas stocker des numéros d’étrangers ou d’enfants sans magouille : le système devra créer un numéro spécial plus ou moins arbitraire. Alors que si la clé est différente et que le numéro de Sécu n’est qu’une information liée au client parmi d’autres, le champ peut être laissé vide et le reste de l’application tourner normalement (pourvu qu’il n’y ait pas un besoin réel dudit numéro). Si le besoin est réel, au moins le logiciel peut-il sauvegarder la ligne problématique, même invalide, ce qui n’est pas le cas s’il y a problème sur la clé fonctionnelle.

N’oublions pas les contraintes de performances et de place disque : une clé primaire doit être indexée par la base de données. Pas de problème si la base est petite par rapport à la machine utilisée ou les clés simples. Cependant, une grosse clé composée comme dans certains exemples ci-dessus, répliquée dans toutes les autres tables qui y font référence, peut finir par coûter très cher en espace disque et en performances dans une grosse installation. Indexer et joindre sur du VARCHAR(50), c’est un peu plus gourmand que du numérique pur.

Sur le principe, il est pour moi aberrant de répercuter les contraintes liées au format d’un numéro de facture (par exemple : « deux lettres et six chiffres », ce qui mènera à un VARCHAR(6)) à la manière dont les données sont jointes (ie les relations entre elles).

Enfin, les clés naturelles gèrent mal le problème de la suppression/recréation : la notion de « clé primaire garantissant l’identification d’une ligne » ne joue plus. Si je supprime la ligne 10 de la commande FR52695A et que je la récrée différemment ensuite, une autre table quelque part qui référençait cette commande (et qu’on a oubliée ou pas su vider) ne verra pas le changement, avec les conséquences que l’on imagine. L’interdiction complète de la réutilisation de membres de clés déjà utilisés ne passe pas forcément toujours auprès du client (et SAP autorise le gag ci-dessus, l’exemple est réel).
De même, les cas où un objet doit être créé et manipulé avant de recevoir son code définitif (numéro de facture impérativement incrémental sans trou par exemple) sont délicats à manipuler.

Je reviens sur un dernier cas où la clé fonctionnelle est pertinente : les codes réellement standardisés. Par exemple, les codes ISO pour des pays (FR pour la France...), la liste des codes des départements... Leur stabilité est garantie sur le long terme. Si un pays éclate, un autre code se crée (et il faut être conscient que le code pays dans une adresse est pour certaines régions fonction de la date… ).

La clé de substitution (ou technique)

Contrairement à la clé primaire fonctionnelle ou naturelle, une clé technique ne doit jamais être montrée à l’utilisateur final. Elle n’est même pas censée « sortir » de l’application. Elle n’a pas de sens, sa valeur n’intéresse personne, elle ne changera jamais.

À première vue, cela paraît redondant avec un numéro de commande ou de client ou de Sécu que de toute façon il faudra bien créer (et indexer, et contraindre à l’unicité...).

Cependant :

  • au contraire d’une clé primaire fonctionnelle, la valeur d’une clé technique n’est absolument pas influencée par une règle extérieure (fonctionnelle, légale, écrite ou pas) ;
  • le numéro visible n’est plus qu’une donnée à un seul endroit, et peut à présent changer au gré des caprices du commanditaire final, sans conséquence sur la structure des données : on devra peut-être agrandir le champ dans un écran et rajouter des contrôles, mais l’intégrité des données n’est pas en danger ; changer ne force pas à mettre à jour toutes les tables qui référencent les clients (et elles peuvent être nombreuses !) car ces tables (commandes, factures...) utilisent l’identifiant abstrait. Le gain en facilité de maintenance vaut bien de gaspiller quelques octets.
    Dans l’exemple ci-dessus, le changement du business_code pour faire plaisir à la lubie d’un chef ne nécessite qu’une modification dans la table BUSINESS. Une renumérotation des clients pour y rajouter le code du département se limite à CLIENTS. Renuméroter les numéros de lignes de commandes pour qu’ils se suivent ne pose plus de problème de cohérence. Etc.

Les requêtes créées sont également nettement plus simples à écrire par le développeur, il n’y a jamais qu’un champ à manier dans la clé primaire. On réduit le risque d’erreur (produit cartésien ou index ignoré par oubli d’une colonne de la clé, mauvais transtypage si on mélange les types de données dans une même clé...).

La clé technique a l’inconvénient du manque de lisibilité immédiate : le développeur lira 123456 alors que l’utilisateur parle de la commande FR52695A. Cela peut freiner d’un rien le débogage.

Cela peut rebuter aussi le non-informaticien volontaire qui bricole une base dans Access ; mais comme dit Lee Richardson, un utilisateur n’a pas à fouiller dans la base de données plus qu’un passager ne bricole un moteur d’avion, et celui qui le fait doit apprendre à le faire comme un pro.

Une autre objection est la nécessité de joindre plus de tables qu’il ne serait nécessaire avec une clé fonctionnelle : si la clé naturelle comprend le numéro de commande, on trouvera ce numéro dans la table des commandes, mais aussi dans la table des lignes de commande. En consultant cette dernière, il n’y aura pas besoin de remonter jusqu’à la commande pour récupérer son numéro (ou tout autre élément de la clé). Cependant, il est rare que l’on ait besoin uniquement de la clé pour ne rien en faire par la suite, les jointures lignes-détails sont en fait quasi-systématiques.

Ce problème de jointures inutiles avec les clés techniques peut courir sur plusieurs niveaux de tables, comme fait remarquer un certain Jonathan Lewis. Dans le cas où chaque niveau est en pratique souvent du 1-1 (une commande a une ligne qui a une ligne d’échéance qui a un lot), on se retrouve à joindre quatre tables ou plus sans gain réel.

Remarquons cependant que si le problème de performances est réel, rajouter une colonne indexée sur les tables inférieures (du genre commande_id au niveau des échéances) pour accélérer certaines requêtes est encore possible, la dénormalisation limitée étant acceptable au prix parfois d’un peu de maintenance (quand un lot est déplacé d’une commande à l’autre par exemple), bien inférieur à celui d’une clé fonctionnelle.

Certaines objections de DBA aux clés techniques portent sur des considérations assez ésotériques sur les index, et en fait dépendantes des implémentations actuelles des bases de données actuelles, Oracle en premier lieu. Mais de nos jours, le temps de développement ou de correction est plus élevé que celui du disque ou du processeur (ceci sans vouloir encourager le gaspillage de ressources à la Microsoft ou Business Objects), et les problèmes de performance sont de loin plus causés par des requêtes mal écrites que par des limitations de l’optimiseur des bases. Bref, premature optimization is the root of all evil comme disait Knuth.[1]

En place disque, la clé technique « coûte » apparemment un index en plus (c’est une clé primaire, il faut l’indexer, et les autres informations comme les numéros de client ou de commande visibles devront être indexées aussi pour faire des recherches). Mais cet index porte sur une colonne, au contraire des clés naturelles composées qui doivent être reprises intégralement dans les tables filles (voir l’exemple ci-dessus avec l’état civil complet dans la clé primaire).

Les bases de données modernes font des miracles, donc je ne m’aventurerais pas à porter des jugements sur les performances entre les deux types de clés dans des cas non dégénérés ; les erreurs d’implémentation en causent bien plus. J’ai juste remarqué que sur les tables d’Oracle Applications les index sur un peu toutes les colonnes étaient légions, et en ajouter était assez bien accepté par le DBA, alors que sous SAP les index semblaient des objets coûteux et dangereux à mettre en place. Question de mentalité ? J’ai aussi constaté que les « requêtes SQL de la mort » de trois pages étaient bien plus simples à écrire avec Oracle Applications, où tout est identifiant numérique simple, que sous SAP (mais cela tient aussi à la nullité de l’ABAP).

Dans l’exemple précédent de la ligne de commande supprimée et recréée, la clé de substitution permet de régler le problème : l’identifiant numérique (bêtement incrémenté ou aléatoire) est, lui, différent sur l’ancienne et la nouvelle lignes. On pourrait renuméroter toutes les commandes et leurs lignes que les programmes annexes retrouveraient leurs petits.

Accessoirement, la séquence qui génère la plupart des identifiants permet de repérer l’ordre chronologique d’insertion (c’est un effet de bord, pas une fonctionnalité !). On utilisera une séquence par table (les numéros s’y suivent alors, c’est plus lisible pour le développeur ; là encore c’est un effet de bord), et si possible en les initialisant avec des ordres de grandeur différents (organisations commençant à 10, clients à 1000, lignes de commandes à 100000...).

Exemples extrêmes de clés techniques : GUID et ROWID

La plupart des applications basées sur des clés de substitution utilisent de bêtes séquences, ie un simple numéro incrémenté à chaque nouvelle utilisation. L’identifiant numérique (visible uniquement par le développeur rappelons-le) reste souvent « manipulable » (la ligne de commande 1999555 est liée à la commande 125666 pour le client 8836).

J’ai vu sous SAP CRM des identifiants sous forme d’identifiant global (GUID, ou UUID si non lié à la machine), une suite hexadécimale de 32, 64, ou 128 bits, du genre de 42d38912-3c32-4ca1-965c-09edb910eb18. Pour le développeur c’est un cauchemar, les chaînes sont trop longues pour être lisibles, si même elles tiennent à l’écran (et les outils de développement ABAP précambriens n’arrangeaient rien…).

Pourtant, les identifiants ne servent pas qu’à la base, mais aussi au développeur ou au support, et infliger un tel boulet à ce dernier, c’est mettre en danger la réactivité et donc la vie même de l’application (mais les managers qui sous-traitent tout en Inde imaginent que le problème disparaît automagiquement quand on considère le développeur comme une sorte de programme impersonnel).

Des détails et une discussion sont disponibles par exemple ici : http://www.codinghorror.com/blog/archives/000817.html. Il y a des utilisations justifiées (notamment pour des fusions de bases de données, ou la communication hors de la base avec des identifiants générés hors de celle-ci, et encore, il y a des moyens plus conviviaux pour générer ces clés) mais franchement, pour la plupart des applications, l’identifiant global est une bombe thermonucléaire pour écraser une mouche, voire un problème de performance potentiel (fragmentation d’index si les clés ne sont pas séquentielles, place en disque et mémoire d’une clé sur 16 octets au lieu de 4 ou 5...), voire pire (l’unicité est très probablement garantie, pas absolument comme avec une séquence classique). Il n’y a pas besoin d’unicité au-delà de la table pour une clé primaire !

Plus grave car source inévitable de problèmes, il ne faut pas utiliser un identifiant a priori unique, le ROWID d’Oracle, que possède chaque ligne de cette base de données. Oui, il est bien unique (c’est ce dont se sert Oracle pour retrouver une ligne sur le disque dur !) mais... il change dès que le DBA décide d’une réorganisation physique, voire avant. La seule utilisation acceptable est transitoire (au sein d’une requête pour dédoublonner par exemple).

Clés naturelles, ETL & datawarehouse

Les exemples ci-dessus se basent sur des programmes de type ERP, des applications de production où les données sont lues par petits paquets, modifiées en permanence, où les contraintes principales sont le nombre de transactions par seconde que peut encaisser le système et l’intégrité des données.

Dans un datawarehouse (entrepôt de données), les contraintes sont différentes. Ces bases de données consolident, agrègent, croisent... ce qui vient de différentes applications « de production ». Les contraintes de performance se situent principalement au niveau de la lecture de gros volumes croisés dans tous les sens, par exemple des statistiques sur toutes les ventes des trois dernières années avec de savants calculs par géographie et classe d’âge des clients (alors que dans l’ERP on se préoccupait essentiellement des commandes encore ouvertes, en affichant surtout des listes).

Pour améliorer les performances pour de gros volumes, les tables d’un datawarehouse sont totalement dénormalisées (les données se répètent), et indexées à mort. Les mises à jour se font souvent ponctuellement par batch nocturne, en masse. Des outils dédiés (les ETL) sont utilisés. L’exploitation s’effectue via des outils spécialisés comme Business Objects qui écrivent les requêtes, ce qui limite l’erreur humaine.

Les contraintes et outils rendent caduques les raisons qui font le succès de la clé primaire technique sur une application de production. Les datawarehouses notamment ne manipulent pas réellement leurs données, ils se synchronisent brutalement avec les bases de production, on peut presque les considérer comme un cache des grosses requêtes statistiques que l’on effectuait autrefois sur la base source.

De plus et surtout, les données à croiser proviennent de nombreuses applications différentes. Celles-ci ne partagent pas les clés techniques qu’elles peuvent utiliser en interne : chaque outil a ses identifiants, l’ID_ORDER d’un logiciel n’est pas le même que le COMMANDE_ID du logiciel d’à côté. Partager ces clés techniques entre programmes n’aurait pas grand sens, les problèmes de synchronisation propres aux clés naturelles réapparaissant, à moins d’avoir la main sur toutes les applications en les considérant comme une seule et unique.

Même si en source on utilise des logiciels utilisant les clés techniques, on en arrive donc au concept de clé « alternative », c’est-à-dire la clé qui est celle intuitive pour un humain : un numéro de commande, un numéro de client, un état civil, un numéro de Sécurité Sociale... avec les mêmes contraintes de présence et d’unicité réelle que l’on a pu évoquer plus haut pour les clés fonctionnelles, et que la clé technique arbitraire avait permis d’éviter.

C’est cette clé alternative qu’un datawarehouse utilisera comme clé pour ses tables. (Mise à jour : En fait il y a deux écoles. Mon maître à penser penche pour les clés fonctionnelles ; d’autres veulent recréer des clés techniques au sein même du datawarehouse car cela est nécessaire dans certains cas.) Elle existe d’ailleurs souvent déjà dans le système de production en plus de la clé technique, sous la forme d’un index supplémentaire. (Mise à jour : En fait, elle devrait quasiment toujours être là.) Au moins les bugs générés par des problèmes de clé mal gérée sur le datawarehouse sont-ils beaucoup moins graves puisque normalement on raisonne au niveau des statistiques, et pas sur un système de production destiné au fonctionnement quotidien de l’entreprise — en théorie...

Bibliographie Web succinte

Chez Robert Vollman (un DBA qui s’interroge) :
Natural vs Synthetic keys

Chez Jonathan Lewis (un pro-clés naturelles pour des raisons très, trop techniques) :
Meaningless keys

Chez Lee Richardson : Surrogate vs Natural Primary Keys – Data Modeling Mistake 2 of 10 (sur les pièges dans la création de clé primaire, et une bonne petite synthèse sur le débat surrogate/natural).

Notes

[1] Oui, ceci est un argument d’autorité.