cardinalité 0,N ou 1,N ?

**laurentSc** · 02/07/2021, 15h34

Bonjour,

je me demande s'il est important de choisir entre ces 2 cardinalités au niveau du MCD, sachant que si je compare le SQL généré dans les 2 cas par Looping, il est identique...Du coup, quel intérêt ?

exemple de MCD :
Nom : cardinalite.png
Affichages : 1942
Taille : 12,1 Ko

Nom : cardinalite.png
Affichages : 1942
Taille : 12,1 Ko

En gros, c'est simple : un utilisateur soumet des tickets ; j'ai choisi 1,N et non 0,N car s'il soumet 0 ticket alors il n'existe pas. Ai-je bien fait ?

De toute façon, SQL généré avec 0,N :

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 US_user(
   US_ident INT UNSIGNED AUTO_INCREMENT,
   US_firstname VARCHAR(50),
   US_lastname VARCHAR(50),
   PRIMARY KEY(US_ident)
);
 
CREATE TABLE TI_ticket(
   TI_ident INT UNSIGNED AUTO_INCREMENT,
   TI_num_ticket BIGINT NOT NULL,
   US_ident_SBM INT UNSIGNED NOT NULL,
   PRIMARY KEY(TI_ident),
   UNIQUE(TI_num_ticket),
   FOREIGN KEY(US_ident_SBM) REFERENCES US_user(US_ident)
);

SQL généré avec 1,N :

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 US_user(
   US_ident INT UNSIGNED AUTO_INCREMENT,
   US_firstname VARCHAR(50),
   US_lastname VARCHAR(50),
   PRIMARY KEY(US_ident)
);
 
CREATE TABLE TI_ticket(
   TI_ident INT UNSIGNED AUTO_INCREMENT,
   TI_num_ticket BIGINT NOT NULL,
   US_ident_SBM INT UNSIGNED NOT NULL,
   PRIMARY KEY(TI_ident),
   UNIQUE(TI_num_ticket),
   FOREIGN KEY(US_ident_SBM) REFERENCES US_user(US_ident)
);

Ca me semble identique, donc aucune importance...Où est-ce que je me trompe ?

**escartefigue** · 03/07/2021, 23h11

Le choix des cardinalités du MCD doit être la conséquence exacte des règles de gestion, or, choisir 1,n de l'utilisateur vers le ticket signifie que sans ticket, un utilisateur ne peut pas exister.
Il est peu probable que ça corresponde à la réalité

**laurentSc** · 04/07/2021, 10h56

Merci pour votre réponse,

j'avais montré ce MCD en le simplifiant, mais en réalité, c'est celui-là :

Nom : MCD28x900.png
Affichages : 1723
Taille : 17,4 Ko

Règles de gestion :

N°	Règle (FR)	Règle (EN)	statut
R015a	Un ticket est soumis par 1 et 1 seul utilisateur-submitter	A ticket is submitted by 1 and only 1 user-submitter
R015b	Un utilisateur-submitter soumet 1 à 1 N ticket(s)	A user-submitter submits 1 to N ticket (s)

Donc si le submitter ne crée pas un ticket, il n'existe pas...

PS : j'ai complété le tableau des règles de gestion montré ici https://www.developpez.net/forums/d2.../#post11741189. J'ai supprimé les règles qui ne contenaient que des listes d'attribut, mais je ne le donne pas car il reste 31 règles (facile de compter avec Excel

) et je pense qu'il manque encore 2 ou 3 règles.

EDIT : à mon avis, erreur de logique car s'il n'est pas submitter, il existe quand même ; OK ?
Donc, OK, on respecte les règles de gestion, mais il n'y a aucune différence au niveau du SQL généré, donc à quoi ça sert ?

**escartefigue** · 04/07/2021, 15h28

Envoyé par laurentSc

à mon avis, erreur de logique car s'il n'est pas submitter, il existe quand même ; OK ?

C'est très probable, sauf s'il s'agit d'une hot line qui crée l'occurrence de USER lors de l'appel, en même temps que l'opérateur crée l'occurrence de TICKET.
S'il s'agit d'un ticket créé au sein d'une entreprise alors toutes les personnes de l'entreprise sont crées même si elles n'ont jamais déclaré d'incident.

