Discussion :

[tatayo]

Bonjour la communauté,
Pour changer j'ai une petite question, je ne sais pas trop si je suis dans la bonne section du forum.

Pour commencer je plante le décors: en prévision du changement de notre ERP, on va mettre en place une base de données pour orchestrer les échanges entres divers applications (WMS, ERP, site web…).
Ici je tiens les rôles de DBA et de développeur, et on utilise Sql Serveur.

Je suis en train de modéliser la base de données, et comme il se doit j'affecte à chaque table possède une clé primaire de type IDENTITY, sauf bien sûr les table d'association, dont la clé est la combinaison des divers clés rapportées (pas plus de 2). Elles possèdent aussi une colonne "code", qui contient une valeur de type chaine ayant une signification pour le commun des mortels.

Et voici mon problème: mes deux collègues (un DSI et un développeur) tiquent sur ce type de données pour les clés. En gros si on prend par exemple une table client et une table commande, ils préfèrent avoir le code du client (de type char)dans la commande, au lieu d'un ID numérique, car "c'est plus simple pour la maintenance"
Autres exemples:
Eux: "Je peux plus facilement retrouver la commande avec le code client".
Moi: "Il suffit d'utiliser une vue"
Eux: "oui mais ça nous oblige à multiplier les vues".

Moi: "Avec des clés numériques, les requêtes seront plus performantes"
Eux: "non, car il faut faire plus de jointures, et par exemple pour créer une commande en import il faut chercher l'id du client, l'id de la devise, l'id du magasin"

Moi: "Si actuellement on a de gros problèmes de performances, c'est en partie parce que les clés sont de type varchar"
Eux: "Non, dans mon ancienne boîte on n'avait pas ce problème"

Bref je ne sais plus trop comment "imposer" de bonnes pratiques avant qu'il ne soit trop tard.

Alors, comment leur faire comprendre qu'utiliser des clés de type IDENTITY, et non des codes de type chaine ?

Merci de votre aide

Tatayo.

[SQLpro]

Un INT c'est 4 octets. => 32 bits et cela permet 4 milliards de clefs (donc 4 milliards de lignes par table au plus)
Un BIGINT c'est 8 octes => 64 bits et cela permet 18*446*744*073*709*551*616 clefs par table au plus...
Un VARCHAR de 8 caractères c'est entre 3 et 12 octets => au plus 96 octets. (il y a 2 octets supplémentaires pour indiquer
MAIS... il y a déjà 32 caractères non imprimables, des lettres accentuées en majuscules et minuscules, des signes de ponctuation. En se limitant aux 26 lettres de l'alphabet en majuscule et en minuscules plus les 10 chiffres cela fait 52 + 10 = 62 combinaisons par caractères.... et non 256 par octets !
MAIS (2e) ... la plupart des bases sont installées avec une collation insensible à la casse => 36 combinaisons par caractères, soit pour 8 caractères 36^8 = 2*821*109*907*456 (2 821 milliards de lignes). Beaucoup, beaucoup moins qu'un BIGINT.... Pour info, la sncf (TGV.com...) a déjà fait 7 cycles avec ses références à 6 lettres pour les dossiers !

Un INT nécessite une seule passe dans le processeur (64 bits) pour être manipulées. De même qu'un BIGINT.
Un VARCHAR(8) va nécessité 2 passes dans le processeur parce que 96 > 64. En jointure ce coût va doubler....
En sus la gestion de la collation (confusion maj/min) coute du traitement

En conclusion :
1) le VARCHAR sera toujours plus volumineux à stocker
2) le coût de traitement sera entre 3 et 5 fois plus lent qu'un entier

Enfin, un des principes fondamentaux de l'art des bases de données consiste à fournir des clés asémantiques afin qu'elle ne soient JAMAIS entachés par des problématiques externes :

• correction parce que mauvaise saisie avec répercussion sur toutes les tables filles
• modification de la taille de la colonne parce que nécessité
• changement de règles métier, de règlementation affectant le type

Tout ceci va avoir un coût gigantesque à termes !

La bonne solution est la technique de la double clé :
1) une clef auto incrémentée comme PRIMARY KEY sur laquelle on fait les jointures
1) une clef sémantique UNIQUE sur laquelle on fait les recherches.

A +

[escartefigue]

Bonjour,

