database - multiple - primary key sql



Clés de substitution vs naturelles/commerciales (13)

Ici nous allons encore, l'ancien argument se pose toujours ...

Serions-nous mieux avoir une clé d'entreprise en tant que clé primaire, ou aurions-nous plutôt un identifiant de substitution (c'est-à-dire une identité SQL Server) avec une contrainte unique sur le champ clé métier?

S'il vous plaît, fournissez des exemples ou des preuves pour soutenir votre théorie.


Answer #1

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


Answer #2

Cheval pour les cours. Pour énoncer mon parti pris Je suis d'abord développeur, donc je m'intéresse principalement à donner aux utilisateurs une application fonctionnelle.

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 seulement des clés de substitution, et le seul inconvénient a été un manque de données dénormalisées pour le partitionnement.

La plupart des développeurs PL / SQL avec lesquels j'ai travaillé n'appréciaient 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 été trop sollicitées; les jointures supplémentaires n'affectent pas les performances de l'application. Avec les dialectes de base de données qui ne supportent pas les clauses comme "X jointure interne 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 les requêtes plus difficiles à lire et plus longues à 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.


Answer #3

Il semble que personne n'ait encore dit quoi que ce soit à l'appui des clés non substitutives (j'hésite à dire «naturelles»). Alors voilà ...

Un inconvénient des clés de substitution est qu'elles sont sans signification (cité comme un avantage par certains, mais ...). Cela vous oblige parfois à joindre beaucoup plus de tables à votre requête que ce qui devrait vraiment être 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 ne 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 organisme extérieur va légiférer pour que «VALIDE» soit désormais recodé «BON». Seul un petit pourcentage de clés «naturelles» entrent vraiment dans cette catégorie - le code SSN et le code postal étant les exemples habituels. J'utiliserais certainement une clé numérique insignifiante pour des tableaux comme Personne, Adresse - mais pas pour tout , ce que , pour une raison quelconque, la plupart des gens semblent défendre.

Voir aussi: ma réponse à une autre question


Answer #4

Je déteste les clés de substitution en général. Ils ne doivent être utilisés que s'il n'y a pas de clé naturelle de qualité disponible. Il est plutôt absurde d'y penser, de penser que l'ajout de 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 façon dont elles sont le plus souvent recherchées, ce qui accélère les requêtes.

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

  3. Lorsque les clés de substitution sont utilisées comme clé primaire, il est beaucoup moins clair quelles sont les clés primaires naturelles. 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 plusieurs factures. Ainsi, la clé logique de l'organisation est OrgName. La clé logique de Comptes est OrgName, AccountID. La clé logique de Invoice est OrgName, AccountID, InvoiceNumber.

    Lorsque des clés de substitution sont utilisées, les chaînes de clés sont tronquées en ayant uniquement une clé étrangère pour le parent immédiat. Par exemple, la table Invoice n'a pas de colonne OrgName. Il a seulement une colonne pour le compte de compte. 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. Le stockage des valeurs de clé de substitution des tables de recherche entraîne le remplissage de tables avec des entiers sans signification. Pour afficher les données, vous devez créer des vues complexes qui se joignent à toutes les tables de recherche. Une table de recherche est destinée à contenir un ensemble de valeurs acceptables pour une colonne. Il ne devrait pas être codifié en stockant une clé de remplacement d'entier à 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 base de données différents. Aucun d'entre eux montre l'utilisation de clés de substitution.


Answer #5

Juste quelques raisons d'utiliser des clés de substitution:

  1. Stabilité : la modification d'une clé en raison d'une activité ou d'un besoin naturel affectera négativement les tables associées. Les clés de substitution doivent rarement, voire jamais, être modifiées car il n'y a pas de sens lié à la valeur.

  2. Convention : vous permet d'avoir une convention de dénomination de colonne Primary Key standardisée plutôt que d'avoir à penser à la manière de joindre des tables avec des noms différents pour leurs PKs.

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


Answer #6

L'utilisation d'une clé de substitution est meilleure à mon avis car il n'y a aucune chance qu'elle change. Presque tout ce que je peux penser que vous pourriez utiliser comme une clé naturelle pourrait changer (avertissement: pas toujours vrai, mais communément).

Un exemple pourrait être une DB de voitures - à première vue, vous pourriez penser que la plaque d'immatriculation pourrait être utilisée comme clé. Mais ceux-ci pourraient être changés pour que ce soit une mauvaise idée. Vous ne voudriez pas vraiment le savoir après la sortie de l'application, quand quelqu'un vient vous demander pourquoi ils ne peuvent pas changer leur plaque d'immatriculation à leur nouveau brillant personnalisé.


Answer #7

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 termes de stockage et de vitesse de mise à jour (mieux encore, les clés étrangères n'ont pas besoin d'être mises à jour). cela change de temps en temps).

