Discussion :

[martinbrait]

Bonjour,

J'aimerai beaucoup être conseillé sur le stockage des informations de références
dans un système de base de données relationnelles.


J'ai quelques exemples à l'esprit :
Prenons la table t_pers_prs, d'état civil suivante :
kn_prs(pk),t_matricule(ak),t_nom,t_prenom,t_sexe,d_datenaiss,t_villnaiss

Dans une table d'état civil, je vais indiquer homme ou femme, comme propriété.
Le domaine de ma propriété t_sexe, aura uniquement 2 valeurs possible,
du coup, je choisis d'inclure le champ t_sexe, dans la même table, sans créer de table de référence. (ok ?)

Est-ce que je dois stocker dans ce champ aux données redondante, la valeur la plus courte possible
d'1 caractère M ou F, quitte à convertir par fonction M->masculin, F->féminin, à chaque requête ?
Est-ce que je dois stocker en toutes lettres masculin, féminin ?

A ce sujet, je constate une première réponse de SQL PRO, avec la création d'une table de référence M/F


Dans mon même exemple, je dispose d'un très grand nombre d'occurences de villes
différentes, au point où je choisis d'élever cette information au rang de propriété. (ok ?)

Dans mon exemple, quelle devrait être mon organisation, si je voulais également stocker
ville de naissance, département de naissance, région de naissance et pays de naissance ?

Combien de tables est il judicieux de créer ?
Y a t il une règle à savoir, qui serait liée, notamment, à l'usage que l'on fera de chaque information ?


Autre question :
Parfois, le gestionnaire ne connaît pas à l'avance,
la valeur qu'il voudra mettre comme référence, pour catégoriser une information donnée.
Dois-je en ce cas, systématiquement, réserver une table de référence et permettre
à mon gestionnaire d'y stocker progressivement de plus en plus de références de son choix ?



Autre question :
A partir de quel nombre de valeurs distinctes d'un domaine de référence, crée-t-on une table séparée contenant cette référence ?


Quelles les inévitables questions à se poser avant
de créer ou non, une table de référence ?

Merci beaucoup pour vos précieux conseils !

[Invité]

Prenons la table t_pers_prs, d'état civilsuivante :
kn_prs(pk),t_matricule(ak),t_nom,t_prenom,t_sexe,d_datenaiss,t_villnaiss

Pour commencer, je trouve tes noms de table et de données pas simples.

Nom de table :

Il devrait être court (2-5 caractères), simple, pertinent alors qu’il est composé de trois arguments. La norme SQL est de préfixer les noms de données du nom de leur table. Donc, autant faire court car on va les utiliser dans toute l’application (requêtes SQL). Cela se faisait déjà en COBOL dans les années 70, les fichiers étaient identifiés dans le programme et dans le shell par une étiquette logique et les données étaient préfixées de leur étiquette logique.

NB : Il est judicieux d’associer à l’application un ou deux caractères (initiale(s) de l’application) qui participerons au nommage de certaines tables comme la table des personnes.

Il est ainsi facile de nommer la plupart de ses tables par la concaténation de l’initiale ou les initiales de l’entité avec l’initiale ou les initiales de l’application.

Quand on développe plusieurs BDD, cela permet de se créer des environnements distincts et de maitriser ses copier-coller d’une BDD vers l’autre.

Soit [xx] les initiales de ton application, ta table des personnes serait alors nommée « pxx ». Nommer la table des personnes « pers » utilise 4 caractères et n’apporte rien.

Les noms de views peuvent être plus longs sans toutefois dépasser 10 caractères. La raison ? L’écran de gestion des tables du SGBD que j’utilisais changeait son affichage au-delà de 10 caractères et devenait moins lisible.

Noms de données :

Dans ta table, il ne devrait y avoir que des noms de données pertinents.

kn_prs(pk) : je ne comprends pas le soulignement, ni le préfixe « kn_ », ni le nom « prs », ni la parenthèse « (pk) ». Il s’agit sans doute d’un code pivot « n_prs », identifiant d’une autre table « pk ».

t_villnaiss : Pourquoi « vill » et non ville ? Pour gagner un caractère ?

En fait, il y a plusieurs informations concernant la naissance. Il serait pertinent de les suffixer par « _naiss ».

Tous tes noms de données préfixés « t_ » n’ont pas à être préfixés.

A l'exception des quatre lettres "C" pour Code, "T" pour Type, "N" pour Numéro et "L" pour Libellé, on évite les abréviations.

