La forme particulière d'un identifiant

Version imprimable

05/06/2023, 15h51
Affouda

La forme particulière d'un identifiant

Je veux que mes identifiants commence par
PL-1 et le chiffre est incrémenter de façon automatique à chaque insertion.soit PL-2 ,PL-3 ...Je ne sais même pas quel question posée sur les moteur de recherche pour avoir une information allant dans ce sens.
Merci d' avance.

Salut Affouda.

Je réponds à ta question, même si je trouve cela idiot de procéder ainsi.
Voici un exemple où la construction de la colonne rang se fait à partir de deux autres colonnes.

Code:

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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
drop table if exists `test`
--------------
 
--------------
CREATE TABLE `test`
( `annee`  smallint unsigned  NOT NULL,
  `seq`    integer  unsigned  NOT NULL,
  `val`    char(10)               NULL default NULL,
  `rang`   char(10) generated always as (concat(annee, right(concat('0000000', cast(seq as char(6))),6))) stored NOT NULL PRIMARY KEY
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP TRIGGER IF EXISTS `next`
--------------
 
--------------
CREATE TRIGGER `next`
BEFORE INSERT ON `test`
FOR EACH ROW
BEGIN
  set new.seq = ifnull((select max(seq)+1 from test where annee = new.annee), 1);
END
--------------
 
--------------
COMMIT
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
insert into `test` (`annee`,`val`) values (2015,'un'),(2015,'deux'),(2015,'trois'),(2016,'quatre'),(2016,'cinq'),(2017,'six'),(2017,'sept')
--------------
 
--------------
select * from  `test`
--------------
 
+-------+-----+--------+------------+
| annee | seq | val    | rang       |
+-------+-----+--------+------------+
|  2015 |   1 | un     | 2015000001 |
|  2015 |   2 | deux   | 2015000002 |
|  2015 |   3 | trois  | 2015000003 |
|  2016 |   1 | quatre | 2016000001 |
|  2016 |   2 | cinq   | 2016000002 |
|  2017 |   1 | six    | 2017000001 |
|  2017 |   2 | sept   | 2017000002 |
+-------+-----+--------+------------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Pour quelle raison as tu besoin d'avoir un identifiant selon ce format ?
As tu d'autres tables ayant un identifiant du même genre ?
Par exemple, "ta-1" ou rg-2" ?

Cordialement.
Artemus24.
@+

06/06/2023, 03h55
Affouda

Merci pour ta réponse, vous m' avez donné satisfaction.
06/06/2023, 04h08
Affouda

Avec cet identifiant le client pourra facilement opérer des transactions sur son compte et la marge d' erreur sera plus ou moins null
Identifiant sera composé des initiales du nom ,de l' année de naissance, département,les deux dernier chiffre de l' année de création du compte plus une auto incrémentation.
Composer un identifiant de la sorte est il idiot.

Salut Affouda.

Tel que vous le faites, oui.

Un identifiant sert à rendre unique une ligne dans une table.
Une colonne auto incrémenté suffit largement et c'est ce que vous devez faire.

Par contre, vous avez besoin d'une sélection multicritère pour trouver votre client.
Par exemple, le numéro de l'insee est multicritère.

Vous créez une table avec tous vos critères.
Chaque critère devra être une colonne.
La clef primaire sera composé de tous ces colonnes.
En plus, au cas où il y aurait des doublons, vous créez une colonne pour faire la distinction.

Cette clef primaire sera utilisé uniquement pour trouver un autre identifiant qui sera utilisé par ailleurs dans toutes vos autres tables.

Voici ce que je vous propose :

Code:

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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `initiale`    char(02)          NOT NULL,
  `naissance`   smallint unsigned NOT NULL,
  `departement` smallint unsigned NOT NULL,
  `compte`      tinyint  unsigned NOT NULL,
  `rang`        integer  unsigned NOT NULL,
  `lib`         varchar(255)      NOT NULL,
  `clef`        integer unsigned  NOT NULL auto_increment,
  primary key (`initiale`,`naissance`,`departement`,`compte`,`rang`),
  unique `fk` (`clef`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP TRIGGER IF EXISTS `increment`
--------------
 
--------------
CREATE TRIGGER `increment`
BEFORE INSERT ON `test`
FOR EACH ROW
BEGIN
  SET NEW.rang=ifnull((select max(rang)+1 from `test` where initiale=NEW.initiale and naissance=NEW.naissance and departement = NEW.departement and compte = NEW.compte),1);
END
--------------
 
--------------
insert into `test` (`initiale`,`naissance`,`departement`,`compte`,`lib`) values
  ('AB', 2000, 25, 17, 'Un'),
  ('XY', 2007, 06, 11, 'Deux'),
  ('AB', 2000, 25, 17, 'Trois'),
  ('XY', 2007, 06, 11, 'Quatre'),
  ('AB', 2000, 25, 17, 'Cinq'),
  ('XY', 2007, 06, 11, 'Six')
--------------
 
--------------
select * from `test`
--------------
 
+----------+-----------+-------------+--------+------+--------+------+
| initiale | naissance | departement | compte | rang | lib    | clef |
+----------+-----------+-------------+--------+------+--------+------+
| AB       |      2000 |          25 |     17 |    1 | Un     |    1 |
| AB       |      2000 |          25 |     17 |    2 | Trois  |    3 |
| AB       |      2000 |          25 |     17 |    3 | Cinq   |    5 |
| XY       |      2007 |           6 |     11 |    1 | Deux   |    2 |
| XY       |      2007 |           6 |     11 |    2 | Quatre |    4 |
| XY       |      2007 |           6 |     11 |    3 | Six    |    6 |
+----------+-----------+-------------+--------+------+--------+------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

La colonne "clef" sera unique puisque auto incrémenté.
C'est cette colonne que vous devrez utiliser dans vos autres tables pour identifier votre client.

Quand à la primary key, elle sera composée de tous vos critères.

Cordialement.
Artemus24.
@+

06/06/2023, 17h02
Affouda

Merci beaucoup tu es un champion
08/06/2023, 14h38
escartefigue

Bonjour,

Un identifiant d'entité au niveau conceptuel devient clef primaire au niveau tabulaire.

Or, les clef primaires se propagent dans les tables liées sous forme de clefs étrangères, pour que le SGBD puisse vérifier l'intégrité de la base de données (absence d'orphelins et absence de parents sans enfants quand l'enfant est obligatoire).

C'est la raison pour laquelle les identifiants (et donc les clefs primaires) doivent être stables, car si leur valeur devait changer, il faudrait propager la nouvelle valeur dans toutes les occurrence de clefs étrangères correspondantes, ce qui peut représenter énormément de lignes dans une table à forte volumétrie.

Afin de garantir cette stabilité, on choisit le plus souvent un identifiant technique, dénué de sens.
Le plus simple étant de laisser le SGBD se débrouiller pour attribuer cette valeur unique. Dans MySQL, ça se traduit par une colonne de type AUTO_INCREMENT.

À l'inverse, choisir un identifiant qui commence par les initiales du nom tel que proposé, implique que s'il y a une erreur orthographique de nom qui engendre une modification, on devra modifier l'identifiant, c'est fâcheux ! Même combat en cas de mariage par exemple.

De plus l'identifiant, et donc les clefs primaires et étrangères, sont les colonnes les plus fréquemment utilisées pour réaliser les jointures.
Or, les type de données CHAR ou VARCHAR nécessaires pour stocker des initiales sont à la fois plus encombrants et donc moins performants à nombre de combinaisons égales que de l'integer, mais aussi, ils sont sensibles à la collation.

Un identifiant doit être unique, non nullable, concis, irréductible et stable.

Rien ne vous empêche d'ajouter les initiales concaténées à un identifiant de type integer lors de la restitution à l'utilisateur si ça peut vous être utile.
08/06/2023, 14h45
escartefigue
@Artemus24

Citation:

Envoyé par Artemus24

Salut Affouda.
Voici ce que je vous propose :
[code=mysql]
--------------
CREATE TABLE `test`
( `initiale` char(02) NOT NULL,
`naissance` smallint unsigned NOT NULL,
`departement` smallint unsigned NOT NULL,
`compte` tinyint unsigned NOT NULL,
`rang` integer unsigned NOT NULL,
`lib` varchar(255) NOT NULL,
`clef` integer unsigned NOT NULL auto_increment,
primary key (`initiale`,`naissance`,`departement`,`compte`,`rang`),
unique `fk` (`clef`)
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED

C'est une très mauvaise idée :
- cette PK est instable car basée sur des colonnes fonctionnelles : initiales en 1re colonne qui peuvent changer, département qui peut changer... ;
- cette PK est sensible à la collation (présence de colonne de type char) ;
- cette PK est inutilement encombrante : elle pèse 11 octets alors qu'un integer simple n'en pèse que 4 (et un bigint si besoin en pèse 8).
Encore une fois, il ne faut jamais utiliser de colonne potentiellement instable comme composante d'une PK !

Si l'utilisateur a besoin qu'on ajoute ses initiales devant son identifiant, on le fait au moyen d'une vue ou par traitement et le tour est joué, mais on ne pollue pas la PK.
08/06/2023, 20h48
Artemus24

Salut Escartefigue.

Je veux bien, mais comment vas-tu entrer dans ta base de données ?
J'ai bien parlé d'une sélection multi critères.

Cordialement.
Artemus24.
@+
09/06/2023, 07h27
escartefigue
"comment vas tu entrer dans ta BDD" c'est à dire ?
Quoi qu'il en soit, peut importe : que la question se rapporte aux droits d'accès (GRANT/REVOKE), au type d'accès (en lecture, en mise à jour), les fondamentaux que j'ai rappelés concernant une clef primaire (PK, Primary Key) sont incontournables.

L'identifiant (stade conceptuel) qui devient clef primaire (stade SQL) doit être :
- unique ;
- non "nullable" ;
- irreductible (une partie de l'identifiant ne peut suffire à déterminer une occurrence ;
- stable (et pour ce faire, il est recommandé qu'il soit asémantique) ;
- et facultativement, mais c'est fortement recommandé pour des raisons de performances, concis.
Donc pas question d'inclure les initiales du nom, le code département ou tout autre élément susceptible de changer comme composante de la PK
09/06/2023, 11h00
escartefigue

En complément, par rapport à ceci :

Citation:

Envoyé par Artemus24

Vous créez une table avec tous vos critères.
Chaque critère devra être une colonne.
La clef primaire sera composé de tous ces colonnes.
En plus, au cas où il y aurait des doublons, vous créez une colonne pour faire la distinction.

Ça n'a pas de sens : une PK est par définition unique et not null, il ne peut pas y avoir de doublon, "vous créez une colonne pour faire la distinction" :weird:

Citation:

Envoyé par Artemus24

Cette clef primaire sera utilisé uniquement pour trouver un autre identifiant qui sera utilisé par ailleurs dans toutes vos autres tables.
C'est cette colonne que vous devrez utiliser dans vos autres tables pour identifier votre client.

Non : c'est la clef primaire qu'on doit retrouver comme clef étrangère dans les autres tables, c'est la seule redondance autorisée dans une BDD.
Tout autre attribut unique ou pas ne doit pas être propagé dans les autres tables.

Citation:

Envoyé par Artemus24

Quand à la primary key, elle sera composée de tous vos critères.

Comme expliqué précédemment, surtout pas.

Ces remarques sont valables pour tout SGBD et encore plus pour MySQL qui confond allègrement clef primaire et index cluster.
Ce faisant, affubler l'index cluster de valeurs modifiables c'est exposer la table à des désorganisations engendrées par ces modifications.
Alors qu'avec une PK stable et donc, pour MySQL, un index cluster table, cet inconvénient supplémentaire disparait.

Bref, il ne faut pas réinventer le principe de base des contraintes d'intégrité qui consiste à propager la PK sous forme de FK, restons simples... et performants ;)