Clés de substitution ou clés naturelles/professionnelles

Question

Nous y revoilà, le vieil argument est toujours d'actualité...

Serait-il préférable d'avoir une clé de gestion comme clé primaire ou plutôt un identifiant de substitution (c'est-à-dire une identité SQL Server) avec une contrainte unique sur le champ de la clé de gestion ?

Veuillez fournir des exemples ou des preuves à l'appui de votre théorie.

Answer 1

Je déteste les clés de substitution en général. Elles ne devraient être utilisées que lorsqu'il n'y a pas de clé naturelle de qualité disponible. Il est plutôt absurde, quand on y réfléchit, de penser que l'ajout de données sans signification à votre tableau pourrait améliorer les choses.

Voici mes raisons :

1. Lors de l'utilisation de clés naturelles, les tables sont regroupées de la manière dont elles sont le plus souvent recherchées, ce qui rend les requêtes plus rapides.

2. Lorsque vous utilisez des clés de substitution, vous devez ajouter des index uniques sur les colonnes de la clé logique. Vous devez toujours empêcher la duplication des données logiques. Par exemple, vous ne pouvez pas autoriser deux organisations portant le même nom dans votre table Organisation, même si la colonne pk est une colonne d'identification de substitution.

3. Lorsque des clés de substitution sont utilisées comme clés primaires, la nature des clés primaires naturelles est beaucoup moins claire. Lors du développement, vous souhaitez savoir quel ensemble de colonnes rend la table unique.

4. Dans les chaînes de relations individuelles, les chaînes de clés logiques. Par exemple, les organisations ont de nombreux comptes et les comptes ont de nombreuses factures. La clé logique de l'organisation est donc OrgName. La clé logique des comptes est OrgName, AccountID. La clé logique de la facture est OrgName, AccountID, InvoiceNumber.

   Lorsque des clés de substitution sont utilisées, les chaînes de clés sont tronquées en n'ayant qu'une clé étrangère pour le parent immédiat. Par exemple, la table Invoice n'a pas de colonne OrgName. Elle ne possède qu'une colonne pour l'AccountID. Si vous souhaitez rechercher des factures pour une organisation donnée, vous devez joindre les tables Organisation, Compte et Facture. Si vous utilisez des clés logiques, vous pouvez interroger directement la table Organisation.

5. L'enregistrement des valeurs des clés de substitution dans les tables de recherche a pour effet de remplir les tables avec des nombres entiers sans signification. Pour visualiser les données, il faut créer des vues complexes qui s'associent à toutes les tables de consultation. Une table de consultation est destinée à contenir un ensemble de valeurs acceptables pour une colonne. Elle ne doit pas être codifiée en stockant à la place une clé de substitution entière. Rien dans les règles de normalisation n'indique qu'il faille stocker un entier de substitution au lieu de la valeur elle-même.

6. J'ai trois livres de base de données différents. Aucun d'entre eux ne mentionne l'utilisation de clés de substitution.

Answer 2

Je souhaite partager mon expérience avec vous sur cette guerre sans fin :D sur le dilemme clé naturel vs mère porteuse. Je pense que ambos les clés de substitution (clés artificielles générées automatiquement) et les clés naturelles (composées de colonnes ayant une signification dans le domaine) ont pros y contre . En fonction de votre situation, il peut donc être plus pertinent de choisir l'une ou l'autre méthode.

Comme il semble que de nombreuses personnes présentent les clés de substitution comme la solution presque parfaite et les clés naturelles comme le fléau, je me concentrerai sur les arguments de l'autre point de vue :

Inconvénients des clés de substitution

Les clés de substitution sont les suivantes :

1. Source des problèmes de performance :
   • Ils sont généralement mis en œuvre à l'aide de colonnes auto-incrémentées, ce qui signifie :
     • Un aller-retour vers la base de données à chaque fois que vous voulez obtenir une nouvelle Id (je sais que cela peut être amélioré en utilisant la mise en cache ou des algorithmes similaires à [seq]hilo, mais ces méthodes ont leurs propres inconvénients).
     • Si un jour vous devez déplacer vos données d'un schéma à l'autre (cela arrive assez régulièrement dans mon entreprise), vous risquez de rencontrer des problèmes de collision Id. Oui, je sais qu'il est possible d'utiliser des UUID, mais ceux-ci nécessitent 32 chiffres hexadécimaux ! (Si vous vous souciez de la taille de la base de données, cela peut être un problème).
     • Si vous utilisez une seule séquence pour toutes vos clés de substitution, il est certain que votre base de données connaîtra des problèmes de contention.