**laurentSc** · 04/07/2021, 15h40

OK pour la valeur de la cardinalité, par contre , et ça ?

Envoyé par laurentSc

mais il n'y a aucune différence au niveau du SQL généré, donc à quoi ça sert ?

**escartefigue** · 05/07/2021, 07h52

Si on voulait exiger la présence d'un ticket pour pouvoir créer le user, il faudrait ne "commiter" la transaction que pour insert ticket+user.

**laurentSc** · 05/07/2021, 09h40

OK,

ne connaissant commit que de nom, j'ai regardé https://baptiste-wicht.developpez.co...ver/securiser/. Même si ça parle de sql-server au lieu de MySQL, j'ai compris le principe. A mon avis, si je code les requêtes avec bon sens, pas besoin ; OK ?

**CinePhil** · 05/07/2021, 10h14

MCD :
A -1,1----associer----1,n- B

La table issue de A contient une clé étrangère référençant B.
Mais comme un B ne peut pas exister sans être associé à un A, il faut alors créer des procédures SQL d'ajout de données pour empêcher qu'un B soit créé sans association avec un A, d'une part ; et pour créer en même temps que le A un B s'il n'existe pas déjà.

La structure des tables ne change effectivement pas si on met 0,n ou 1,n côté B mais dans le cas de 0,n, on peut se contenter de simples requêtes INSERT au lieu de procédures, même si ce n'est pas forcément la meilleure pratique. Un programme devrait en effet toujours agir sur les données des tables via des procédures.

A mon avis, si je code les requêtes avec bon sens, pas besoin ; OK ?

Techniquement, puisque la structure des tables est identique quelles que soient les cardinalités que vous mettez côté B, le SGBD ne vous empêchera pas de créer un B via une requête INSERT sans qu'il soit associé à un A mais alors votre BDD devient (temporairement) incohérente avec votre modèle. Si pour une raison ou une autre le A n'est finalement pas créé, vous avez créé un B qui n'est pas associé à un A.
Par contre, si vous créez un A et que la colonne référençant B ne correspond à aucune valeur dans B, vous aurez une erreur de clé étrangère et le système empêchera la création du A sans B existant en face.

**laurentSc** · 05/07/2021, 10h42

Merci pour votre réponse, CinePhil.

Envoyé par CinePhil

on peut se contenter de simples requêtes INSERT au lieu de procédures, même si ce n'est pas forcément la meilleure pratique. Un programme devrait en effet toujours agir sur les données des tables via des procédures.

Quand vous parlez de procédures, il s'agit bien des procédures stockées ? Ne connaissant pas du tout, j'ai entré dans Google, SQL procédure, et ça m'a tout de suite proposé des articles sur les procédures stockées.

Envoyé par CinePhil

Techniquement, puisque la structure des tables est identique quelles que soient les cardinalités que vous mettez côté B, le SGBD ne vous empêchera pas de créer un B via une requête INSERT sans qu'il soit associé à un A mais alors votre BDD devient (temporairement) incohérente avec votre modèle. Si pour une raison ou une autre le A n'est finalement pas créé, vous avez créé un B qui n'est pas associé à un A.
Par contre, si vous créez un A et que la colonne référençant B ne correspond à aucune valeur dans B, vous aurez une erreur de clé étrangère et le système empêchera la création du A sans B existant en face.

En lisant ça, j'en déduis qu'il faudra d'abord créer (s'il n'existe pas déjà) B avant A. OK ?

**CinePhil** · 05/07/2021, 11h15

Quand vous parlez de procédures, il s'agit bien des procédures stockées ?

Oui.

En lisant ça, j'en déduis qu'il faudra d'abord créer (s'il n'existe pas déjà) B avant A. OK ?

Oui. Et donc il vaut mieux encapsuler ça dans une procédure stockée.

