Clé de substitution - Surrogate key

Une clé de substitution (ou clé synthétique , pseudokey , identificateur d'entité , clé factless ou clé technique ) dans une base de données est un identificateur unique pour une ou l' autre d' une entité dans le monde modélisé ou un objet dans la base de données. La clé de substitution n'est pas dérivée des données d'application, contrairement à une clé naturelle (ou métier ) .

Définition

Il existe au moins deux définitions d'une mère porteuse :

Substitut (1) - Hall, Owlett et Todd (1976)
Un substitut représente une entité dans le monde extérieur. Le substitut est généré en interne par le système mais est néanmoins visible pour l'utilisateur ou l'application.
Substitut (2) - Wieringa et De Jonge (1991)
Un substitut représente un objet dans la base de données elle-même. Le substitut est généré en interne par le système et est invisible pour l'utilisateur ou l'application.

La définition de substitution (1) se rapporte à un modèle de données plutôt qu'à un modèle de stockage et est utilisée tout au long de cet article. Voir Date (1998).

Une distinction importante entre une clé de substitution et une clé primaire dépend du fait que la base de données est une base de données actuelle ou une base de données temporelle . Étant donné qu'une base de données actuelle ne stocke que des données actuellement valides, il existe une correspondance un à un entre un substitut dans le monde modélisé et la clé primaire de la base de données. Dans ce cas, le substitut peut être utilisé comme clé primaire, ce qui donne le terme clé de substitution . Dans une base de données temporelle, cependant, il existe une relation plusieurs-à-un entre les clés primaires et le substitut. Puisqu'il peut y avoir plusieurs objets dans la base de données correspondant à un seul substitut, nous ne pouvons pas utiliser le substitut comme clé primaire ; un autre attribut est requis, en plus du substitut, pour identifier de manière unique chaque objet.

Bien que Hall et al. (1976) ne disent rien à ce sujet, d'autres ont soutenu qu'une mère porteuse devrait avoir les caractéristiques suivantes :

  • la valeur est unique à l'échelle du système, donc jamais réutilisée
  • la valeur est générée par le système
  • la valeur n'est pas manipulable par l'utilisateur ou l'application
  • la valeur ne contient aucune signification sémantique
  • la valeur n'est pas visible pour l'utilisateur ou l'application
  • la valeur n'est pas composée de plusieurs valeurs de domaines différents.

Les mères porteuses en pratique

Dans une base de données actuelle , la clé de substitution peut être la clé primaire , générée par le système de gestion de base de données et non dérivée des données d'application de la base de données. La seule signification de la clé de substitution est d'agir en tant que clé primaire. Il est également possible que la clé de substitution existe en plus de l' UUID généré par la base de données (par exemple, un numéro HR pour chaque employé autre que l'UUID de chaque employé).

Une clé de substitution est fréquemment un numéro séquentiel (par exemple une "colonne d'identité" Sybase ou SQL Server , PostgreSQL ou Informix serial , Oracle ou SQL Server SEQUENCE ou une colonne définie avec AUTO_INCREMENTdans MySQL ). Certaines bases de données fournissent UUID / GUID comme type de données possible pour les clés de substitution (par exemple PostgreSQLUUID ou SQL ServerUNIQUEIDENTIFIER ).

Le fait d'avoir la clé indépendante de toutes les autres colonnes isole les relations de la base de données des modifications des valeurs des données ou de la conception de la base de données (rendant la base de données plus agile ) et garantit l'unicité.

Dans une base de données temporelle , il faut faire la distinction entre la clé de substitution et la clé métier . Chaque ligne aurait à la fois une clé métier et une clé de substitution. La clé de substitution identifie une ligne unique dans la base de données, la clé métier identifie une entité unique du monde modélisé. Une ligne du tableau représente une tranche de temps contenant tous les attributs de l'entité pendant une période définie. Ces tranches représentent toute la durée de vie d'une entité commerciale. Par exemple, une table EmployeeContracts peut contenir des informations temporelles pour suivre les heures de travail contractuelles. La clé commerciale d'un contrat sera identique (non unique) dans les deux lignes, mais la clé de substitution pour chaque ligne est unique.

Clé de substitution Clé d'entreprise Nom de l'employé Heures de travail par semaine LigneValideDe LigneValideVers
1 BOS0120 John Smith 40 2000-01-01 2000-12-31
56 P0000123 Bob Brown 25 1999-01-01 2011-12-31
234 BOS0120 John Smith 35 2001-01-01 2009-12-31