Toutes les tables de références (Communes, Départements, Nomenclature, etc.) possèdent un code ou un numéro, parfois un type et toujours un libellé. Ces noms de données sont constitués très simplement du nom de la table à laquelle ils appartiennent préfixé par la lettre mnémonique, "C" pour Code, "N" pour Numéro, "T" pour Type et "L" pour Libellé. Ainsi, une table des communes nommée "cm" aura pour noms de données :

cm.c_cm, (code commune)
cm.l_cm, (libellé commune)

Comme pour toute règle, il peut y avoir des exceptions, la table des codes postaux par exemple :

cp.n_cp, (numéro de code postal et non c_cp pour code code postal)
cp.l_ld, (libellé lieu distribué et non l_cp pour libellé code postal)

Sans en abuser, rien n'interdit d'étendre le principe (exemple : d_ pour date).

A signaler que dans les programmes, les noms de données gagnent à être renommés de la façon suivante :

cm.c_cm cm_c_cm,
cm.l_cm cm_l_cm,
etc.

A tout endroit d'un programme on sait de quelles données il s’agit. Les erreurs éventuelles sur les noms de données sont essentiellement des erreurs de saisie facilement repérables.

A noter que les variables d'un programme gagnent également à être préfixées, les préfixes classiques étant :

« P_ » pour Paramètre
« V_ » pour Variable
« CTR_ » pour Compteur
« NBR_ » pour Nombre

Pour une table « TA_ » :

« I_TA » pour Indice courant
« J_TA » pour Indice borne des items renseignés
« K_TA » pour indice borne de la table

Dans mon exemple, quelle devrait être mon organisation, si je voulais également stocker ville de naissance, département de naissance, région de naissance et pays de naissance ?

Tes tables pourraient ressembler à ça :

Code :

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
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
{ pers  (personnes) -----------------------------------------------------------}

create table pers
(
n_pers            char(8)  not null,
matricule         char(8)  not null,
nom               char(30) not null,
prenom            char(30) not null,
sexe              char(1)  not null, { [M]asculin [F]éminin [N]eutre            }
d_naiss           date     not null,
c_cm_naiss        char(5)  not null, { [00000] => ville et pays naissance       }
ville_naiss       char(30),
pays_naiss        char(30)
) ;

{ cm    (communes) ------------------------------------------------------------}

create table cm
(
c_dp             char(2)  not null,
c_cm             char(5)  not null,
l_cm             char(38) not null
) ;

{ cp    (codes postaux) -------------------------------------------------------}

create table cp
(
c_cp             char(5)  not null,
c_cm             char(5)  not null,
l_ld             char(30) not null
) ;

{ dp    (départements) --------------------------------------------------------}

create table dp
(
c_dp             char(2)  not null,
c_rg             char(2)  not null,
l_dp             char(30) not null
) ;

{ rg    (régions) -------------------------------------------------------------}

create table dp
(
c_rg             char(2)  not null,
l_rg             char(30) not null
) ;

Combien de tables est-il judicieux de créer ?
Y a t il une règle à savoir, qui serait liée, notamment, à l'usage que l'on fera de chaque information ?

C’est la règle du bon sens. La fonction crée l’organe.

Par exemple, il est pertinent de gérer une table des départements et des communes mais pas vraiment des pays, donc des villes étrangères. Ainsi, soit le lieu de naissance est en France et on renseigne la commune, soit le lieu de naissance est à l’étranger et l’on renseigne la ville et le pays.

Pour être concret et exhaustif concernant l’une de mes BDD avec laquelle je devais gérer des frais de déplacement, j’ai dû créer ces tables en complément des tables classiques « cm », « cp » et « dp ». Par contre, la région ne m’intéressait pas.

Code :

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

1
2
3
4
5
6
{ am    (agglomérations multi-communales) -------------------------------------}
{ cl    (communes limitrophes) ------------------------------------------------}
{ dg    (districts géographique) ----------------------------------------------}
{ mp    (média-post) ----------------------------------------------------------}
{ t_cm  (typologie communes) --------------------------------------------------}

NB : La table mp (média/post) résulte de l’importation des communes depuis un fichier acquis chez MEDIAPOST.

A partir de quel nombre de valeurs distinctes d'un domaine de référence, crée-t-on une table séparée contenant cette référence ?

A partir du moment où traduire un code dans les programmes devient fastidieux. Traduire l’information sexe est supportable.

Quelles sont les inévitables questions à se poser avant de créer ou non, une table de référence ?

La création d’une table de référence doit être évidente. C’est l’utilisation des libellés dans les développements qui impose la création d’une table de référence, utilisation dans les écrans, dans les états, dans les requêtes SQL.

Cela dit, il n’y a pas qu’à propos des tables de références qu’il faut s’interroger.

Anecdote :

