Discussion :

[BibouHB]

Bonjour chère développeurs, je me tourne vers vous pour un la résolution d'un problème,
j'aimerais avoir une codification spéciale pour une clé primaire , elle doit commencer par "A" suivi par un nombre qui s'auto incrémente sur Cinque position "00001" a chaque foi i+1 => "A00001" "A00002" ainsi de suite ...pouvez vous m'aider ?
Merci.

[SQLpro]

Non, parce que c'est d'une haute stupidité ! En effet une clé primaire ne devrait avoir AUCUNE sémantique et donc être totalement arbitraire ! En faisant tout le contraire vous serez tôt au tard dans la merde.

A +

[BibouHB]

Merci de m'avoir répondu, cela dit vous auriez pu éviter ce type d'argument, et me proposer une autre solution, si je suis ici, c'est bien pour apprendre...
"الحبقْ من عندنا سْبَقْ"
A+.

[escartefigue]

Bonjour,

Il ne faut pas polluer la clef primaire mais simplement construire votre identifiant par concaténation des éléments qui vous intéressent lors de la restitution.
Construire la clef primaire en l'affublant d'un préfixe ou d'un suffixe de type char ou varchar ne présente aucun intérêt et pénalise les perfs.

Pour la clef primaire utilisez une IDENTITY (AUTO_INCREMMENT pour MySQL)

Attention toutefois :

• une colonne de type IDENTITY peut présenter des trous de numérotation (lignes non commitées, incrément/décrément supérieur à 1, valeur de départ différente de un...)
• il ne faut pas déduire une chronologie à partir de ce type de colonnes, il n'y a pas de garantie que les valeurs d'IDENTITY soient insérées dans l'ordre chronologique, même si c'est le plus souvent le cas

Exemple :

Code :

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

1
2
3
4
5
6
7
8
9
10
create table TAB0(  IDENT integer not null auto_increment primary key
                  , TXT1  char(10))
                  engine=innodb ;
insert into TAB0 (TXT1) values ('abcdef');
insert into TAB0 (TXT1) values ('TOTO');
insert into TAB0 (TXT1) values ('coucou');
insert into TAB0 (TXT1) values ('ça va ?');  
select concat('A', repeat('0', 5-length(char(IDENT))), IDENT) as machin
     , TXT1
from TAB0 ;

Résultat :

Code :

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

1
2
3
4
5
machin	TXT1
A00001	abcdef
A00002	TOTO
A00003	coucou
A00004	ça va ?

[az6muth]

@Artemus24 : ça répond à la demande mais c'est une mauvaise idée : pk polluée par une constante alpha ==> plus couteux qu'une pk integer et sans intéret

[manzeki]

Utilisez une cléf primaire numérique.
Maîtriser les bases de la normalisation des bases de données, cela vous évitera bien des désagréments.
Inutile de postfixer ou préfixer les noms des colonnes par le nom de table (_Empl...)
Écrivez simplement vos tables et rendez le tout lisible.

Vu qu'un employé peut avoir plus d'un numéro de téléphone, alors créez une table telephones.

Table employés

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
CREATE TABLE employes (
	employe_id integer auto_increment primary key,
	inscription DATETIME DEFAULT CURRENT_TIMESTAMP,
	matricule varchar(50),
	sexe ENUM('m', 'f')
	nom varchar(50),
	prenom varchar(50),
	naissance date,
	naissance_lieu varchar(50),
	fonction varchar(256),
	email varchar(50),
	addresse varchar(256),
);

Table téléphones

Code :

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

1
2
3
4
5
6
 
CREATE TABLE telephones (
	telephone_id integer auto_increment primary key,
	employe_id integer references employes(employe_id),
	telephone varchar(20);
);

[escartefigue]

Bonjour Manzeki

Utilisez une cléf primaire numérique.

Sur ce point, tout le monde sera d'accord, je l'espère, à l'orthographe près : soit clef avec un "f" terminal et sans accent (pour rappel de sa racine latine Clava) soit clé, mais pas cléf