Quitte à être redondant avec les arguments déjà développés par Frédéric

• le (var)char contient le plus souvent des valeurs susceptibles d'être modifiées car leur contenu est fonctionnel.
  Ce faisant, on risque de propager via les FK des mises à jour en cascade qui peuvent être très volumineuses, là ou l'utilisation d'une clef asémantique évite ce genre de soucis. J'ai connu ce genre de problème dans le milieu bancaire, où le code banque (qu'on retrouve dans tous les comptes) avait été utilisé comme PK. Or, les banques fusionnent, c'est même très fréquent...
• le (var)char, à nombre de valeurs identique, est plus encombrant que l'integer
• le (var)char est sensible à la collation contrairement aux types numériques. Point particulièrement sensible si vous faites des jointures entre deux databases distinctes (mais pas seulement)
• le varchar nécessite un réalignement sur longueur fixe pour certaines opérations (group by, distinct, partition by...)
• le varchar provoque des déplacements dans les pages data et index lorsque la longueur effective est modifiée lors d'un UPDATE, ce qui provoque une désorganisation des données au détriment du cluster ratio et donc des perfs

Je ne vois donc aucun argument plaidant en faveur d'une ou plusieurs colonnes (var)char en tant que PK. Comme clef candidate oui, primaire non !

[tatayo]

Bonjour,
En fait c'est bien ce que je tente de leur expliquer, mais rien à faire.
J'ai pris par exemple la recherche des clients (plusieurs dizaines de milliers) sur un code pays.
Avec leur méthode, pas de jointure mais la recherche porte sur une colonne (var)char pour les milliers de lignes.
Avec "ma" méthode, il faut une jointure mais sur un int, et la recherche sur la colonne (var)char n'est faite que sur la table pays, donc grosso-modo dans les 200 lignes.

Il ne fait aucun doute que les performances seront toutes autres.
Il en va de même avec les contraintes d'intégrité (que j'ai du mal aussi à faire accepter d'ailleurs ), pour les mêmes raisons.
En cas d'insertion d'une ligne dans la table client, les contrôles d'intégrité seront forcément plus rapides avec des clé de type integer.

Je ne parle même pas des tables avec plusieurs clé rapportées.

Bref, à la dernière réunion je suis limite passé pour un intégriste.

Tatayo.

[al1_24]

