Discussion :

[GuiDjad]

Bonjour,

Pour améliorer la rapidité d'exécution d'une requête SELECT, qu'elle est le choix le plus judicieux entre utiliser un CHAR comme clé primaire ou utiliser un INT comme clé primaire et un CHAR comme attribut avec la contrainte UNIQUE. Ce qui revient au même question résultat mais quelle choix propose un temps d'exécution optimisé?

Merci d'avoir lu ce message

[CinePhil]

Un entier est codé sur 4 octets.
Un CHAR est codé sur autant d'octets que la longueur du CHAR.

C'est à tester mais je pense que jusqu'à CHAR(4), les performances seront équivalentes. Au-delà, on préférera l'entier.

Pour plus d'arguments en faveur de la clé auto-incrémentée donc entière, voir l'article de SQLPro.

[GuiDjad]

D'accord mais le fait qu'on utilise deux attributs à la place d'un n'a t-il pas d'influence aussi?

[fsmrel]

Bonsoir,


Comme disait Guillaume d’Ockham :

Pluralitas non est ponenda sine necessitate.

Autrement dit, pourquoi deux attributs dont l’objet est le même, à savoir garantir l’unicité des tuples d’une relation ? Si c’est pour gagner une demi-milliseconde pour une requête dont la durée est d’une seconde, une minute, une heure ou un jour, et au-delà, quel intérêt ?
Attention aux mises à jour, une clé primaire + une contrainte UNIQUE => doublement des index => durée des mises à jour multipliée au moins par deux fois le nombre de relations auxquelles vous infligeriez ce traitement. Bref, soyez parcimonieux.

Comme dit CinePhil : la clé primaire ne doit pas être imposée par l'utilisateur. Si tel était le cas, alors seulement vous doublez sa clé (UNIQUE) par votre clé primaire bien à vous, invariable et sans signification, dont seul le système a la responsabilité. A ce sujet, je vous renvoie à l’élection de Miss Clé primaire.

[GuiDjad]

Attention aux mises à jour, une clé primaire + une contrainte UNIQUE => doublement des index => durée des mises à jour multipliée au moins par deux fois le nombre de relations auxquelles vous infligeriez ce traitement. Bref, soyez parcimonieux.

Comme dit CinePhil : la clé primaire ne doit pas être imposée par l'utilisateur. Si tel était le cas, alors seulement vous doublez sa clé (UNIQUE) par votre clé primaire bien à vous, invariable et sans signification, dont seul le système a la responsabilité. A ce sujet, je vous renvoie à l’élection de Miss Clé primaire.

Bonsoir,

Pour les mises à jour, la clé primaire n'est pas censé être mise à jour comme tu l'as dis dans ton post du Miss Clé primaire (Stabilité) . Mais si je suis ce que tu dis dans ton post:

Dans l’exemple ci-dessus, par hypothèse {MbrId} garantit ces deux critères et sera élue Miss clé primaire. Ses dauphines, à savoir {Pseudonyme} et {AdrCourriel} seront ravalées au rang de clés alternatives.

Il est mieux de mettre un INT en clé par clé primaire et de mettre Pseudonyme et AdrCourriel en UNIQUE. C'est le contraire de ce que tu m'as dit au dessus

[fsmrel]

Bonsoir,

Pour les mises à jour, la clé primaire n'est pas censé être mise à jour comme tu l'as dis dans ton post du Miss Clé primaire (Stabilité)

Par mise à jour j’entends INSERT, DELETE et UPDATE. Dans le cas d’un insert ou d’un delete, l’index associé à la clé primaire est mis à jour.

Il est mieux de mettre un INT en clé par clé primaire et de mettre Pseudonyme et AdrCourriel en UNIQUE. C'est le contraire de ce que tu m'as dit au dessus