Maîtriser les bases de la normalisation des bases de données, cela vous évitera bien des désagréments.

La aussi, pas de débat

Inutile de postfixer ou préfixer les noms des colonnes par le nom de table (_Empl...)

Pas d'accord, sous réserve d'avoir toute latitude pour nommer les objets bases de données (c'est rarement le cas en entreprise), utiliser un préfixe ou suffixe court est bigrement pratique, car ça évite les homonymes.
Par exemple, dans une BDD, il y a pléthore de dates de début et de fin dans les différentes tables. Si un préfixe ou suffixe permet de les distinguer d'emblée, c'est pratique à l'usage.
Avantage supplémentaire : pas de risque de percussion avec un mot réservé SQL grâce à cet ajout.
Encore un avantage : on sait d'emblée de quelle table provient une FK sans même besoin de consulter le DDL CREATE TABLE .
Bref, quand on a le choix, chacun fait comme il veut, mais pour mon usage personnel, j'applique systématiquement un préfixe court (un par table) à chaque attribut.

Vu qu'un employé peut avoir plus d'un numéro de téléphone, alors créez une table telephones.

En général oui, mais sous réserve que les règles de gestion le stipulent, peut être que dans ce contexte particulier, tout employé n'a qu'un et un seul téléphone
La remarque vaut aussi si un employé peut ne pas avoir de numéro de téléphone : externaliser le numéro évite d'avoir des attributs nullables dans la table employé.


Pour le reste, s'il s'agit d'améliorer le script, il faut aller jusqu'au bout de la démarche en notant également que :

• une adresse courriel peut aller jusqu'à 255 caractères, donc du varchar(50) est inadapté
• on pourrait faire la même remarque que pour les téléphones : selon les règles de gestion, un employé peut avoir ou pas zéro à plusieurs courriel, auquel cas il faut également externaliser les adresses courriel.
• le lieu de naissance ne dépend pas fonctionnellement de l'identifiant de l'employé, c'est un viol de la 3^e forme normale. Il doit donc être externalisé par un lien FK vers les communes pour éviter les erreurs et les redondances.
• idem pour les fonctions
• les adresses françaises et étrangères sont normalisées. Une seule ligne de type varchar(255) est complètement inadapté
• selon le contexte (règles de gestion), il est possible d'avoir plusieurs adresses (de livraison, de facturation...), en ce cas, il faut également les externaliser.

[SQLpro]

En ce qui concerne le préfixage des colonnes, il y a un autre avantages, extrêmement important et non négligeable... Retrouver ou, dans les multiples objets de la base (tables, vues, fonctions, procédures déclencheurs, synonymes, contraintes....), figure cette colonne parce que l'on doit pouvoir connaitre l'impact par exemple d'un changement de type !

Comme je pratique l'audit de base de données régulièrement, depuis plusieurs décennies, voici un cas concret que j'ai systématiquement :
Passer les dates du type DATETIME (quasi obsolète) au type DATETIME2 dans SQL Server...
Bon courage pour faire cela s'il ne vous est pas possible de faire une sorte de GREP pour trouver, d'un coup, tous les objets concernés et les requêtes applicatives !!!
Un de mes client y a passé plus d'un an...

A +

[SQLpro]

Pour le reste je rappelle le modèle pour les personnes :

https://blog.developpez.com/exercice...n_de_personnes

et les adresses :

https://blog.developpez.com/exercice..._d_une_adresse

A +

[escartefigue]

Frédéric,

Il me semble que je t'en avais déjà parlé, quand j'ouvre le lien adresse, voici ce qui s'affiche :

Pièce jointe 588512

C'est illisible

Même combat avec firefox ou chrome, peu importe le zoom, c'est pareil

[SQLpro]

Clic droit, ouvrir l'image

A +

[manzeki]

Bonjour Manzeki
Sur ce point, tout le monde sera d'accord, je l'espère, à l'orthographe près : soit clef avec un "f" terminal et sans accent (pour rappel de sa racine latine Clava) soit clé, mais pas cléf

La aussi, pas de débat

Pas d'accord, sous réserve d'avoir toute latitude pour nommer les objets bases de données (c'est rarement le cas en entreprise), utiliser un préfixe ou suffixe court est bigrement pratique, car ça évite les homonymes.
Par exemple, dans une BDD, il y a pléthore de dates de début et de fin dans les différentes tables. Si un préfixe ou suffixe permet de les distinguer d'emblée, c'est pratique à l'usage.
Avantage supplémentaire : pas de risque de percussion avec un mot réservé SQL grâce à cet ajout.
Encore un avantage : on sait d'emblée de quelle table provient une FK sans même besoin de consulter le DDL CREATE TABLE .
Bref, quand on a le choix, chacun fait comme il veut, mais pour mon usage personnel, j'applique systématiquement un préfixe court (un par table) à chaque attribut.

En général oui, mais sous réserve que les règles de gestion le stipulent, peut être que dans ce contexte particulier, tout employé n'a qu'un et un seul téléphone
La remarque vaut aussi si un employé peut ne pas avoir de numéro de téléphone : externaliser le numéro évite d'avoir des attributs nullables dans la table employé.


Pour le reste, s'il s'agit d'améliorer le script, il faut aller jusqu'au bout de la démarche en notant également que :

• une adresse courriel peut aller jusqu'à 255 caractères, donc du varchar(50) est inadapté
• on pourrait faire la même remarque que pour les téléphones : selon les règles de gestion, un employé peut avoir ou pas zéro à plusieurs courriel, auquel cas il faut également externaliser les adresses courriel.
• le lieu de naissance ne dépend pas fonctionnellement de l'identifiant de l'employé, c'est un viol de la 3^e forme normale. Il doit donc être externalisé par un lien FK vers les communes pour éviter les erreurs et les redondances.
• idem pour les fonctions
• les adresses françaises et étrangères sont normalisées. Une seule ligne de type varchar(255) est complètement inadapté
• selon le contexte (règles de gestion), il est possible d'avoir plusieurs adresses (de livraison, de facturation...), en ce cas, il faut également les externaliser.

Vous inventez ou vous confirmez que clef signifie ou a pour racine clava ?

Je suis désolé, je ne me plais pas à faire des antithèses sur les reponses des gens.
Je préfère contribuer et aider les personnes qui posent les questions.
Je m'abtiens aussi de ne pas importuner ou insulter des inconnus sur des forums, par prudence.
Qu'à cela ne tienne chacun assume ce qu'il débite sur internet.

Donc vos antithèses, débats et votre latin, gardez les pour vous.

Pour ce qui est du sujet proprement dit :
Çela fait plus de 15 ans que je travaille sur les bases de données.
Sachez lire entre les lignes, vos commentaires sur la taille des varchar ou sur la taille des emails ne sont pas pertinents à mes yeux.
Expliquez correctement au concerné pourquoi on choisit une taille et pas une autre pour un varchar.

Je suis sûr qu'à votre premier post, vous n'avez pas contasté qu'il utilisait deux colonnes pour stocker ses numéros de téléphone.
Une personne peut avoir 0, 1, 2, 3.. numéros de téléphone. C'est trivial de mettre le tout dans une table à part.
Quid des emails, lieu ou ville de naissance, adresse (tout dépendra de comment est structurée l'adresse du pays).

C'est une piste que j'ai émise pour alerter le concerné, vous pouvez compléter le script pour lui et non pour moi.

Pour ce qui est de préfixer les noms des tables ou vues avec des tbl_, ou des noms colonnes avec le nom de la table, je ne le fais jamais, c'est une abbération pour moi. En plus c'est pas une norme. C'est comme si on préfixait les méthodes d'une classe Java avec des fn_.....

Quand on écrit correctement les noms de ses objets, je me demande comment on peut confondre les noms des tables avec ceux des fonctions.
Sinon à quoi sert de créer des bases de données, des alias, les schemas .... ?
A partir d'un certain niveau d'expertise, je pense qu'il est rare de confondre un mot réservé avec autre chose. Et sinon, à quoi servent les tests unitaires.
Même un éditeur comme NotePad++ avec de bons plugins nous évite de commettre ce genre d'erreurs.

[escartefigue]

Bonjour Manzeki

Vous inventez ou vous confirmez que clef signifie ou a pour racine clava ?

Clef, clavier, clavecin, claviforme, clavicule... ont la même racine latine : clavem, clavis (selon les différentes déclinaisons)
Cf. https://cnrtl.fr/definition/academie9/clef
et dans wiki ici https://fr.wiktionary.org/wiki/clava
Pièce jointe 588587

Je m'abtiens aussi de ne pas importuner ou insulter des inconnus sur des forums, par prudence.

Où vous-ai je insulté ?

Donc vos antithèses, débats et votre latin, gardez les pour vous.

Si vous ne supportez pas la contradiction, évitez les débats et donc notamment les forums
Vous recommandez, fort justement, de respecter les formes normales, or, vous proposez un script qui viole la 3e forme normale (attribut non dépendant de l'identifiant)
Il est donc tout à fait normal que j'en fasse mention.

vos commentaires sur la taille des varchar ou sur la taille des emails ne sont pas pertinents à mes yeux.

Mentionner que vous proposez une longueur insuffisante pour recevoir une donnée n'est pas pertinent ?
Mentionner que vous proposez de stocker une adresse sur une seule ligne de 255 caractères n'est pas pertinent, alors que vous pouvez en quelques clics trouver les normes d'adresses françaises et étrangères sur le web?

Je suis sûr qu'à votre premier post, vous n'avez pas contasté qu'il utilisait deux colonnes pour stocker ses numéros de téléphone.
Une personne peut avoir 0, 1, 2, 3.. numéros de téléphone. C'est trivial de mettre le tout dans une table à part.
Quid des emails, lieu ou ville de naissance, adresse (tout dépendra de comment est structurée l'adresse du pays).

Vous m'avez décidément très mal lu ! Je propose justement d'externaliser non seulement les téléphones si requis, mais aussi les adresses puisqu'elles peuvent elles aussi être multiples...

Pour ce qui est de préfixer les noms des tables ou vues avec des tbl_, ou des noms colonnes avec le nom de la table, je ne le fais jamais, c'est une abbération pour moi. En plus c'est pas une norme. C'est comme si on préfixait les méthodes d'une classe Java avec des fn_.....

C'est une abération pour vous, j'en prends bonne note, cela étant j'ai apporté des arguments, d'autres contributeurs ont surenchéri dans le même sens, libre à vous de faire ce qui vous convient.

Quand on écrit correctement les noms de ses objets, je me demande comment on peut confondre les noms des tables avec ceux des fonctions.
Sinon à quoi sert de créer des bases de données, des alias, les schemas .... ?
A partir d'un certain niveau d'expertise, je pense qu'il est rare de confondre un mot réservé avec autre chose. Et sinon, à quoi servent les tests unitaires.
Même un éditeur comme NotePad++ avec de bons plugins nous évite de commettre ce genre d'erreurs.

Faites quelques recherches dans ce forum et vous trouverez de nombreux sujets dans lequels on trouve des requêtes avec des crochets autour des noms des tables et des colonnes justement à cause de l'utilisation de noms réservés.

[SQLpro]

Je confirme les propos de mon collègue escartefigue et j'ajouterais...

...
Pour ce qui est de préfixer les noms des tables ou vues avec des tbl_, ou des noms colonnes avec le nom de la table, je ne le fais jamais, c'est une abbération pour moi. En plus c'est pas une norme. C'est comme si on préfixait les méthodes d'une classe Java avec des fn_.....

Quand on écrit correctement les noms de ses objets, je me demande comment on peut confondre les noms des tables avec ceux des fonctions.
Sinon à quoi sert de créer des bases de données, des alias, les schemas .... ?...

Visiblement vous ne savez pas du tout ce qu'est une base de données ni un langage de requêtes comme SQL.
Le langage SQL n'a rien à voir avec les langages objets dont je suis sur que vous maîtriser la technique. Donc, les concepts comme "classe" ou "objet" n'existe pas en SQL. Un nom peut indifféremment être attribué à une colonne, une contrainte, une table, une vue, une fonction, une procédure, un déclencheur, un index... Et un même nom peut se retrouver sur différents éléments de la base.... Et il n'existe aucun moyen simple d'interroger le SGBDR pour savoir à quel type d'objte un nom donné fait référence... De plus par le biais des alias, un nom peut être transformé en un autre (par exemple un nom de colonne dans une vue, une vue dans une fonction, une fonction dans une procédure.... et on se retrouve avec 4 niveaux d'empilement d'alias différents !) C'est pour cela qu'il est si important d'adopter une codification précise des noms des objets et de s'y tenir !

Mais vous êtes libre de faire les pires conneries....

A +

[fsmrel]

A propos des clefs :

Pour mémoire, selon le célèbre « Gaffiot » que j’utilisais dans les années cinquante, « clavis » se traduit par « clef », « clava » par « massue » et « clavus » par « clou ».

Pour sa part, la clé primaire pourrait être la « clavis prima inter claves ».

In Latina Lingua:

CREARE TABULAM PERSONA
(
        personaId        NUMERUS
      , personaNomen     LITTERAE(XXXII)
      , personaNatalis   TEMPUS
    , CLAVIS PRIMA (personaId)
) ;

Bon, c’est assez approximatif... En tout cas, pour le téléphone des personnes, le Gaffiot est muet, donc je ne propose rien...

Clavis selon Gaffiot :

[fsmrel]

Bonsoir,

le lieu de naissance ne dépend pas fonctionnellement de l'identifiant de l'employé

Dans la table employes (post #6), de quel attribut (ou ensemble d’attributs) non clé dépend donc ce lieu de naissance ? A supposer qu’un tel attribut existe, par application de l’axiome d’Armstrong de transitivité, alors il existe bien la dépendance fonctionnelle {employe_id} → {naissance_lieu}.

En passant, je fais observer que l’attribut Matricule de la table employes doit être déclaré UNIQUE (clé alternative), sinon plusieurs employés pourraient se partager le même matricule...

[escartefigue]

L'identifiant du lieu a toute sa place dans la table employé, mais pas le lieu, sinon c'est la porte ouverte à toutes les fenêtres.

Pièce jointe 588672

[SQLpro]

Là (lieu de naissance) je rejoins escartefigue, et c'est plus qu'important, car PARIS c'est ou ???? Au Texas bien sur !!!

[fsmrel]

Bonsoir Capitaine,

L'identifiant du lieu a toute sa place dans la table employé, mais pas le lieu

On est évidemment bien d’accord que la table des employés doit contenir l’identifiant du lieu et non pas le lieu proprement dit, donc que l’on mette en oeuvre une clé étrangère permettant de référencer une table des lieux : on est ici dans le domaine, je dirais l’art, de la saine modélisation, laquelle comme tu l’illustres fort bien avec tes deux modèles doit bien sûr commencer au niveau conceptuel (MCD) avant que l’on en arrive au niveau « logique » (MLD). Tes nombreux MCD/MLD chez DVP et tes recommandations auxquelles je souscris en sont la confirmation.

Mais conviens que tout ceci n’a pas grand-chose à voir au plan formel avec les dépendances fonctionnelles (lesquelles sont à manipuler au moyen des axiomes d’Armstrong qui ont vu le jour en 1974) et la 3NF (troisième forme normale). La table des employés est certes à revoir quant au lieu, mais en l’état elle respecte la 3NF ! DF et 3NF relèvent de la théorie de la normalisation des bases de données, branche des mathématiques appliquées, car tout y est axiomes, lemmes et théorèmes, ce qui échappe totalement à Merise car son objet n’est pas là. Pour sa part, Yves Tabourier (in De l’autre côté de Merise, page 89) avertit les auteurs de l’ouvrage incontournable des années 80/90⁽¹⁾ du danger que présente leur appellation « dépendance fonctionnelle ». Il est évident que ces auteurs n’avaient qu’une connaissance approximative de la théorie de la normalisation, initialement décrite par Codd, mais ils l’ont quand même superficiellement reprise (voir leur définition de la Normalisation dans l’ouvrage incontournable).

Quand les auteurs de l’ouvrage incontournable parlent de « leurs » dépendances fonctionnelles, il s’agit en réalité de contraintes d’unicité⁽³⁾ entre entités-types (Nanci utilise plus volontiers le terme de « contrainte d’intégrité fonctionnelle »), et bien entendu les indispensables axiomes d’Armstrong pour pouvoir normaliser rationnellement n’y ont pas leur place : par exemple que signifierait pour eux l’utilisation de l’axiome d’augmentation (très précieux pour moi !) : si X → Y, alors XZ → YZ... (Au sujet des axiomes je te renvoie à la page 384 de l’ouvrage de Jeffrey Ullman⁽²⁾).

Pardonne-moi, mais tu auras compris que je suis d’une vigilance extrême dès que l’on parle de normalisation et de 3NF (et plus si affinité ).
____________________

⁽¹⁾ H. Tardieu, A. Rochfeld, R. Colletti - La Méthode Merise Tome 1 Principes et outils, aux Editions d’Organisation (1984).

⁽²⁾ Jeffrey Ullman - Principles of Database and Knowledge-Base Systems, Volume I

⁽³⁾ Je rappelle les définitions des contraintes d’unicité données par le Groupe 135 de l’Afcet - Journée du 15 novembre 1990 :

UNICITÉ COMPLÈTE (sur relation, contrainte, symbole → ou (I) ou (F)).

Une contrainte d’unicité complète est définie sur la relation R, appelée sa "portée", avec pour "cible" l’un des individus C reliés par R, si et seulement si l’occurrence de C est déterminée de façon unique lorsque l’on connaît les occurrences des autres individus reliés par R.

Remarque 1

Si l’un (des) individu(s) autre(s) que la cible joue deux ou plusieurs rôles dans la relation R, il intervient autant de fois dans la détermination de C qu’il joue de rôles dans R.

Remarque 2

Si l’individu cible joue deux ou plusieurs rôles dans la relation R, la cible doit préciser via quel rôle Cr l’occurrence de C est déterminée, les autres rôles de C dans R étant vus comme s’il agissait d’autres individus.

Remarque 3

Si plusieurs relations R, R’... peuvent être composées sans ambiguïté, la contrainte peut avoir pour "portée" cette composition, et ce qui est dit plus haut de R s’entend alors de cette composition, et ce qui est dit des individus reliés par R s’entend des individus reliés par cette composition si seuls certains des individus reliés par R, R’, ..., ou certains des rôles qu’ils y jouent, sont à retenir, ils forment le "pivot" qui doit être explicité (la cible peut alors faire partie du pivot).

UNICITÉ INCOMPLÈTE (sur relation : symbole (I)ou (F))

Une contrainte d’unicité incomplète est définie .sur la relation R, appelée sa "portée", avec pour "cible" l’un des individus C reliés par R, si et seulement si l’occurrence de C est déterminée de façon unique lorsque l’on connaît les occurrences d’une liste (S, S’, ...) d’individus ("source") reliés par R et formant avec C le "pivot" de la contrainte.

Remarque 1

Une contrainte d’unicité incomplète conduit normalement à la décomposition de sa portée et n’a donc pas à être représentée, sauf si elle porte sur la restriction d’une relation à des sous-types ; en effet dans ce cas elle ne permet pas la décomposition de la relation "principale".

Remarque 2

Si un (des) individu(s) de la source joue deux ou plusieurs rôles dans la relation R, il faut préciser via lesquels de ces rôles il intervient dans la détermination de C.

Remarque 3

Si l’individu cible joue deux ou plusieurs rôles dans la relation R, la cible doit préciser via quel rôle Cr l’occurrence de C est déterminée, les autres rôles de C dans R étant vus comme s’il agissait d’autres individus.