Une solution que j'essaye d'employer le plus souvent possible (mais ce n'est pas toujours gagné) :
Ne mettre à disposition que des vues présentant les tables telles qu'ils les attendent mais avec un beau modèle normalisé dessous.
Sans oublier les déclencheurs qui vont bien pour prendre en charge les mises à jour et les insertions.

[vanagreg]

Bonjour,

En gros ils ne veulent pas utiliser des ids numériques parce qu'ils ont la flemme de faire des jointures, c'est bien ça ??
En quoi l'utilisation du varchar au lieu du numérique serait "plus simple pour la maintenance"? S'assurer que les clés varchar2 soient insensibles à la casse, aux accents et autres caractères spéciaux c'est plus simple pour la maintenance? Et en plus ça veut dire que les ids varchar2 ne seraient pas générées automatiquement?

[tatayo]

Vanagreg : En quoi c'est "plus simple" ?
Par exemple pour récupérer un client français (je simplifie pour l'exemple):
Leur version:

Code sql :

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

1
2
3
4
 
select *
from client
where codepays = 'FR'

"Ma" version:

Code sql :

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

1
2
3
4
5
 
select client.*
from client
inner join pays on client.idpays = pays.id
where pays.code = 'FR'

C'est quand même plus simple, je n'ai pas à mémoriser le code du pays...
Par maintenance ils entendent "je regarde dans la base, pour voir d'où vient le problème".

De mon point de vue il me semble quand même que la base de données est plus souvent "consultée" par les applications qu'à la main pour la maintenance.

al1_24: Déjà que quand je propose une vue pour "résoudre" le "problème" précédent, on me rétorque que ça va multiplier les vues pour rien, je n'imagine même pas masquer toutes les tables et ne présenter que des vues.
C'est en fait une piste que je voulais explorer, mais clairement ce n'est même pas la peine d'y penser.

Si je ne trouve pas de solides arguments, ce n'est vraiment pas gagné.

Tatayo.

[escartefigue]

@Tatayo :
Cet exemple est mal choisi car le débat ne porte que sur les identifiants primaires. Ceux qui se propagent dans les tables liées.
Or, le code pays, ou l'identifiant permettant de faire une jointure vers la table des pays, ne saurait bien évidemment en aucun cas être l'identifiant primaire de la table client.

L'argument principal en faveur d'un identifiant technique comme identifiant primaire est la stabilité de celui-ci.
Ceux qui plaident en faveur d'un type (var)char sont le plus souvent tentés d'utiliser une valeur sémantique.
Pour rester sur l'exemple du code pays, si on choisit celui-ci comme identifiant primaire de la table des pays, on s'expose à des mises à jour en cascade dans les tables client, fournisseur, adresse etc. à chaque fois qu'un pays change de code.
Ca n'arrive pas tous les jours, mais ce n'est pas rare, souvenons nous près de chez nous de l'éclatement de la Tchécoslovaquie en République Tchèque et en Slovaquie, de l'éclatement de la Yougoslavie en une multitude de petits pays, plus loin de nous la Haute Volta devenue Burkina Faso, le Dahomey de venu Benin etc.

Quant à la réticence sur la mise en œuvre de l'intégrité référentielle, c'est encore pire. Dans un environnement multi-utilisateurs, aucune application ne peut garantir l'intégrité des données, seul le SGBD peut le faire. Or, que valent les données si leur intégrité n'est pas garantie ?
En l'absence d'intégrité référentielle, on ouvre la porte aux factures sans clients, aux lignes de commande sans commande, aux lignes d'écriture comptable sans compte... Donc, non seulement on pourrit la base de données, mais en plus, on est dans l'illégalité !

[vanagreg]

Donc ça voudrait dire que FR est une valeur de la PK de la table pays. Impossible d'alimenter automatiquement les valeurs de cette PK donc. Ok pour une table pays on peut imaginer la précharger, mais si on prend l'exemple de la table clients. Comment seraient alimentés les valeurs de la PK, par saisie? C'est risqué quand même. Si on se trompe? Au lieu de saisir 'Matrin' on voulait saisir en fait 'Martin'. Vu qu'on ne met pas à jour une PK, on ne peut pas corriger. Alors qu'une valeur numérique alimentée automatiquement en identity ou par trigger serait quand même plus simple.

[escartefigue]

NON : la colonne code pays (pouvant prendre la valeur "FR") n'est pas non plus l'identifiant primaire de la table pays !
C'est une clef candidate, unique, mais pas primaire.
Là aussi, il faut utiliser un identifiant technique (identity) comme clef primaire

ce qui donne le modèle (MCD et MLD) suivant :

Pièce jointe 594774

Et le script (ici pour SQL server) :

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
17
18
19
20
CREATE TABLE PY_pays(
   PY_ident INT IDENTITY,
   PY_code CHAR(3) NOT NULL,
   PY_libelle VARCHAR(50) NOT NULL,
   PY_dtdeb DATE NOT NULL,
   PY_dtfin DATE NOT NULL,
   PRIMARY KEY(PY_ident),
   UNIQUE(PY_code)
);
 
CREATE TABLE CL_client(
   CL_ident INT IDENTITY,
   CL_numero CHAR(6) NOT NULL,
   CL_nom VARCHAR(50) NOT NULL,
   CL_prenom VARCHAR(50) NOT NULL,
   PY_ident INT NOT NULL,
   PRIMARY KEY(CL_ident),
   UNIQUE(CL_numero),
   FOREIGN KEY(PY_ident) REFERENCES PY_pays(PY_ident)
);

Avec une date de début et de fin de validité du pays, puisque certains pays disparaissent, d'autres se créent

[vanagreg]

On est d'accord, mais ce n'est pas ce que les collègues de Tatayo veulent ici si j'ai bien compris. Ils veulent la colonne pays Py_Code directement dans la table clients.

[escartefigue]

Ces collègues en question seraient avisés de s'informer de ce que sont les formes normales et pourquoi il est important de s'en préoccuper...

[CinePhil]

Je reviens sur l'exemple donné :

Vanagreg : En quoi c'est "plus simple" ?
Par exemple pour récupérer un client français (je simplifie pour l'exemple):
Leur version:

Code sql :

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

1
2
3
4
 
select *
from client
where codepays = 'FR'

"Ma" version:

Code sql :

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

1
2
3
4
5
 
select client.*
from client
inner join pays on client.idpays = pays.id
where pays.code = 'FR'

C'est quand même plus simple, je n'ai pas à mémoriser le code du pays...
Par maintenance ils entendent "je regarde dans la base, pour voir d'où vient le problème".

De mon point de vue il me semble quand même que la base de données est plus souvent "consultée" par les applications qu'à la main pour la maintenance.

Exact pour le dernier point. Et je renforce l'argument en disant que dans la pratique, il va y avoir une liste déroulante permettant de sélectionner le pays et la valeur de chaque option sera l'identifiant du pays.
=> Ils n'ont même pas besoin de savoir le code du pays et encore moins son identifiant ; c'est automatique.
J'ose espérer qu'ils ne mettent pas de valeurs en dur dans leur code ! Si ?

J'ai pris par exemple la recherche des clients (plusieurs dizaines de milliers) sur un code pays.
Avec leur méthode, pas de jointure mais la recherche porte sur une colonne (var)char pour les milliers de lignes.
Avec "ma" méthode, il faut une jointure mais sur un int, et la recherche sur la colonne (var)char n'est faite que sur la table pays, donc grosso-modo dans les 200 lignes.

Tu peux faire un petit prototype.
1) La table des clients avec la colonne code pays
2) Les deux tables normalisées

Puis un test de performance sur les deux solutions... à répéter plusieurs fois avec des codes pays différents (parce que la jointure entre la table pays et la table client sera peut-être gardée en mémoire par SQL Server ; je ne sais pas bien comment le SGBD fonctionne sur ce plan là). Et aussi avec une liste de pays : WHERE code_pays IN ('FR', 'GB', 'CH', 'LU') par exemple.

Un autre argument concernant la jointure :
Il y a un souci pour les clients chinois. Le mainteneur ne connait pas le code pays de la Chine et veut vite sortir la liste des clients chinois. => Il va faire une jointure avec la table des pays pour récupérer le code !
Donc leur argument d'absence de jointure il ne tient même pas pour tous les cas !

Et puis merde ! C'est toi le DBA ou pas ?

EDIT : On doit pouvoir retrouver l'exemple vécu donné régulièrement par fsmrel. De mémoire : l'utilisation du SIRET comme clé primaire et un traitement batch de mise à jour qui prenait plus de 24h !

[Waldar]

C'est le but de la démarche de tatayo, pourquoi travailler avec des bonnes pratiques 3NF ne doit pas être contourné.

L'argument de la mise à jour du code pays, quand tu travailles en France, ça ne sera pas retenu - j'ai bien compris que ce n'était qu'un exemple, mais tu sais quand un décideur est entêté il faut le convaincre.

D'ailleurs la démarche de son équipe est honnête je trouve, puisqu'en environnement OLAP la dénormalisation est une technique d'optimisation très efficace (et si un code change, on recalcule). En OLTP bien entendu il faut l'éviter.

L'argument de la performance lui est intéressant, mais si la base pèse 20 Go, ça ne sera pas retenu non plus. Par contre s'ils veulent grossir sur quelque chose de plus gros ça doit être pris en compte impérativement.
Peut-être qu'il faille mettre en avant la maintenabilité du modèle également.

[CinePhil]

clients (plusieurs dizaines de milliers)

Ça doit vouloir dire encore plus de dizaines de milliers de commandes, de bons de livraison et de factures ; et encore plus de dizaines de milliers de lignes de commande, de lignes de livraison et de lignes de factures.
On doit vite arriver à des volumes intéressants avec des requêtes sûrement plus complexes que la recherche d'un client sur un code pays.

Cherche les requêtes qui prennent le plus de temps et construit un modèle avec des clés primaires IDENTITY et un autre avec des clés primaires alpha et compare. Je suis quasi sûr que ces requêtes sont plus rapides avec les IDENTITY, sans même parler de la génération de la clé qui prend moins de temps en IDENTITY (car réalisé par une séquence) qu'avec une clé alpha (genre 3 premières lettres du nom du client suivies d'un numéro d'ordre parce que plusieurs clients peuvent avoir les mêmes trois premières lettres).

[tatayo]

Effectivement, le code pays n'est qu'un exemple, par forcément le plus pertinent.
Je n'avais pas avancé le cas du changement de code (pays, client ou autre) en réunion puisque de fait on n'en fait pas.

Actuellement la base fait environ 300Go, et si je prends un exemple plus concret, nous avons un peu plus de 300000 code articles, qui sont référencés dans les lignes de commande, livraison, facture (plusieurs centaines de milliers de lignes chacune), encours de stock (2 millions de lignes), mouvements de stock (un peu moins de 20 millions de lignes), etc...

Je suis resté sur le côté performance, car c'est le plus concret et le plus "palpable", vu qu'actuellement nous avons des calculs de stats qui sont (à juste titre) critiquées pour leur durée. 2 minutes pour sortir un récapitulatif de CA, c'est effectivement bien trop long.
Je peux difficilement leur dire "Faites moi confiance, avec des clés de type entier le même écran ne prendra que quelques secondes.", sans avoir de preuve concrètes.

Pour rester cohérent dans le schéma je voulais donc systématiquement utiliser comme clé primaire une colonne de type IDENTITY, et ajouter si besoin une colonne "code" avec un contenu plus parlant et un index unique dessus.

Tatayo.

[escartefigue]

Autre argument : une clef encombrante pénalise non seulement les performances d'accès, mais aussi les performances des servitudes, c'est moins significatif, mais tant qu'à faire d'argumenter, autant avoir plus d'une corde à son arc

[CinePhil]

Actuellement la base fait environ 300Go, et si je prends un exemple plus concret, nous avons un peu plus de 300000 code articles, qui sont référencés dans les lignes de commande, livraison, facture (plusieurs centaines de milliers de lignes chacune), encours de stock (2 millions de lignes), mouvements de stock (un peu moins de 20 millions de lignes), etc...

Alors je pense que tu as de quoi faire des tests concluants.

Actuellement, les clés primaires sont en IDENTITY ou en alpha ?

[tatayo]

Actuellement, les clés primaires sont en IDENTITY ou en alpha ?

Du varchar entre 5 et 17 (!) charactères.
Je veux justement profiter qu'on repars sur une nouvelle base pour qu'on prenne d'entrée les bonnes décisions, donc je me bats bec et ongles pour avoir des clés de type IDENTITY.
Mais la sauce ne prend pas.

Je vais essayer de dégager du temps pour monter un "prototype" avec les données de la prod, mais normalisées.
Je ne doute pas que les performances seront incomparable, j'espère juste ne pas me reprendre l'excuse des 10 secondes gagnés lors de la saisie de la requête manuelle une fois par mois pour maintenance.

En dernier recours je leur envoie un lien vers cette discussion

Tatayo.

[CinePhil]

Dans la bible relationnelle de fsmrel, voir le chapitre 3.2.6.

Et sa fameuse histoire vécue :

Les concepteurs d’un projet particulièrement sensible d’une grande banque avaient retenu le numéro Siren des entreprises pour identifier celles-ci (attribut NoSiren de l’entité-type ENTREPRISE dans le MCD). Au niveau SQL, par le jeu des liens inter-tables (clé primaire - clé étrangère), le numéro Siren se propageait dans de nombreuses tables. Or, ce numéro est fourni par l’INSEE, lequel envoyait tous les mois les correctifs modifiant le Siren des entreprises venant de naître (10% d’entre elles à peu près). Les concepteurs en avaient tenu compte et me montrèrent le modèle correspondant à la mise à jour des tables impliquées : une usine à gaz ! J’avais fait observer que, vu le nombre de tables touchées et leur volumétrie (plusieurs millions de lignes chacune), cela pouvait faire exploser la production informatique (batchs de nuit), du fait d’une activité de mise à jour excessive et en plus, délicate à ordonnancer. Après leur avoir parlé de la règle d’or de Tabourier, sans que j’ai eu à le leur demander, les concepteurs définirent dans le MCD un nouvel attribut, non porteur d’information, artificiel et invariant, destinée à devenir au stade SQL la colonne composant la clé primaire de la table Entreprise, propagé en conséquence dans les autres tables, en lieu et place de la colonne NoSiren. A partir de là, modifier un numéro de Siren n’impactait plus que la seule table ENTREPRISE, les utilisateurs ayant bien évidemment toujours accès au contenu de la colonne NoSiren (et à elle seule du reste), devenue clé alternative (et n’ayant donc pas perdu ses propriétés d’unicité et d’irréductibilité).