Vite fait, en MariaDB (MySQL), ça donne un truc de ce genre :

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
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
DELIMITER //
CREATE OR REPLACE PROCEDURE pi_ajout_ticket
(
    INOUT id_submitter INTEGER, -- identifiant de l'utilisateur qui soumet la demande
    IN firsname VARCHAR(50), -- Prénom de l'utilisateur
    IN lastname VARCHAR(50), -- Nom de l'utilisateur
    IN objet TEXT, -- Objet du ticket 
    OUT id_ticket INTEGER -- identifiant du ticket
)
/************************************************************************************************
Base :			pef
Objet :			Procédure
Auteur :		Philippe Leménager
Version :		V 0.1 - plemenager - 2021-07-05 - Création
Description : 	Procédure d'ajout d'un ticket avec contrôle de l'existence du submitter
Utilise :		US_user, TI_ticket
Historique :	
 
*************************************************************************************************/
BEGIN
    DECLARE ctr_exception CONDITION FOR SQLSTATE '23000'; -- Erreur de contrainte
	DECLARE EXIT HANDLER FOR ctr_exception
		BEGIN
			GET DIAGNOSTICS @nb_errors = NUMBER;
			GET DIAGNOSTICS CONDITION @nb_errors
				@errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
 
			IF LOCATE('fk_US_ident_SBM', @text) > 0 THEN 
				SET @errno = 10019;
				SET @text = 'Identifiant du submitter inconnu';
			END IF;
 
			SIGNAL SQLSTATE VALUE '23000'
			SET MYSQL_ERRNO = @errno, MESSAGE_TEXT = @text;
		END;
 
    -- Vérification de l'existence du submitter et création éventuelle
    IF id_submitter IS NULL OR id_submitter < 1 THEN
        -- id_submitter non fourni => on cherche avec le nom et le prénom
        IF firstname IS NULL OR CHAR_LENGHT(firstname) < 1
            OR lastname IS NULL OR CHAR_LENGTH(lastname) < 1 THEN 
            -- Identité non fournie => Erreur
            SIGNAL SQLSTATE VALUE '45000'
            SET MYSQL_ERRNO = 10001, MESSAGE_TEXT = 'Identifiant ou identité du submiter non fourni !';
        ELSE
            -- Recherche du submitter par son nom et son prénom
            SELECT US_ident INTO id_submitter
            FROM US_user
            WHERE US_firstname = firstname
                AND US_lastname = lastname;
 
            IF id_submitter IS NULL OR id_submitter < 1 THEN
                -- Submitter non trouvé => on le crée
                INSERT INTO US_user (US_firstname, US_lastname)
                VALUES (firsname, lastname);
 
                SET id_submitter = LAST_INSERT_ID();
            END IF;
        END IF;
    END IF;
 
    -- Création du ticket
    INSERT INTO TI_ticket(US_ident_SBM, TI_objet)
    VALUES (id_submitter, objet);
 
    SET id_ticket = LAST_INSERT_ID();
END; //
DELIMITER ;

Nota :
1) pas vérifié ni testé !
2) Je ne vois pas l'utilité de la colonne TI_num_ticket BIGINT NOT NULL en plus de la clé primaire TI_ident INT UNSIGNED AUTO_INCREMENT alors je ne l'ai pas pris en compte dans ma procédure.
3) J'ai par contre ajouté l'objet du ticket qui, il me semble, manque à votre table. Ceci dit, je n'ai pas lu toutes les discussions que vous avez lancées sur votre projet.

**laurentSc** · 05/07/2021, 16h13

Merci pour votre proposition de procédure stockée.

Comme je ne connais pas du tout, j'aurais été bien incapable de faire cela !

J'ai compris la majeure partie de votre code, néanmoins, quelques questions :

- SELECT US_ident INTO id_submitter FROM US_user : ça ressemble à une requête SQL, sauf le INTO id_submitter, où id_submitter est un paramètre INOUT passé à la procédure ; pouvez-vous en dire plus ?

- pouvez-vous décoder IF LOCATE('fk_US_ident_SBM', @text) > 0 ?

- si j'ai bien compris, les variables sont écrites @VARXYZ. Donc quelle est la signification de termes comme SQLSTATE, MYSQL_ERRNO ou MESSAGE_TEXT ?