La clé primaire d'une table doit être utilisée pour identifier uniquement la ligne, principalement à des fins de jointure. Pensez à une table Personnes: les noms peuvent changer, et ils ne sont pas garantis uniques.

Think Companies: vous êtes une entreprise Merkin heureuse qui fait des affaires avec d'autres sociétés à Merkia. Vous êtes assez astucieux pour ne pas utiliser le nom de l'entreprise comme clé primaire, vous utilisez donc l'identifiant unique de l'entreprise du gouvernement Merkia dans sa totalité de 10 caractères alphanumériques. Puis Merkia change les identifiants de l'entreprise parce qu'ils pensaient que ce serait une bonne idée. C'est bon, vous utilisez la fonctionnalité de mises à jour en cascade de votre moteur de base de données, pour un changement qui ne devrait pas vous impliquer en premier lieu. Plus tard, votre entreprise se développe, et maintenant vous travaillez avec une entreprise à Freedonia. La société Freedonian id peut contenir jusqu'à 16 caractères. Vous devez agrandir la clé primaire de l'ID de la société (également les champs de 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é devrait ê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 indice (ou peut-être plusieurs, si vous voulez que vos ordres soient également regroupés par pays).

Tous ces éléments ne sont pas une preuve, mais une indication qu'une clé de substitution pour identifier de façon unique une ligne pour toutes les utilisations, y compris les opérations de jointure, est préférable à une clé d'entreprise.


Answer #8

Les clés de substitution peuvent être utiles lorsque les informations commerciales peuvent changer ou être identiques. Les noms commerciaux ne doivent pas nécessairement être uniques à travers le pays, après tout. Supposons que vous traitiez avec deux entreprises nommées Smith Electronics, l'une au Kansas et l'autre 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 se déplace de l'autre côté de la rivière à Kansas City, Missouri? Il n'y a aucun moyen évident de garder ces entreprises 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 ils sont certainement des livres différents, pas seulement des éditions différentes. Dans un cas comme celui-là, je pourrais me référer à l'apparence des livres, ou plus tôt que plus tard, mais c'est tout aussi bien que j'ai l'ISBN sur lequel je me rabat.


Answer #9

Peut-être pas complètement pertinent à ce sujet, mais un mal de tête que j'ai affaire avec des clés de substitution. L'analytique pré-livrée d'Oracle crée des clés de sécurité générées automatiquement sur toutes ses tables de dimension dans l'entrepôt, et stocke également celles-ci sur les faits. Ainsi, chaque fois qu'ils (dimensions) doivent être rechargés à mesure que de nouvelles colonnes sont ajoutées ou doivent être remplies pour tous les éléments de la dimension, les SK affectés lors de la mise à jour désynchronisent les SK avec les valeurs originales stockées sur le fait. un rechargement complet de toutes les tables de faits qui s'y joignent. Je préférerais que même si le SK était un nombre sans signification, il y aurait un moyen de ne pas le faire pour les enregistrements originaux / anciens. Comme beaucoup le savent, une 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 ils sont ajoutés dans un entrepôt pour aider à la création de rapports. Je comprends, mais les nôtres changent, et c'est un cauchemar.


Answer #10

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'a pas la capacité de trier physiquement ces données. Vous devez à la place placer des index uniques sur ces données, bien qu'il soit également utile de simplement exécuter 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 base de données.

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


Answer #11

Toujours utiliser une clé qui n'a aucune 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], il explique pourquoi vous ne devriez pas utiliser autre chose qu'une clé sans autre signification que d'être une clé. Cela se résume au fait qu'il devrait avoir un seul emploi et un seul emploi.


Answer #12

Toujours utiliser une seule colonne, clé de substitution si possible. Cela rend les jointures ainsi que les insertions / mises à jour / suppressions beaucoup plus propres car vous êtes seulement responsable du suivi d'une seule information pour maintenir l'enregistrement.

Ensuite, au besoin, empilez vos clés commerciales en tant que contraintes ou index uniques. Cela vous permettra de garder l'intégrité des données intactes.

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


Answer #13

Cas 1: Votre table est une table de recherche avec moins de 50 types (inserts)

Utilisez les clés professionnelles / 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 les 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 dans la table PEOPLE sans utiliser la jointure avec la table JOB, mais seulement avec: "SELECT * FROM PEOPLE WHERE JOBCODE = 'PRG'"

Dans le deuxième cas:

  • Vos requêtes de base de données sont plus rapides car votre clé primaire est un nombre entier
  • Vous n'avez pas besoin de vous embêter à trouver la clé unique suivante car la base de données elle-même vous donne l'auto-incrémentation suivante.




key