Le responsable de la formation continue d’une académie a décrété que le personnel ne pouvait candidater qu’à deux stages. Pour deux candidatures, le développeur a décidé de les mettre dans l’entité « personnes ». Quelques années plus tard, j’ai l’occasion de revoir le développeur. Je lui demande : comment t’en es–tu sorti avec tes candidatures dans ta table personnes ; il a bien fallu que tu gères des candidatures collectives, des candidatures public désigné, etc. ?

Réponse : c’est simple, nous avons tout redéveloppé… en 4GL.

Ha bon ! Quelques années encore plus tard, j’apprends que son application a été abandonnée car plus personne n’était capable de la maintenir.

Parfois, le gestionnaire ne connaît pas à l'avance, la valeur qu'il voudra mettre comme référence, pour catégoriser une information donnée. Dois-je en ce cas, systématiquement, réserver une table de référence et permettre à mon gestionnaire d'y stocker progressivement de plus en plus de références de son choix ?

Il est préférable de prévoir une référence « divers » ou « autre » que le gestionnaire pourra renseigner plus tard. Il est également possible de prévoir une zone mémo.

[martinbrait]

Bonjour IFA2377,
Ｍerci BEAUCOUP pour votre réponse très documentée !
Certains détails m'ont moins parlé que d'autre. J'y réfléchis encore.

... C’est la règle du bon sens. La fonction crée l’organe.
Par exemple, il est pertinent de gérer une table des départements et des communes mais pas vraiment des pays, donc des villes étrangères. Ainsi, soit le lieu de naissance est en France et on renseigne la commune, soit le lieu de naissance est à l’étranger et l’on renseigne la ville et le pays. ...

Bien compris, je garde ce précieux conseil à l'esprit !

t_villnaiss : Pourquoi « vill » et non ville ? Pour gagner un caractère ?

Exactement, c'est pour gagner sur le nombre de caractères.
Pour être franc, mes mnémoniques de colonnes, ne devraient jamais dépasser 7 caractères.
J'y ajoute systématiquement la désignation en clair (dans la légende, sous access, ou en description, pour les autres SGBD)
Ainsi, t_vilnais ferait encore mieux l'affaire. La colonne dans une requête sql en aurait été écourtée.

Tous tes noms de données préfixés « t_ » n’ont pas à être préfixés.

En fait, je tiens à mes préfixes devant chaque mnémonique de colonne.
Je préfixe systématiquement mes noms de colonnes de la manière suivante :
t_ : type texte
n_ : type numérique
m_ : type mémo
d_ : type date
h_ : type timestamp
Ainsi, lors notamment de requêtes depuis des sources externes, je ne me pose pas 36 questions à enquêter sur la concordance de type entre la colonne source et la colonne cible.
De plus, j'aime cette abréviation très simple de mes colonnes date, tous préfixés par d_

[martinbrait]

En fait, il y a plusieurs informations concernant la naissance. Il serait pertinent de les suffixer par « _naiss ».

Je garde cette info précieuse, dans un coin de la tête.

Voici la manière dont j'ai désormais créé mes tables,
en espérant avoir respecté l'esprit de la solution proposée :

Code :

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
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
{ pers  (personnes) -----------------------------------------------------------}

create table pers
(
kn_pers            char(8)  not null,
ak_matri        char(8)  not null,
t_nom             char(30) not null,
t_prenom           char(30) not null,
t_sexe              char(1)  not null, { [M]asculin [F]éminin [N]eutre            }
d_naiss           date     not null,
kn_cm       char(5)  not null, { [00000] => ville et pays naissance       }
pays_naiss        char(30)
) ;

{ cm    (communes) ------------------------------------------------------------}

create table cm
(
kn_cm             char(5)  not null,
kn_dp             char(2)  not null,
t_libcm             char(38) not null
) ;

{ cp    (codes postaux) -------------------------------------------------------}

create table cp
(
c_cp             char(5)  not null,
c_cm             char(5)  not null,
t_libcp           char(30) not null
) ;

{ dp    (départements) --------------------------------------------------------}

create table dp
(
c_dp             char(2)  not null,
t_libdp            char(30) not null
c_rg             char(2)  not null,
) ;

{ rg    (régions) -------------------------------------------------------------}

create table rg
(
c_rg             char(2)  not null,
t_librg            char(30) not null
) ;

[Invité]

À propos de tes nouveaux noms de données…

Je tiens à mes préfixes (typologiques) devant chaque mnémonique de colonne.

J'y ajoute systématiquement la désignation en clair (dans la légende, sous access, ou en description, pour les autres SGBD).

Lors notamment de requêtes depuis des sources externes, je ne me pose pas 36 questions à enquêter sur la concordance de type entre la colonne source et la colonne cible.

