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

Nous y revoilà, la vieille dispute se pose encore …

Faudrait-il avoir une clé commerciale en tant que clé primaire ou préférerions-nous avoir un identifiant de substitution (c.-à-d. Une identité SQL Server) avec une contrainte unique sur le domaine des clés métier?

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

    Tous les deux. Prends ton gâteau et mange-le.

    N’oubliez pas qu’il n’y a rien de particulier à propos d’une clé primaire, sauf qu’elle est étiquetée comme telle. Ce n’est rien de plus qu’une contrainte NOT NULL UNIQUE, et une table peut en avoir plusieurs.

    Si vous utilisez une clé de substitution, vous souhaitez toujours une clé commerciale pour garantir l’unicité selon les règles métier.

    Quelques raisons pour utiliser des clés de substitution:

    1. Stabilité : Changer une clé en raison d’une entreprise ou d’un besoin naturel aura un impact négatif sur les tables associées. Les clés de substitution ont rarement, voire jamais, besoin d’être modifiées car il n’y a aucune signification liée à la valeur.

    2. Convention : vous permet d’avoir une convention de dénomination standardisée de colonne de clé primaire plutôt que d’avoir à réfléchir à la façon de joindre des tables avec différents noms pour leurs PK.

    3. Vitesse : Selon la valeur et le type de clé PK, une clé de substitution d’un entier peut être plus petite, plus rapide à indexer et à rechercher.

    Il semble que personne n’ait encore dit quoi que ce soit à l’appui des clés de non-substitution (j’hésite à dire «naturel»). Alors voilà …

    Un inconvénient des clés de substitution est qu’elles n’ont aucun sens (citées comme un avantage par certains, mais…). Cela vous oblige parfois à joindre beaucoup plus de tables à votre requête que ce qui est vraiment nécessaire. Comparer:

    select sum(t.hours) from timesheets t where t.dept_code = 'HR' and t.status = 'VALID' and t.project_code = 'MYPROJECT' and t.task = 'BUILD'; 

    contre:

     select sum(t.hours) from timesheets t join departents d on d.dept_id = t.dept_id join timesheet_statuses s on s.status_id = t.status_id join projects p on p.project_id = t.project_id join tasks k on k.task_id = t.task_id where d.dept_code = 'HR' and s.status = 'VALID' and p.project_code = 'MYPROJECT' and k.task_code = 'BUILD'; 

    À moins que quelqu’un pense sérieusement que ce qui suit est une bonne idée ?:

     select sum(t.hours) from timesheets t where t.dept_id = 34394 and t.status_id = 89 and t.project_id = 1253 and t.task_id = 77; 

    “Mais quelqu’un dira” que se passe-t-il lorsque le code pour MYPROJECT ou VALID ou HR change? ” À quoi ma réponse serait: “pourquoi auriez-vous besoin de le changer?” Ce ne sont pas des clés “naturelles” dans le sens où un corps extérieur va légiférer pour que désormais “VALID” soit recodé comme “BON”. Seul un faible pourcentage de clés “naturelles” se situe réellement dans cette catégorie – le code SSN et le code postal étant les exemples habituels. J’utiliserais certainement une clé numérique dénuée de sens pour des tableaux comme Person, Address – mais pas pour tout ce qui, pour une raison quelconque, la plupart des gens ici semblent préconiser.

    Voir aussi: ma réponse à une autre question

    La clé de substitution n’aura JAMAIS de raison de changer. Je ne peux pas en dire autant des clés naturelles. Noms de famille, e-mails, numéros ISBN – ils peuvent tous changer un jour.

    Les clés de substitution (généralement des entiers) ont la valeur ajoutée de rendre vos relations de table plus rapides et plus économiques en stockage et en vitesse de mise à jour (encore mieux, les clés étrangères ne nécessitent pas de mise à jour). cela change de temps en temps).

    La clé primaire d’une table doit être utilisée pour identifier de manière unique la ligne, principalement à des fins de jointure. Table Think a Persons: les noms peuvent changer et ils ne sont pas garantis uniques.

    Pensez aux entresockets: vous êtes une entreprise Merkin heureuse de faire affaire avec d’autres entresockets à Merkia. Vous êtes assez intelligent pour ne pas utiliser le nom de l’entreprise comme clé primaire. Vous utilisez donc l’identifiant d’entreprise unique du gouvernement de Merkia dans sa totalité de 10 caractères alphanumériques. Ensuite, Merkia change les identifiants de la société car ils pensaient que ce serait une bonne idée. C’est bien, vous utilisez la fonctionnalité de mise à jour en cascade de votre moteur de firebase database, pour un changement qui ne devrait pas vous impliquer en premier lieu. Plus tard, votre entreprise se développe et vous travaillez maintenant avec une entreprise à Freedonia. Les identifiants de la société libre sont de 16 caractères. Vous devez agrandir la clé primaire de l’identifiant de la société (également les champs de la clé étrangère dans Orders, Issues, MoneyTransfers, etc.) en ajoutant un champ Country dans la clé primaire (également dans les clés étrangères). Aie! Guerre civile à Freedonia, elle est divisée en trois pays. Le nom de pays de votre associé doit être changé pour le nouveau; mises à jour en cascade à la rescousse. BTW, quelle est votre clé primaire? (Pays, CompanyID) ou (CompanyID, Pays)? Ce dernier aide les jointures, le premier évite un autre index (ou peut-être plusieurs, si vous souhaitez que vos ordres soient regroupés par pays également).

    Tout cela n’est pas une preuve, mais une indication qu’une clé de substitution pour identifier de manière unique une ligne pour toutes les utilisations, y compris les opérations de jointure, est préférable à une clé métier.

    Je déteste les clés de substitution en général. Ils ne doivent être utilisés que lorsqu’il n’y a pas de clé naturelle de qualité disponible. Quand on y pense, il est absurde de penser qu’append des données sans signification à votre table pourrait améliorer les choses.

    Voici mes raisons:

    1. Lorsque vous utilisez des clés naturelles, les tables sont regroupées de la manière la plus courante, ce qui accélère les requêtes.

    2. Lorsque vous utilisez des clés de substitution, vous devez append des index uniques sur les colonnes de clés logiques. Vous devez toujours éviter les données en double logiques. Par exemple, vous ne pouvez pas autoriser deux organisations portant le même nom dans votre table d’organisation même si le pk est une colonne d’ID de substitution.

    3. Lorsque les clés de substitution sont utilisées comme clé primaire, les clés primaires naturelles sont beaucoup moins claires. Lorsque vous développez, vous voulez savoir quel ensemble de colonnes rend la table unique.

    4. Dans une à plusieurs chaînes de relations, les chaînes de clés logiques. Ainsi, par exemple, les organisations ont de nombreux comptes et comptes ont de nombreuses factures. La clé logique d’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 ne disposant que d’une clé étrangère au parent immédiat. Par exemple, le tableau Facture n’a pas de colonne OrgName. Il n’a qu’une colonne pour le AccountID. Si vous souhaitez rechercher des factures pour une organisation donnée, vous devez rejoindre les tables Organisation, Compte et Facture. Si vous utilisez des clés logiques, vous pouvez directement interroger la table Organisation.

    5. Le stockage des valeurs de clé de substitution des tables de recherche permet de remplir les tables avec des entiers sans signification. Pour afficher les données, des vues complexes doivent être créées pour joindre toutes les tables de recherche. Une table de consultation est destinée à contenir un ensemble de valeurs acceptables pour une colonne. Il ne devrait pas être codifié en stockant une clé de substitution entière à la place. Il n’y a rien dans les règles de normalisation qui suggère que vous devriez stocker un entier de substitution au lieu de la valeur elle-même.

    6. J’ai trois livres de firebase database différents. Aucun d’entre eux ne montre l’utilisation de clés de substitution.

    Je veux partager mon expérience avec vous sur cette guerre sans fin: D sur le dilemme clé naturel vs substitut. Je pense que les deux clés de substitution (celles générées automatiquement) et les clés naturelles (composées de colonnes avec une signification de domaine) ont des avantages et des inconvénients . Donc, selon votre situation, il peut être plus pertinent de choisir l’une ou l’autre méthode.

    Comme il semble que beaucoup de personnes présentent des clés de substitution comme solution presque parfaite et des clés naturelles comme la peste, je me concentrerai sur les arguments de l’autre sharepoint vue:

    Inconvénients des clés de substitution

    Les clés de substitution sont:

    1. Source des problèmes de performance:
      • Ils sont généralement implémentés à l’aide de colonnes auto-incrémentées, ce qui signifie:
        • Un aller-retour vers la firebase database chaque fois que vous souhaitez obtenir un nouvel identifiant (je sais que cela peut être amélioré en utilisant la mise en cache ou les algorithmes similaires, mais ces méthodes ont toujours leurs propres inconvénients).
        • Si un jour vous devez déplacer vos données d’un schéma à un autre (cela arrive assez régulièrement au moins dans mon entreprise), vous risquez de rencontrer des problèmes de collision d’identité. Et oui je sais que vous pouvez utiliser des UUID mais ceux-ci durent 32 chiffres hexadécimaux! (Si vous vous souciez de la taille de la firebase database, cela peut poser problème).
        • Si vous utilisez une séquence pour toutes vos clés de substitution, vous vous retrouverez sans aucun doute sur votre firebase database.
    2. Erreur sujette à erreur. Une séquence a une limite max_value. En tant que développeur, vous devez donc faire attention aux points suivants:
      • Vous devez cycler votre séquence (lorsque la valeur maximale est atteinte, elle retourne à 1,2, …).
      • Si vous utilisez la séquence comme un ordre (dans le temps) de vos données, vous devez gérer le cas du cycle (la colonne avec Id 1 peut être plus récente que la ligne avec Id max-value – 1).
      • Assurez-vous que votre code (et même vos interfaces clientes, ce qui ne devrait pas être un identifiant interne) supporte les entiers 32b / 64b que vous avez utilisés pour stocker vos valeurs de séquence.
    3. Ils ne garantissent pas les données non dupliquées. Vous pouvez toujours avoir 2 lignes avec toutes 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 sharepoint vue de la conception de bases de données.
    4. Plus dans Wikipedia …

    Mythes sur les clés naturelles

    1. Les clés composites sont moins inefficaces que les clés de substitution. Non! Cela dépend du moteur de firebase database utilisé:
      • Oracle
      • MySQL
    2. Les clés naturelles n’existent pas dans la vie réelle. Désolé mais ils existent! Dans l’indussortinge aéronautique, par exemple, le tuple suivant sera toujours unique pour un vol régulier donné (compagnie aérienne, départ, date de vol, numéro de vol opérationnel). Plus généralement, lorsqu’un dataset métier est garanti unique par un standard donné , cet dataset est un candidat clé naturel.
    3. Les clés naturelles “polluent le schéma” des tables enfant. Pour moi, c’est plus un sentiment qu’un vrai problème. Avoir une clé primaire de 4 colonnes de 2 octets chacune peut être plus efficace qu’une seule colonne de 11 octets. En outre, les 4 colonnes peuvent être utilisées pour interroger directement la table enfant (en utilisant les 4 colonnes d’une clause where) sans se connecter à la table parent.

    Conclusion

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

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

    Utilisez toujours une clé sans signification commerciale. C’est juste une bonne pratique.

    EDIT: J’essayais de trouver un lien en ligne, mais je ne pouvais pas. Cependant, dans «Patterns of Enterprise Archtecture» [Fowler], vous avez une bonne explication sur la raison pour laquelle vous ne devriez pas utiliser autre chose qu’une clé sans autre signification que la clé. Cela revient au fait qu’il ne devrait y avoir qu’un seul emploi et un seul emploi.

    Les clés de substitution sont très pratiques si vous prévoyez 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 mappeurs les plus avancés (lisez: hibernate), cela ajoute de la complexité à votre code.

    (Bien sûr, les puristes de bases de données soutiendront que même la notion de clé de substitution est une abomination.)

    Je suis fan de l’utilisation des uids pour les clés de substitution lorsque cela est approprié. Le principal avantage avec eux est que vous connaissez la clé à l’avance, par exemple vous pouvez créer une instance d’une classe avec l’ID déjà défini et garanti unique alors qu’avec, par exemple, une clé entière, vous devrez par défaut 0 ou – 1 et mettre à jour à une valeur appropriée lorsque vous enregistrez / mettez à jour.

    Les UID ont des pénalités en termes de recherche et de rapidité, mais cela dépend de l’application en question pour savoir s’ils sont souhaitables.

    À mon avis, utiliser une clé de substitution, car il n’ya aucune chance qu’elle change. Presque tout ce que je peux penser comme une clé naturelle pourrait changer (avertissement: pas toujours vrai, mais souvent).

    Un exemple pourrait être un DB de voitures – à première vue, vous pourriez penser que la plaque d’immasortingculation pourrait être utilisée comme clé. Mais cela pourrait être changé, ce serait une mauvaise idée. Vous ne voudriez pas vraiment découvrir cela après avoir lancé l’application, quand quelqu’un vient à vous et veut savoir pourquoi il ne peut pas changer sa plaque d’immasortingculation à sa nouvelle personnalisation shinye.

    Toujours utiliser une seule colonne, la clé de substitution si possible. Cela rend les jointures aussi bien que les insertions / mises à jour / suppressions beaucoup plus propres, car vous êtes uniquement responsable du suivi d’une seule information pour gérer l’enregistrement.

    Ensuite, emstackz vos clés métier en tant que contraintes ou index uniques. Cela vous permettra de conserver l’intégrité des données.

    La logique métier / les touches naturelles peuvent changer, mais la clé physique d’une table ne doit JAMAIS changer.

    Dans un scénario de datawarehouse, il est préférable de suivre le chemin de la clé de substitution. Deux raisons:

    • Vous êtes indépendant du système source et les modifications apscopes – telles qu’un changement de type de données – ne vous concernent pas.
    • Votre DW aura besoin de moins d’espace physique puisque vous utiliserez uniquement des types de données entiers pour vos clés de substitution. De plus, vos index fonctionneront mieux.

    Les clés de substitution peuvent être utiles lorsque les informations commerciales peuvent changer ou être identiques. Après tout, les noms commerciaux ne doivent pas nécessairement être uniques au pays. Supposons que vous traitez avec deux entresockets nommées Smith Electronics, une au Kansas et une au Michigan. Vous pouvez les distinguer par adresse, mais cela va changer. Même l’État peut changer; Et si Smith Electronics de Kansas City, Kansas, traversait la rivière pour aller à Kansas City, dans le Missouri? Il n’y a pas de moyen évident de garder ces entresockets distinctes avec des informations clés naturelles, donc une clé de substitution est très utile.

    Pensez à la clé de substitution comme un numéro ISBN. Habituellement, vous identifiez un livre par titre et par auteur. Cependant, j’ai deux livres intitulés “Pearl Harbor” par HP Willmott, et ce sont certainement des livres différents, pas seulement des éditions différentes. Dans un cas comme celui-ci, je pourrais me référer aux regards des livres, ou aux précédents par rapport aux plus récents, mais il est tout aussi important que l’ISBN soit utilisé.

    Pour rappel, il n’est pas recommandé de placer des index clusterisés sur des clés de substitution aléatoires, c’est-à-dire des GUID qui lisent XY8D7-DFD8S, car SQL Server n’est pas en mesure de sortinger physiquement ces données. Vous devez plutôt placer des index uniques sur ces données, bien qu’il soit également utile d’exécuter simplement le profileur SQL pour les opérations de la table principale, puis de placer ces données dans l’Assistant Paramétrage du moteur de firebase database.

    Voir thread @ http://social.msdn.microsoft.com/Forums/en-us/sqlgetstarted/thread/27bd9c77-ec31-44f1-ab7f-bd2cb13129be

    C’est l’un de ces cas où une clé de substitution a toujours du sens. Dans certains cas, vous pouvez choisir ce qui convient le mieux à la firebase database ou ce qu’il y a de mieux pour votre modèle d’object, mais dans les deux cas, il est préférable d’utiliser une clé ou un GUID sans signification. Cela rend l’indexation plus facile et plus rapide, et c’est une identité pour votre object qui ne change pas.

    Cheval pour les cours. Énoncer mon parti pris; Je suis d’abord développeur, donc je suis principalement soucieux de donner aux utilisateurs une application qui fonctionne.

    J’ai travaillé sur des systèmes avec des clés naturelles et j’ai dû passer beaucoup de temps à m’assurer que les changements de valeur se répercuteraient.

    J’ai travaillé sur des systèmes avec uniquement des clés de substitution, et le seul inconvénient était le manque de données dénormalisées pour le partitionnement.

    La plupart des développeurs PL / SQL traditionnels avec lesquels je travaillais n’aimaient pas les clés de substitution en raison du nombre de tables par jointure, mais nos bases de données de test et de production n’ont jamais fait défaut; les jointures supplémentaires n’affectaient pas les performances de l’application. Avec les dialectes de firebase database qui ne prennent pas en charge les clauses comme “X inner join Y sur Xa = Yb” ou les développeurs qui n’utilisent pas cette syntaxe, les jointures supplémentaires pour les clés de substitution rendent la lecture plus difficile et plus longue à taper vérifier: voir @Tony Andrews post. Mais si vous utilisez un ORM ou tout autre framework de génération SQL, vous ne le remarquerez pas. La saisie tactile atténue également.

    Cas 1: Votre table est une table de consultation de moins de 50 types (insertions)

    Utilisez des clés commerciales / naturelles . Par exemple:

     Table: JOB with 50 inserts CODE (primary key) NAME DESCRIPTION PRG PROGRAMMER A programmer is writing code MNG MANAGER A manager is doing whatever CLN CLEANER A cleaner cleans ............... joined with Table: PEOPLE with 100000 inserts foreign key JOBCODE in table PEOPLE looks at primary key CODE in table JOB 

    Cas 2: Votre table est une table avec des milliers d’inserts

    Utilisez des clés de substitution / auto-incrémentation . Par exemple:

     Table: ASSIGNMENT with 1000000 inserts joined with Table: PEOPLE with 100000 inserts foreign key PEOPLEID in table ASSIGNMENT looks at primary key ID in table PEOPLE (autoincrement) 

    Dans le premier cas:

    • Vous pouvez sélectionner tous les programmeurs de la table PEOPLE sans utiliser de jointure avec la table JOB, mais simplement avec: “SELECT * FROM PEOPLE WHERE JOBCODE = ‘PRG'”

    Dans le second cas:

    • Vos requêtes de firebase database sont plus rapides car votre clé primaire est un entier
    • Vous n’avez pas besoin de vous soucier de trouver la prochaine clé unique car la firebase database elle-même vous donne le prochain incrémentement.

    Peut-être pas tout à fait pertinent à ce sujet, mais un mal de tête, j’ai affaire à des clés de substitution. L’parsing pré-livrée d’Oracle crée des SK auto-générés sur toutes ses tables de dimension dans l’entrepôt et les stocke également sur les faits. Ainsi, chaque fois que les dimensions doivent être rechargées au fur et à mesure que de nouvelles colonnes sont ajoutées ou doivent être remplies pour tous les éléments de la dimension, les SK atsortingbués lors de la mise à jour désynchronisent les SK avec les valeurs originales stockées. un rechargement complet de toutes les tables de faits qui s’y joignent. Je préférerais que même si le numéro SK était dénué de sens, il y aurait un moyen de ne pas le modifier pour les dossiers originaux / anciens. Comme beaucoup le savent, la solution prête à l’emploi répond rarement aux besoins d’une organisation et nous devons constamment la personnaliser. Nous avons maintenant 3 ans de données dans notre entrepôt et les rechargements complets des systèmes Oracle Financial sont très importants. Donc, dans mon cas, ils ne sont pas générés à partir de la saisie de données, mais ajoutés dans un entrepôt pour faciliter la génération de rapports. Je comprends, mais les nôtres changent, et c’est un cauchemar.

    Dans le cas d’une firebase database ponctuelle, il est préférable d’avoir une combinaison de clés de substitution et de clés naturelles. Par exemple, vous devez suivre les informations d’un membre pour un club. Certains atsortingbuts d’un membre ne changent jamais. Par exemple, la date de naissance mais le nom peut changer. Créez donc une table Member avec une clé de substitution member_id et disposez une colonne pour DOB. Créer une autre table appelée nom de la personne et avoir des colonnes pour member_id, member_fname, member_lname, date_updated. Dans cette table, la clé naturelle serait member_id + date_updated.