- Les codes d'erreur que vous mettez dans SQLSTATE ou dans MYSQL_ERRNO ont probablement une signification. Pouvez-vous en dire plus ?

**CinePhil** · 05/07/2021, 16h28

- SELECT US_ident INTO id_submitter FROM US_user : ça ressemble à une requête SQL, sauf le INTO id_submitter, où id_submitter est un paramètre INOUT passé à la procédure ; pouvez-vous en dire plus ?

Oui, les paramètres peuvent être valorisés à l'appel de la procédure ou modifiés durant la procédure.
Un SELECT US_ident INTO id_submitter va affecter automatiquement le résultat du SELECT au paramètre id_submitter. Bien entendu, il ne faut faire ça que si on est sûr d'avoir une seule ligne en réponse au SELECT, ce qui ne serait pas forcément le cas ici car il peut y avoir des homonymes. Il faudrait davantage de caractères discriminants et, d'ailleurs, si possible, mettre une clé UNIQUE sur un ensemble de colonnes. Mais, là encore, je n'ai pas lu toutes vos discussions donc je ne sais pas si ce point a été évoqué.
Avec ma procédure, soit on fournit à la procédure l'identifiant du submitter s'il est déjà connu par le programme, soit on fournit le nom et le prénom et c'est la procédure qui cherche le submitter et le crée s'il n'est pas trouvé.

- pouvez-vous décoder IF LOCATE('fk_US_ident_SBM', @text) > 0 ?

- si j'ai bien compris, les variables sont écrites @VARXYZ. Donc quelle est la signification de termes comme SQLSTATE, MYSQL_ERRNO ou MESSAGE_TEXT ?

- Les codes d'erreur que vous mettez dans SQLSTATE ou dans MYSQL_ERRNO ont probablement une signification. Pouvez-vous en dire plus ?

La première partie de la procédure définit un handler d'erreur. Il s'agit ici de détecter les erreurs et de fournir au programme un message d'erreur explicite.
Vous pouvez lire mon billet de blog à ce sujet.

**laurentSc** · 05/07/2021, 19h56

grâce à votre blog, je comprends presque tout. Néanmoins j'ai encore du mal avec ces 2 lignes :

Code sql :

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

1
2
DECLARE ctr_exception CONDITION FOR SQLSTATE '23000'; -- Erreur de contrainte
	DECLARE EXIT HANDLER FOR ctr_exception

D'autre part, au lieu de recourir aux procédures stockées, que pensez-vous d'utiliser les transactions (commit, rollback, etc) ?

**CinePhil** · 07/07/2021, 09h31

Il s'agit de déclarer un handler d'exception qui sera utilisé par le SGBD en cas d'erreur relevée par celui-ci. Exemple : le nom respect d'une contrainte de clé étrangère enclenchera une erreur par le SGBD. Il faut donc déclarer le handler avec l'instruction DECLARE EXIT HANDLER FOR ctr_exception.
Et comme on peut potentiellement déclarer plusieurs handlers d'exception, on désigne dans cette ligne dans quelle condition utiliser ce handler, via FOR ctr_exception. ce ctr_exception est un nom de condition qui est déclarée à la ligne juste au dessus : DECLARE ctr_exception CONDITION FOR SQLSTATE '23000'; -- Erreur de contrainte.

Donc on commence par déclarer une condition pour les erreurs de type 23000 et on utilise ensuite le nom ctr_exception dans l'initialisation du handler. Comme indiqué dans le commentaire, le code 23000 est pour les erreurs de non respect de contrainte (clés étrangères, unicité...). Un autre type d'erreur ne serait pas traité par le handler et le SGBD renverrait son texte d'erreur standard, souvent difficilement compréhensible par l'utilisateur lambda, si on lui affiche l'erreur tel quel.

Cette syntaxe de gestion d'erreur par MySQL / MariaDB est un peu complexe à comprendre au début mais on s'y habitue.

**escartefigue** · 07/07/2021, 14h17

Envoyé par laurentSc

OK,