Certains concepteurs de bases de données utilisent systématiquement des clés de substitution indépendamment de l'adéquation des autres clés candidates , tandis que d'autres utiliseront une clé déjà présente dans les données, s'il y en a une.

Certains des noms alternatifs ("clé générée par le système") décrivent la manière de générer de nouvelles valeurs de substitution plutôt que la nature du concept de substitution.

Les approches pour générer des mères porteuses comprennent :

Avantages

Stabilité

Les clés de substitution ne changent généralement pas tant que la ligne existe. Cela présente les avantages suivants :

  • Les applications ne peuvent pas perdre leur référence à une ligne de la base de données (puisque l'identifiant ne change pas).
  • Les données de clé primaire ou naturelle peuvent toujours être modifiées, même avec des bases de données qui ne prennent pas en charge les mises à jour en cascade sur les clés étrangères associées .

Modifications des exigences

Les attributs qui identifient de manière unique une entité peuvent changer, ce qui peut invalider l'adéquation des clés naturelles. Considérez l'exemple suivant :

Le nom d'utilisateur du réseau d'un employé est choisi comme clé naturelle. Lors de la fusion avec une autre entreprise, de nouveaux employés doivent être insérés. Certains des nouveaux noms d'utilisateur du réseau créent des conflits car leurs noms d'utilisateur ont été générés indépendamment (lorsque les sociétés étaient séparées).

Dans ces cas, généralement un nouvel attribut doit être ajouté à la clé naturelle (par exemple, une colonne original_company ). Avec une clé de substitution, seule la table qui définit la clé de substitution doit être modifiée. Avec les clés naturelles, toutes les tables (et éventuellement d'autres logiciels connexes) qui utilisent la clé naturelle devront changer.

Certains domaines problématiques n'identifient pas clairement une clé naturelle appropriée. Les clés de substitution évitent de choisir une clé naturelle qui pourrait être incorrecte.

Performance

Les clés de substitution ont tendance à être un type de données compact, tel qu'un entier de quatre octets. Cela permet à la base de données d'interroger la colonne clé unique plus rapidement que plusieurs colonnes. De plus, une distribution non redondante des clés entraîne un équilibrage complet de l'index b-tree résultant . Les clés de substitution sont également moins coûteuses à joindre (moins de colonnes à comparer) que les clés composées .

Compatibilité

Lors de l'utilisation de plusieurs systèmes de développement d'applications de base de données, de pilotes et de systèmes de mappage objet-relationnel , tels que Ruby on Rails ou Hibernate , il est beaucoup plus facile d'utiliser un nombre entier ou des clés de substitution GUID pour chaque table au lieu de clés naturelles afin de prendre en charge la base de données. opérations indépendantes du système et mappage objet-ligne.

Uniformité

Lorsque chaque table a une clé de substitution uniforme, certaines tâches peuvent être facilement automatisées en écrivant le code d'une manière indépendante de la table.

Validation

Il est possible de concevoir des valeurs-clés qui suivent un modèle ou une structure bien connus qui peuvent être vérifiés automatiquement. Par exemple, les clés qui sont destinées à être utilisées dans une colonne d'une table peuvent être conçues pour « avoir une apparence différente » de celles qui sont destinées à être utilisées dans une autre colonne ou table, simplifiant ainsi la détection des erreurs d'application dans lesquelles les clés ont été égarés. Cependant, cette caractéristique des clés de substitution ne doit jamais être utilisée pour piloter la logique des applications elles-mêmes, car cela violerait les principes de la normalisation de la base de données .

Désavantages

Dissociation

Les valeurs des clés de substitution générées n'ont aucune relation avec la signification réelle des données détenues dans une ligne. Lors de l'inspection d'une ligne contenant une référence de clé étrangère à une autre table à l'aide d'une clé de substitution, la signification de la ligne de la clé de substitution ne peut pas être discernée de la clé elle-même. Chaque clé étrangère doit être jointe pour voir l'élément de données associé. Si des contraintes de base de données appropriées n'ont pas été définies, ou des données importées d'un système hérité où l' intégrité référentielle n'a pas été utilisée, il est possible d'avoir une valeur de clé étrangère qui ne correspond pas à une valeur de clé primaire et est donc invalide. (À cet égard, CJ Date considère l'absurdité des clés de substitution comme un avantage.)

Pour découvrir de telles erreurs, il faut effectuer une requête qui utilise une jointure externe gauche entre la table avec la clé étrangère et la table avec la clé primaire, montrant les deux champs clés en plus des champs nécessaires pour distinguer l'enregistrement ; toutes les valeurs de clé étrangère non valides auront la colonne de clé primaire comme NULL. La nécessité d'effectuer une telle vérification est si courante que Microsoft Access fournit en fait un assistant "Rechercher une requête sans correspondance" qui génère le SQL approprié après avoir guidé l'utilisateur dans une boîte de dialogue. (Cependant, il n'est pas trop difficile de composer de telles requêtes manuellement.) Les requêtes « Rechercher sans correspondance » sont généralement utilisées dans le cadre d'un processus de nettoyage des données lors de l'héritage de données héritées.