Préfixer un nom de donnée d’une lettre représentant sa typologie pollue les développements :

1. en ajoutant inutilement deux caractères au nom de donnée,
2. en ajoutant une contrainte de gestion de tes développements,
3. en singularisant ta programmation non conventionnelle,
4. en contraignant une tierce personne à essayer de comprendre ta démarche alambiquée.

Des problèmes de concordance typologique entre une colonne source et une colonne cible se règlent au cas par cas, on est sensé savoir ce que l’on fait, quelles données on manipule.

Lorsque tu nommes tes données « kn_pers », « ak_matri », « kn_cm », je m’interroge à propos de ton « k » et ce n’est pas normal de devoir s’interroger.

À propos de tes noms de tables…

Lorsque tu nommes ta table « t_pers_prs », je m’interroge sur le préfixe « t_ » et le suffixe « _prs » qui semble être un alias de "pers". Si le préfixe « t_ » signifie « table », je ne vois pas l’intérêt. Quant-au suffixe « _prs », qu’apporte-t-il à part un questionnement pour une tierce personne ?

De même pour ta table « tr_grade_tgd2 » que tu cites dans ton autre discussion. Je présume que ton préfixe « tr_ » signifie « table référente ». Même remarque pour ton préfixe « _tr » et ton suffixe « _tgd2 ».

Dans son sketch "Les oranges" Fernand Raynaud aborde une question fondamentale en matière d’écriture : sa nécessité :

Cela dit, je ne veux pas passer pour un donneur de leçons. Je fais part simplement de mes propres bonnes pratiques qui ont fait leurs preuves. Je les évoque car j’ai pu constater que le sujet n’est pas abordé dans les formations (IUT), comme s’il appartenait à chacun de se les inventer. J’ai pu le vérifier lors d’un DUT en formation permanente. On y enseigne la technicité, pas le savoir faire. J’ignore ton cursus formation mais il semble que ce soit toujours vrai.

[martinbrait]

Ok IFA2377,
J'ouvre un autre sujet autour de la normalisation des noms en base de données
car il y a contenu à débattre !
merci, @+

[CinePhil]

J'ai parcouru vite fait la discussion...

Les tables de référence sont chez moi, comme le suggère SQLPro, des tables dont le contenu change peu, vient d'une référence externe à la BDD (nomenclatures), est destiné côté applicatif à remplir des listes et/ou de l'auto-complétion après la saisie des premiers caractères.

Cas typiques :
- tr_sexe_sex (sex_id, sex_code, sex_libelle, sex_ordre) => sex_ordre est destiné à définir l'ordre d'affichage dans une liste
- tr_civilite_cive (civ_id, civ_abreviation, civ_libelle, civ_ordre) => civ_ordre : idem ci-dessus
- tr_pays_pay (pay, id, pay_code_iso, pay_code_insee, pay_nom_original, pay_nom_francais) => pay_code_insee et pay_nom_francais pour une utilisation française mais pas obligatoire (on peut faire un héritage pour les utilisations nationales, ou autres solutions de traduction...)
- tr_region_reg (reg_id_pays, reg_numero, reg_code_local, reg_abreviation, reg_libelle) => Identification relative au pays car la notion de région existe ou peut s'appliquer sous différents noms à plusieurs pays (lander en Allemagne, generalitat en Espagne, canton en Suisse... là aussi, plusieurs modélisations sont possibles)
- tr_departement_dpt (dpt_id, dpt_numero, dpt_nom) => dpt_numero sur 3 caractères à cause des départements d'outre-mer et, bien sûr, cette notion ne s'applique qu'à la France.
- tr_ville_vil (vil_id, vil_id_pays, vil_nom_original, vil_nom_francais)
- th_ville_francaise_vlf (vlf_id_ville, vlf_id_departement, vlf_code_insee...) => Bien sûr pour une application française !
- tr_monnaie_mon (mon_id, mon_abreviation, mon_symbole, mon_nom...)
- tr_type_produit_tpr (tpr_id, tpr_code, tpr_libelle_tpr_ordre)
- tr_type_adresse_tad (tad_id, tad_code, tad_libelle, tad_ordre)
...

[martinbrait]

Les tables de référence sont chez moi, comme le suggère SQLPro, des tables dont le contenu change peu, vient d'une référence externe à la BDD (nomenclatures), est destiné côté applicatif à remplir des listes et/ou de l'auto-complétion après la saisie des premiers caractères.

Merci Philippe, pour ta réponse extrêmement explicite. Je suis maintenant beaucoup plus confiant, dans ma mise en œuvre des bonnes pratiques !