ne connaissant commit que de nom, j'ai regardé https://baptiste-wicht.developpez.co...ver/securiser/. Même si ça parle de sql-server au lieu de MySQL, j'ai compris le principe. A mon avis, si je code les requêtes avec bon sens, pas besoin ; OK ?

Les notions de transactions sont fondamentales. L'enjeu est la cohérence du contenu de la base de données. Les contraintes d'intégrité n'y suffisent pas ni la qualité des requêtes.
Il est donc essentiel de maîtriser ce concept.

**laurentSc** · 08/07/2021, 09h53

Je commence à regarder. Ca a l'air plus simple que les procédures stockées :

Code sql :

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

1
2
3
4
5
6
7
8
9
-- on désactive l'autocommit (nécessaire pour MySQL)
SET autocommit = 0;
-- on lance la transaction
START TRANSACTION;
-- les requêtes SQL
if NO ERROR
    COMMIT;
else
    ROLLBACK;

La classe PHP PdoPlusPlus de Rawsrc les prend en charge. Comme j'utilise cette classe, je regarde comment faire...

**CinePhil** · 08/07/2021, 10h01

L'avantage d'une procédure stockée est que tout est fait dans le SGBD avec une seule connexion. Ce qui n'empêche pas de lancer les SART TRANSACTION et COMMIT ou ROLLBACK en dehors de la procédure. On peut aussi gérer la transaction à l'intérieur d'une procédure mais c'est un peu plus délicat s'il y a une procédure qui en utilise une autre.

**laurentSc** · 08/07/2021, 10h35

Peut-être, mais vu mon niveau, je préfère rester dans un domaine que je maîtrise.

Au fait, je réponds à certains de vos points :

Envoyé par CinePhil

2) Je ne vois pas l'utilité de la colonne TI_num_ticket BIGINT NOT NULL en plus de la clé primaire TI_ident INT UNSIGNED AUTO_INCREMENT alors je ne l'ai pas pris en compte dans ma procédure.
3) J'ai par contre ajouté l'objet du ticket qui, il me semble, manque à votre table. Ceci dit, je n'ai pas lu toutes les discussions que vous avez lancées sur votre projet.

Le fait de créer la rubrique TI_ident, c'est pour avoir une clé primaire plus concise, vu le type de la rubrique TI_num_ticket.

L'objet du ticket existe ; simplement, pour alléger le post, j'avais simplifié la classe TI_ticket...

**CinePhil** · 08/07/2021, 11h07

Le fait de créer la rubrique TI_ident, c'est pour avoir une clé primaire plus concise, vu le type de la rubrique TI_num_ticket.

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TABLE TI_ticket(
   TI_ident INT UNSIGNED AUTO_INCREMENT,
   TI_num_ticket BIGINT NOT NULL,
   US_ident_SBM INT UNSIGNED NOT NULL,
   PRIMARY KEY(TI_ident),
   UNIQUE(TI_num_ticket),
   FOREIGN KEY(US_ident_SBM) REFERENCES US_user(US_ident)
);

Soit le numéro de ticket est un entier qui est incrémenté à chaque nouveau ticket et est donc équivalent, au type près, à la clé primaire TI_ident ; soit c'est un code composé de chiffres et je ne suis pas sûr que le BIGINT soit le meilleur choix.
D'alleurs, il est un peu paradoxal d'avoir une colonne pouvant accueillir potentiellement un plus grand nombre de lignes (TI_num_ticket de type BIGINT) que la colonne clé primaire (de type INT). Soit vous envisagez vraiment d'avoir un si grand nombre de tickets que le BIGINT UNSIGNED s'impose et il faut que la clé primaire puisse accueillir autant de tickets, donc être aussi de type BIGINT UNSIGNED, soit vous avez vu trop grand et il conviendrait de réduire le type du numéro de ticket.

**laurentSc** · 08/07/2021, 11h35

Effectivement, si la clé primaire est de type INT, il n'était pas logique d'avoir un attribut NUM_ticket de type BIGINT. Ayant regardé, le type INT suffit, et du coup, je l'ai mis en clé primaire.

cardinalité 0,N ou 1,N ? [MCD]

Schéma

Discussions similaires

Partager

Partager