Les clés de substitution ne sont pas naturelles pour les données exportées et partagées. Une difficulté particulière est que les tables de deux schémas par ailleurs identiques (par exemple, un schéma de test et un schéma de développement) peuvent contenir des enregistrements qui sont équivalents d'un point de vue commercial, mais ont des clés différentes. Cela peut être atténué en n'exportant PAS les clés de substitution, sauf en tant que données transitoires (le plus évident, lors de l'exécution d'applications qui ont une connexion « live » à la base de données).

Lorsque les clés de substitution supplantent les clés naturelles, l' intégrité référentielle spécifique au domaine sera compromise. Par exemple, dans une table principale client, le même client peut avoir plusieurs enregistrements sous des ID client distincts, même si la clé naturelle (une combinaison du nom du client, de la date de naissance et de l'adresse e-mail) serait unique. Pour éviter toute compromission, la clé naturelle de la table ne doit PAS être supplantée : elle doit être conservée en tant que contrainte unique , qui est implémentée comme un index unique sur la combinaison de champs de clé naturelle.

Optimisation des requêtes

Les bases de données relationnelles supposent qu'un index unique est appliqué à la clé primaire d'une table. L'index unique sert à deux fins : (i) pour appliquer l'intégrité de l'entité, puisque les données de clé primaire doivent être uniques sur toutes les lignes et (ii) pour rechercher rapidement des lignes lorsqu'elles sont interrogées. Étant donné que les clés de substitution remplacent les attributs d'identification d'une table (la clé naturelle) et que les attributs d'identification sont susceptibles d'être ceux interrogés, l'optimiseur de requête est obligé d'effectuer une analyse complète de la table lorsqu'il répond aux requêtes probables. Le remède à l'analyse complète de la table est d'appliquer des index sur les attributs d'identification, ou des ensembles d'entre eux. Lorsque de tels ensembles sont eux-mêmes une clé candidate , l'index peut être un index unique.

Ces index supplémentaires, cependant, prendront de l'espace disque et ralentiront les insertions et les suppressions.

Normalisation

Les clés de substitution peuvent entraîner des valeurs en double dans toutes les clés naturelles . Pour éviter la duplication, il faut préserver le rôle des clés naturelles en tant que contraintes uniques lors de la définition de la table à l'aide de l'instruction SQL CREATE TABLE ou de l'instruction ALTER TABLE ...ADD CONSTRAINT, si les contraintes sont ajoutées après coup.

Modélisation des processus métier

Étant donné que les clés de substitution ne sont pas naturelles, des défauts peuvent apparaître lors de la modélisation des exigences métier. Les exigences métier, reposant sur la clé naturelle, doivent ensuite être traduites en clé de substitution. Une stratégie consiste à établir une distinction claire entre le modèle logique (dans lequel les clés de substitution n'apparaissent pas) et la mise en œuvre physique de ce modèle, pour s'assurer que le modèle logique est correct et raisonnablement bien normalisé, et pour s'assurer que le modèle physique est une implémentation correcte du modèle logique.

Divulgation par inadvertance

Des informations exclusives peuvent être divulguées si des clés de substitution sont générées séquentiellement. En soustrayant une clé séquentielle précédemment générée d'une clé séquentielle récemment générée, on pourrait apprendre le nombre de lignes insérées au cours de cette période. Cela pourrait exposer, par exemple, le nombre de transactions ou de nouveaux comptes par période. Voir par exemple le problème des chars allemands .

Il existe plusieurs façons de surmonter ce problème :

  • augmenter le numéro séquentiel d'un montant aléatoire ;
  • générer une clé aléatoire telle qu'un UUID .

Hypothèses involontaires

Les clés de substitution générées séquentiellement peuvent impliquer que des événements avec une valeur de clé plus élevée se sont produits après des événements avec une valeur plus faible. Ce n'est pas nécessairement vrai, car de telles valeurs ne garantissent pas la séquence temporelle car il est possible que des insertions échouent et laissent des espaces qui peuvent être comblés ultérieurement. Si la chronologie est importante, la date et l'heure doivent être enregistrées séparément.

Voir également

Les références

Citations

Sources