Je ne suis pas sûr d’interpréter correctement ce que vous écrivez car vous donnez l’impression de juger de l’intérêt d’un type (INT en l’occurrence) par rapport à une contrainte (UNIQUE). En tout état de cause, pour parler SQL, une table n’a droit qu’à une seule clé primaire (clause PRIMARY KEY), tandis qu’elle peut avoir plusieurs clés alternatives (clause UNIQUE) :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
CREATE TABLE Membre
(
    MbrId        ...
  , Pseudonyme   ...
  , AdrCourriel  ...
   ...
  , CONSTRAINT Membre_PK PRIMARY KEY (MbrId)
  , CONSTRAINT Membre_AK1 UNIQUE (Pseudonyme)
  , CONSTRAINT Membre_AK2 UNIQUE (AdrCourriel)
   ... 
) ;

Quant à utiliser le type INT ou le type CHAR pour MbrId, c’est affaire de goût, de parcimonie, etc. Bien entendu, Pseudonyme et AdrCourriel ne seront pas du type INT.

Pour mémoire, la notion de clé primaire a disparu de la théorie relationnelle (à juste titre, Guillaume d'Ockham a encore frappé) :

Code D :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
VAR Membre BASE RELATION
{
    MbrId        ...
  , Pseudonyme   ...
  , AdrCourriel  ...      
  ...
}
KEY {MbrId}
KEY {Pseudonyme}
KEY {AdrCourriel} ;

[GuiDjad]

Bonjour,

Je ne suis pas sûr d’interpréter correctement ce que vous écrivez car vous donnez l’impression de juger de l’intérêt d’un type (INT en l’occurrence) par rapport à une contrainte (UNIQUE). En tout état de cause, pour parler SQL, une table n’a droit qu’à une seule clé primaire (clause PRIMARY KEY), tandis qu’elle peut avoir plusieurs clés alternatives (clause UNIQUE) :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
CREATE TABLE Membre
(
    MbrId        ...
  , Pseudonyme   ...
  , AdrCourriel  ...
   ...
  , CONSTRAINT Membre_PK PRIMARY KEY (MbrId)
  , CONSTRAINT Membre_AK1 UNIQUE (Pseudonyme)
  , CONSTRAINT Membre_AK2 UNIQUE (AdrCourriel)
   ... 
) ;

Je ne comprends pas à quoi sert l'attribut MbrId dans votre table. Un membre ne peut t'il pas être désigné par son Pseudonyme (ou son AdrCouriel)? J'ai supposé que c'était pour avoir une clé primaire plus simple d'où mon interprétation.

Pour mémoire, la notion de clé primaire a disparu de la théorie relationnelle (à juste titre, Guillaume d'Ockham a encore frappé) :

J'avais étudié la théorie relationnelle, il y'a longtemps avant. Pourtant je trouvais cette notion intéressante... Je vais faire quelques recherche pour me mettre à jour.

En tout cas, merci pour vos réponses.

[CinePhil]

Je ne comprends pas à quoi sert l'attribut MbrId dans votre table. Un membre ne peut t'il pas être désigné par son Pseudonyme (ou son AdrCouriel)?

Ce sont effectivement des clés candidates mais en terme de performances sur de grosses tables, ce ne sont pas des clés idéales car il est plus lourd d'indexer et de traiter en indexation des chaînes de caractères de plus de 4 octets plutôt que des entiers.
De plus, un pseudonyme ou une adrel peuvent être modifiées, ce qui contredit la bonne pratique d'invariabilité de la clé (primaire).
C'est pour ça qu'il est souvent préférable d'ajouter un identifiant artificiel de type entier et auto-incrémenté aux tables directement issues des entités du MCD.

J'ai supposé que c'était pour avoir une clé primaire plus simple d'où mon interprétation.

En gros c'est ça oui.

Si j'ai une entité Categorie qui contient un attribut code_categorie sur deux caractères, que le nombre de catégorie restera faible et que ces codes sont intangibles, il sera plus économe d'utiliser un type CHAR(2) que d'ajouter un identifiant de type entier.

Par contre pour une table de personnes, compte-tenu de ce que j'ai dit plus haut, il est préférable d'ajouter un identifiant de type entier, surtout si on ne sait pas combien de personnes seront à terme enregistrées dans la table.

Je vous renvoie sur l'article de SQLPro référencé dans mon précédent message.

[fsmrel]

Bonsoir,

Pour mémoire, la notion de clé primaire a disparu de la théorie relationnelle (à juste titre, Guillaume d'Ockham a encore frappé)

J'avais étudié la théorie relationnelle, il y'a longtemps avant. Pourtant je trouvais cette notion intéressante... Je vais faire quelques recherches pour me mettre à jour.

Pour commencer, vous pouvez par exemple consulter le dictionnaire de Chris Date, The Relational Database Dictionary auquel fait par exemple référence le papier suivant : Oracle Database / Applications Tips.

L’article dans lequel Chris Date a montré que le concept de clé primaire état strictement inutile fut publié dans InfoDB : The Primacy of Primary Keys: An Investigation. InfoDB 7(3), Summer 1993. Cet article fut republié in C. J. Date, Relational Database Writings 1991-1994, Addison-Wesley, 1995. Date a même battu sa coulpe, car il conclut ainsi son article, je cite :

In a previous paper I criticized the SQL standard for permitting foreign keys to reference alternate keys as well as primary keys. As this paper shows, I no longer feel that such criticism is valid, and I hereby withdraw it, with apologies.

Ainsi, Chris Date dut se résoudre à rendre caduques des contraintes formelles qu’il avait défendues pendant plus de vingt ans, essentiellement les suivantes et qu’il a reconnues comme arbitraires :

• Pour chaque variable relationnelle (table en SQL) on doit privilégier une des clés candidates, que l’on appellera clé primaire.
• Chaque clé étrangère fera exclusivement référence à une clé primaire.

=> Évolution mais pas révolution.

Je ne comprends pas à quoi sert l'attribut MbrId dans votre table. Un membre ne peut t'il pas être désigné par son Pseudonyme (ou son AdrCouriel)? J'ai supposé que c'était pour avoir une clé primaire plus simple d'où mon interprétation.

Du strict point de vue théorique, l’attribut MbrId ne se justifie pas. D’un point de vue pratique il en va autrement. Je fais référence une fois de plus à une expérience que j’ai vécue il y a quinze ans et que je rapporte de temps en temps chez DVP :

Les concepteurs d’une grande banque française avaient retenu le numéro Siren des entreprises pour identifier celles-ci (attribut NoSiren de la table Entreprise). Par le jeu des liens clé primaire - clé étrangère, l’attribut NoSiren se propageait dans une trentaine de tables. Balek ! Le numéro Siren est établi par un organisme extérieur à la banque, à savoir l’INSEE. J’avais fait observer qu’il n’était pas raisonnable de bâtir une clé primaire sur une donnée dont on ne maitrise pas la stabilité. De fait, l’INSEE envoyait tous les mois les nombreux correctifs modifiant le Siren des nouvelles entreprises (10% d’entre elles à peu près) et, vu le nombre de tables touchées (une trentaine) et leur volumétrie (plusieurs millions de lignes chacune), cela pouvait fortement pénaliser la production informatique (batchs de nuit). Une trentaine de tables à mettre à jour quant à leur valeur de clé primaire et/ou étrangère induit une activité physique intense (nombre excessif d'écritures sur le disque), outre un ordonnancement délicat. A ma demande, l’identifiant conceptuel de l’entreprise fit donc l’objet d’un nouvel attribut, non porteur d’information, artificiel et invariant, EntrepriseId, destiné à représenter la clé primaire de la table Entreprise et propagé en conséquence dans les autres tables, en lieu et place de l’attribut NoSiren. A partir de là, modifier un numéro de Siren impactait la seule table Entreprise au lieu d’une trentaine. Tandis qu'il n'avait pas connaissance de l'existence de l'attribut EntrepriseId, l’utilisateur Tartempion avait bien évidemment toujours accès au numéro Siren, devenu clé alternative pour la table Entreprise (et n’ayant donc pas perdu sa propriété d’unicité), modifiable sans que cela pose quelque problème que ce soit. Du point de vue technique, l’organisation des relations entre les clés primaires et étrangères avaient lieu sous le capot, sans que les modifications dues à l’INSEE aient un quelconque impact. Last but not least, les informaticiens de la banque avaient prévu un package, une usine à gaz de programmes pour s’assurer que le numéro Siren était cohérent dans toutes les tables où on le retrouvait : ce monstre ne vit évidemment pas le jour.

En corollaire, pour la nième fois je cite le grand Yves Tabourier qui écrivait il y a trente ans dans le contexte Merise (De l’autre côté de MERISE page 81) :

« ... la fonction d’une propriété est de décrire les objets (et les rencontres), alors que l’identifiant ne décrit rien. Son rôle fondamental est d’être sûr de distinguer deux jumeaux parfaits, malgré des descriptions identiques.
L’expérience montre d’ailleurs que l’usage des “identifiants significatifs” (ou “codes significatifs”) a pu provoquer des dégâts tellement coûteux que la sagesse est d’éviter avec le plus grand soin de construire des identifiants décrivant les objets ou, pis encore, leurs liens avec d’autres objets... »

Considérez la recommandation de Tabourier comme une règle d’or qui n’a pas vieilli.


Exemple d’organisation des tables :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE Entreprise
(   EntrepriseId      ...
  , EntrepriseSiren   ...
  , EntrepriseNom     ...
   ...
  , CONSTRAINT Entreprise_PK PRIMARY KEY (EntrepriseId)
  , CONSTRAINT Entreprise_AK UNIQUE (EntrepriseSiren)
  ... ) ;
CREATE TABLE Collaborateur
(    CollaborateurId    ...
   , CollaborateurNom   ...
   , EnrepriseId        ...
 , CONSTRAINT Coll_PK PRIMARY KEY (CollaborateurId)
 , CONSTRAINT Coll_FK FOREIGN KEY (EnrepriseId)
              References Entreprise (EnrepriseId) ...) ;

Selon la théorie relationnelle :

Code D :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
VAR Entreprise BASE RELATION
{   EntrepriseId      ...
  , EntrepriseSiren   ...
  , EntrepriseNom     ...   }
KEY {EntrepriseId}
KEY {EntrepriseSiren} ;

VAR Collaborateur BASE RELATION
{    CollaborateurId    ...
   , CollaborateurNom   ...
   , EnrepriseId        ... }
KEY {CollaborateurId}
FOREIGN KEY {EnrepriseId}
            REFERENCES Entreprise {EntrepriseId} ... ;

Notez la symétrie qui transparaît dans cette notation. Par contre, dans le cas de la notation SQL, la clé primaire peut être disqualifiée pour manque de stabilité et être remplacée par sa dauphine. Tant qu'on ne les aura pas chargées, cela restera transparent vu de la table Collaborateur et de la trentaine des autres tables impliquées (ouf !) :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
CREATE TABLE Entreprise
(   EntrepriseId      ...
  , EntrepriseSiren   ...
  , EntrepriseNom     ...
   ...
  , CONSTRAINT Entreprise_PK PRIMARY KEY (EntrepriseSiren) -- bof...
  , CONSTRAINT Entreprise_AK UNIQUE (EntrepriseId)
  ... ) ;
CREATE TABLE Collaborateur
(    CollaborateurId    ...
   , CollaborateurNom   ...
   , EnrepriseId        ...
 , CONSTRAINT Coll_PK PRIMARY KEY (CollaborateurId)
 , CONSTRAINT Coll_FK FOREIGN KEY (EnrepriseId)
              References Entreprise (EnrepriseId) ...) ;

Ai-je répondu à votre question ?

[GuiDjad]

Ai-je répondu à votre question ?

Oui et bien plus que ca

Merci à vous deux. Je vais pouvoir optimiser ma base de donnée