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

Ce ne sont là que quelques raisons d'utiliser des clés de substitution :

1. Stabilité : La modification d'une clé en raison d'une nécessité commerciale ou naturelle aura des répercussions négatives sur les tables connexes. Les clés de substitution doivent rarement, voire jamais, être modifiées car leur valeur n'a pas de signification.

2. Convention : Vous permet d'avoir une convention de dénomination de colonne de clé primaire standardisée plutôt que d'avoir à réfléchir à la manière de joindre des tables dont les clés primaires portent des noms différents.

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

Answer 2

Les deux. Le beurre et l'argent du beurre.

N'oubliez pas qu'une clé primaire n'a rien de particulier, si ce n'est qu'elle est désignée comme telle. Ce n'est rien d'autre qu'une contrainte UNIQUE NOT NULL, et une table peut en avoir plusieurs.

Si vous utilisez une clé de substitution, vous devez toujours disposer d'une clé de gestion pour garantir l'unicité conformément aux règles de gestion.

Answer 3

Il semble que personne n'ait encore dit quoi que ce soit en faveur des clés non substitutives (j'hésite à dire "naturelles"). Alors voici...

A désavantage des clés de substitution est qu'elles sont insignifiant (cité comme un avantage par certains, mais...). Cela vous oblige parfois à joindre beaucoup plus de tables dans votre requête qu'il n'est nécessaire. Comparez :

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';

Sauf si 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, dira-t-on, que se passe-t-il lorsque le code de MYPROJECT ou de VALID ou de HR change ? Ce à quoi je répondrai : "pourquoi besoin pour le changer ?" Il ne s'agit pas de clés "naturelles" dans le sens où un organisme extérieur va légiférer pour que, dorénavant, "VALIDE" soit recodé en "BON". Seul un petit pourcentage de clés "naturelles" entre vraiment dans cette catégorie - le numéro de sécurité sociale et le code postal en sont les exemples habituels. J'utiliserais certainement une clé numérique sans signification pour des tables telles que Personne, Adresse - mais pas pour des tables telles que tout ce que, pour une raison ou une autre, la plupart des gens ici semblent préconiser.

Voir aussi ma réponse à une autre question

Answer 4

Les clés de substitution (généralement des nombres entiers) ont la valeur ajoutée de rendre les relations de votre 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 lors de l'utilisation de clés de substitution, contrairement aux champs de clé d'entreprise, qui changent 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. Pensez à une table de personnes : les noms peuvent changer et leur unicité n'est pas garantie.

Pensez aux entreprises : vous êtes une entreprise merkinoise heureuse qui fait des affaires avec d'autres entreprises de Merkia. Vous êtes assez malin pour ne pas utiliser le nom de l'entreprise comme clé primaire, et vous utilisez donc l'identifiant unique de l'entreprise du gouvernement de Merkia dans son intégralité (10 caractères alphanumériques). Ensuite, Merkia modifie les identifiants de l'entreprise parce qu'elle a pensé que c'était une bonne idée. Ce n'est pas grave, vous utilisez la fonction de mise à jour en cascade de votre moteur de base de données, pour un changement qui ne devrait pas vous concerner en premier lieu. Plus tard, votre entreprise se développe et vous travaillez désormais avec une entreprise de Freedonia. L'identifiant de l'entreprise en Freedonia comporte jusqu'à 16 caractères. Vous devez élargir la clé primaire de l'identifiant de l'entreprise (ainsi que les champs de clé étrangère dans les commandes, les émissions, les transferts d'argent, etc.), en ajoutant un champ "Pays" dans la clé primaire (ainsi que dans les clés étrangères). Ouch ! Guerre civile en Freedonia, qui est divisée en trois pays. Le nom du pays de votre associé doit être remplacé par le nouveau ; les mises à jour en cascade à la rescousse. BTW, quelle est votre clé primaire ? (Country, CompanyID) ou (CompanyID, Country) ? Cette dernière clé facilite les jointures, tandis que la première permet d'éviter un autre index (ou peut-être plusieurs, si vous souhaitez que vos commandes soient également regroupées par pays).

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é d'entreprise.

Answer 5

La clé de substitution n'aura JAMAIS de raison de changer. Je ne peux pas en dire autant des clés naturelles. Les noms de famille, les courriels, les numéros ISBN - tout cela peut changer un jour.