2. Sujette aux erreurs. Une séquence a une limite max_value. En tant que développeur, vous devez donc prêter attention aux points suivants :
   • Vous devez effectuer un cycle dans votre séquence (lorsque la valeur maximale est atteinte, elle revient à 1, 2,...).
   • Si vous utilisez la séquence comme ordre (dans le temps) de vos données, vous devez gérer le cas du cyclage (la colonne avec Id 1 peut être plus récente que la ligne avec Id valeur max - 1).
   • Assurez-vous que votre code (et même vos interfaces clients, ce qui ne devrait pas se produire puisqu'il s'agit d'un Id interne) supporte les entiers 32b/64b que vous avez utilisés pour stocker vos valeurs de séquence.
3. Ils ne garantissent pas la non duplication des données. Vous pouvez toujours avoir deux lignes avec les mêmes valeurs de colonne mais avec une valeur générée différente. Pour moi, c'est LE problème des clés de substitution du point de vue de la conception d'une base de données.
4. Plus dans Wikipedia...

Mythes sur les clés naturelles

1. Les clés composées sont moins inefficaces que les clés de substitution. Non ! Cela dépend du moteur de base de données utilisé :
   • Oracle
   • MySQL
2. Les clés naturelles n'existent pas dans la vie réelle. Désolé, mais elles existent ! Dans l'industrie aéronautique, par exemple, le tuple suivant sera toujours unique pour une donnée prévu vol (compagnie aérienne, date de départ, numéro de vol, suffixe opérationnel). Plus généralement, lorsqu'un ensemble de données commerciales est garanti comme étant unique par un standard alors cet ensemble de données est un [bon] candidat à la clé naturelle.
3. Les clés naturelles "polluent le schéma" des tables filles. Pour moi, il s'agit plus d'un sentiment que d'un réel problème. Avoir une clé primaire de 4 colonnes de 2 octets chacune pourrait être plus efficace qu'une seule colonne de 11 octets. De plus, les 4 colonnes peuvent être utilisées pour interroger la table enfant directement (en utilisant les 4 colonnes dans une clause where) sans faire de jointure avec la table parent.

Conclusion

Utilisez des clés naturelles lorsqu'il est pertinent de le faire et utilisez des clés de substitution lorsqu'il est préférable de le faire.

J'espère que cela a aidé quelqu'un !

Answer 3

Utilisez toujours une clé qui n'a pas de signification professionnelle. Ce n'est qu'une bonne pratique.

EDIT : J'ai essayé de trouver un lien en ligne, mais je n'y suis pas parvenu. Cependant, dans Modèles d'architecture d'entreprise [Fowler] explique bien pourquoi il ne faut pas utiliser autre chose qu'une clé qui n'a d'autre signification que celle d'être une clé. Cela se résume au fait qu'elle doit avoir une fonction et une seule.

Answer 4

Les clés de substitution sont très pratiques si vous envisagez d'utiliser un outil ORM pour gérer/générer vos classes de données. Bien que vous puissiez utiliser des clés composites avec certains des mappers les plus avancés (lire : hibernate), cela ajoute une certaine complexité à votre code.

(Bien entendu, les puristes des bases de données diront que la notion même de clé de substitution est une abomination).

Je suis partisan de l'utilisation d'identifiants comme clés de substitution lorsque cela est possible. Vous pouvez par exemple créer une instance d'une classe dont l'ID est déjà défini et garanti unique, alors qu'avec, par exemple, une clé entière, vous devrez choisir par défaut 0 ou -1 et mettre à jour avec une valeur appropriée lorsque vous sauvegardez/mettez à jour.

Les UID sont toutefois pénalisés en termes de vitesse de recherche et de jointure, et leur intérêt dépend donc de l'application en question.

Answer 5

L'utilisation d'une clé de substitution est préférable à mon avis, car il n'y a aucun risque qu'elle change. Presque tout ce à quoi je peux penser et que vous pourriez utiliser comme clé naturelle pourrait changer (clause de non-responsabilité : ce n'est pas toujours vrai, mais c'est souvent le cas).

Un exemple pourrait être une DB de voitures - à première vue, on pourrait penser que la plaque d'immatriculation pourrait être utilisée comme clé. Mais comme ces plaques peuvent être modifiées, ce serait une mauvaise idée. Vous ne voudriez pas vraiment le découvrir après en libérant l'application, lorsque quelqu'un vient vous demander pourquoi il ne peut pas changer sa plaque d'immatriculation pour sa nouvelle plaque personnalisée.