Discussion :

[steve-movi]

Bonjour à tous,

J'ai un petit doute sur mon modèle de données.

J'ai aujourd'hui une table "utilisateur" qui contient les champs "nom" "prenom" "type_utilisateur" "sexe" "moyenne" "fonction" "ancienneté"...

Ces utilisateurs peuvent être de type "élève" ou "professeur" (identifié grâce au champ "type_utilisateur").

Cependant certains champs n'existent que pour un type d'utilisateur.

Par exemple "moyenne" ne doit être renseigné que pour un utilisateur de type "élève"
Et "fonction" "ancienneté" ne doivent être renseignés que pour un utilisateur de type "professeur".

Dois-je plutôt faire deux tables "eleve" et "professeur" ou ne garder qu'une seule table "utilisateur" avec des champs qui seront inutilisés selon les cas ?

Merci pour vos réponses.

Steve

[punkoff]

Bonjour,

ca serait plutot trois en fait.

Cas typique de l'héritage.


On garde les attributs commun dans l'entité utilisateur, et les attributs spécifique dans les entité approprié.

Niveau clef, celle de l'entité utilisateur sera celle des deux autres entités.

Dans le cas ou un professeur ne peut etre aussi un eleve il faudra implémenter une CIF, via des trigger, afin de gérer ceci.

[steve-movi]

Ok merci.

Donc trois tables :

• utilisateur
• élève
• professeur

?

Dans utilisateur tout ce qui est commun et dans élève et prof tout ce qui est spécifique à chaque type d'utilisateur ?

[fsmrel]

Bonsoir,


Comme dit punkoff, l’héritage est à mettre en œuvre, c'est-à-dire spécialiser le type d’entité UTILISATEUR en ELEVE et PROFESSEUR, en factorisant dans UTILISATEUR les attributs communs tels que nom, prénom et sexe, tandis que les types d’entités spécialisées (sous-types) hébergent les attributs où ils trouvent leur justification.

Si vous grimpez au niveau conceptuel, vous pouvez modéliser à l’aide d’un AGL comme PowerAMC ou WinDesign (payants), ou DB-MAIN (gratuit).

Exemple de MCD (modèle conceptuel des données) avec PowerAMC :




Notez que, pour s’assurer d’une meilleure cohérence, il est d’usage de définir un type d’entité pour des données telles que les fonctions exercées.

En descendant d’un cran, vous pouvez demander à ces AGL de traduire le MCD en MLD (modèle logique des données), propre à donner lieu à la génération automatique de scripts de création des tables SQL. A défaut de d’élaborer un MCD, vous pouvez représenter directement un MLD à l’aide d’un outil gratuit comme MySQL Workbench, pour produire la représentation graphique suivante :



Dans le MCD, vous remarquerez la demie-lune où figure la lettre « X » : elle symbolise l’exclusion : un utilisateur est soit un professeur, soit un élève. En outre, cette lettre est soulignée, ce qui symbolise la totalité : les professeurs et les élèves sont les seuls utilisateurs, il n’y a pas d’autre type d’utilisateur. Par contre, MySQL Workbench ne propose rien pour représenter les contraintes d’exclusion et de totalité : on peut seulement surcharger la représentation graphique avec un outil comme PAINT... Et comme dit punkoff, c’est au niveau SQL qu’on agira, à l’aide triggers (voir ci-dessous), à moins que (rêvons) le SGBD propose l’instruction CREATE ASSERTION (qui fait partie de la norme SQL).


Script SQL généré avec MySQL Workbench :

TABLE FONCTION

Code SQL :

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

1
2
3
4
5
6
CREATE TABLE FONCTION 
(
  FonctionId              INT           NOT NULL,
  FonctionLibelle         VARCHAR(64)   NOT NULL,
  CONSTRAINT FONCTION_PK PRIMARY KEY (FonctionId)
) ;

TABLE UTILISATEUR

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TABLE UTILISATEUR 
(
  UtilisateurId           INT           NOT NULL,
  Nom                     VARCHAR(64)   NOT NULL,
  Prenom                  VARCHAR(64)   NOT NULL,
  Sexe                    CHAR(1)       NOT NULL,
  CONSTRAINT UTILISATEUR_PK PRIMARY KEY (UtilisateurId)
) ;

TABLE PROFESSEUR

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE PROFESSEUR 
(
  UtilisateurId           INT           NOT NULL,
  DateEmbauche            DATE          NOT NULL,
  FonctionId              INT           NOT NULL,
  CONSTRAINT PROFESSEUR_PK PRIMARY KEY (UtilisateurId),
  CONSTRAINT PROFESSEUR_UTILISATEUR_FK FOREIGN KEY (UtilisateurId) REFERENCES UTILISATEUR (UtilisateurId)
    ON DELETE CASCADE,
  CONSTRAINT PROFESSEUR_FONCTION_FK FOREIGN KEY (FonctionId) REFERENCES FONCTION (FonctionId)
) ;

TABLE ELEVE

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TABLE ELEVE 
(
  UtilisateurId           INT           NOT NULL,
  Moyenne                 INT           NOT NULL,
  CONSTRAINT ELEVE_PK PRIMARY KEY (UtilisateurId),
  CONSTRAINT ELEVE_UTILISATEUR_FK FOREIGN KEY (UtilisateurId) REFERENCES UTILISATEUR (UtilisateurId)
    ON DELETE CASCADE
) ;

Les contraintes d’exclusion sont à programmer, sous la forme de triggers (le contexte ici est MySQL) :

S’assurer qu’on ne crée pas un professeur qui serait déjà élève :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TRIGGER PROF_ELEVE_XT_INSERT BEFORE INSERT ON PROFESSEUR
FOR EACH ROW 
BEGIN
    SET @N = (
              SELECT COUNT(*) 
              FROM   ELEVE 
              WHERE UtilisateurId = NEW.UtilisateurId
             ) ;
    IF  @N > 0 THEN
        SET @T = CONCAT ('Echec de l’ajout d’un professeur : il existe déjà un élève pour l’attribut UtilisateurId égal à ', NEW.UtilisateurId) ;
        SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @T ;
    END IF ;
END ;

S’assurer qu’on ne modifie pas la clé d’un professeur alors que cette clé est déjà utilisée pour un élève :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TRIGGER PROF_ELEVE_XT_UPDATE BEFORE UPDATE ON PROFESSEUR
FOR EACH ROW 
BEGIN
    SET @N = (
              SELECT COUNT(*) 
              FROM   ELEVE 
              WHERE UtilisateurId = NEW.UtilisateurId
             ) ;
    IF  @N > 0 THEN
        SET @T = CONCAT ('Echec de l’update de la colonne UtilisateurId pour un prof : il existe déjà un élève pour l’attribut UtilisateurId égal à ', NEW.UtilisateurId) ;
         SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @T ;
    END IF ;
END ;

S’assurer qu’on ne crée pas un élève qui serait déjà professeur :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TRIGGER ELEVE_PROF_XT_INSERT BEFORE INSERT ON ELEVE
FOR EACH ROW 
BEGIN
    SET @N = (
              SELECT COUNT(*) 
              FROM   PROFESSEUR 
              WHERE UtilisateurId = NEW.UtilisateurId
             ) ;
    IF  @N > 0 THEN
        SET @T = CONCAT ('Echec de l’ajout d’un élève : il existe déjà un professeur pour l’attribut UtilisateurId égal à ', NEW.UtilisateurId) ;
        SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @T ;
    END IF ;
END ;

S’assurer qu’on ne modifie pas la clé d’un élève alors que cette clé est déjà utilisée pour un professeur :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TRIGGER ELEVE_PROF_XT_UPDATE BEFORE UPDATE ON ELEVE
FOR EACH ROW 
BEGIN
    SET @N = (
              SELECT COUNT(*) 
              FROM   PROFESSEUR 
              WHERE UtilisateurId = NEW.UtilisateurId
             ) ;
    IF  @N > 0 THEN
        SET @T = CONCAT ('Echec de l’update de la colonne UtilisateurId pour un élève : il existe déjà un prof pour l’attribut UtilisateurId égal à ', NEW.UtilisateurId) ;
        SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @T ;
    END IF ;
END ;

Dans le cas ou un professeur ne peut être aussi un élève il faudra implémenter une CIF, via des trigger, afin de gérer ceci.

Une CIF (contrainte d’intégrité fonctionnelle) est une contrainte d’unicité. Dans le cas présent on doit mettre en œuvre une contrainte d’exclusion (voire de partitionnement : exclusion + totalité, sachant que la totalité en SQL relève plutôt du vœu pieux ).

[steve-movi]

Merci beaucoup pour ces infos !
Je vais essayer de mettre tout cela en place en SQL Server.

Bon dimanche.

Steve

[steve-movi]

Bonjour

Question un peu naïve : que se passe-t-il si je reste avec une seule table "utilisateur" contenant tous les champs (au lieu de mettre en place l'héritage) ?
Je demande cela car c'est dans le but d'une application mobile et il faut limiter aux maximum le nombres d'appels à la base via l'API.

merci

Steve

[fsmrel]

Bonsoir Steve,

Que se passe-t-il si je reste avec une seule table "utilisateur" contenant tous les champs (au lieu de mettre en place l'héritage) ?

Vous allez vous retrouver avec des colonnes dans lesquelles le bonhomme Null va s’engouffrer, en jouant le rôle d’information « sans objet » (la moyenne d’un prof est sans objet, etc.) et Null (qui n’est pas une valeur mais une marque) est l’ennemi n° un des bases de données, avec sa logique ternaire propre à dérouter le non initié. Par ailleurs, la table UTILISATEUR sera d’autant plus encombrante que le nombre de colonnes rapatriées depuis les tables PROFESSEUR et ELEVE sera important.

Vous pourriez plutôt définir une vue dédiée aux élèves et une autre vue dédiée aux professeurs :

Vue dédiée aux élèves

Code SQL :

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

1
2
3
4
CREATE VIEW PROF_VUE (UtilisateurId, Nom, Prenom, Sexe, DateEmbauche, FonctionLibelle) AS
    SELECT x.UtilisateurId, Nom, Prenom, Sexe, DateEmbauche, FonctionLibelle
    FROM   UTILISATEUR AS x JOIN PROFESSEUR AS y ON x.UtilisateurId = y.UtilisateurId 
                            JOIN FONCTION AS z ON y.FonctionId = z.FonctionId ;

Vue dédiée aux professeurs

Code SQL :

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

1
2
3
CREATE VIEW ELEVE_VUE (UtilisateurId, Nom, Prenom, Sexe, Moyenne) AS
    SELECT x.UtilisateurId,  Nom, Prenom, Sexe, y.Moyenne
    FROM   UTILISATEUR AS x JOIN ELEVE AS y ON x.UtilisateurId = y.UtilisateurId ;

Je rappelle que pour l’utilisateur, une vue se manipule comme une table : il n’a pas à savoir que PROF_VUE et ELEVE_VUE ne sont pas des tables de base, mais des tables dérivées. Exemple :

Code SQL :

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

1
2
SELECT UtilisateurId, Nom, Prenom, Sexe, Moyenne 
FROM   ELEVE_VUE ;

Code SQL :

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

1
2
SELECT UtilisateurId, Nom, Prenom, Sexe, DateEmbauche, FonctionLibelle 
FROM   PROF_VUE ;

A part certains retardataires (MySQL par exemple), les SGBD permettent de mettre les vues à jour (insert, update, delete), via des triggers (voyez SQL Server). Du point de vue de la théorie relationnelle, toute vue peut être mise à jour nativement, mais SQL accuse un retard certain à ce sujet. A ce propos, quel est votre SGBD ?

[steve-movi]

Mon SGBD est SQL Server et j'accède à la base soit via "requétage direct" soit via une API REST pour mobile.

[fsmrel]

Bonsoir Steve,



Puisque vous utilisez SQL Server, vous pouvez mettre à jour les vues. Cela passe par la mise en oeuvre de triggers ad-hoc qui ventileront les données dans les tables de base.

Par exemple, si vous effectuez un INSERT « dans » la vue ELEVE_VUE :

Code SQL :

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

1
2
INSERT INTO ELEVE_VUE (UtilisateurId, Nom, Prenom, Sexe, Moyenne)
       VALUES (4, 'Remords', 'Yves', 'M', 17) ;

Alors le trigger ci-dessous interceptera l’insert et mettra à jour les tables UTILISATEUR et ELEVE :

Code SQL :

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

1
2
3
4
5
6
7
8
9
CREATE TRIGGER ELEVE_VUE_TR ON ELEVE_VUE INSTEAD OF INSERT AS
 
    INSERT INTO UTILISATEUR 
        SELECT UtilisateurId, Nom, Prenom, Sexe
        FROM   INSERTED ;
 
    INSERT INTO ELEVE 
        SELECT UtilisateurId, Moyenne
        FROM   INSERTED ;

Au résultat :

Code SQL :

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

SELECT * FROM UTILISATEUR ;

=>

Code :

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

1
2
3
UtilisateurId    Nom        Prenom    Sexe
            4    Remords    Yves      M

Code SQL :

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

SELECT * FROM ELEVE ;

=>

Code :

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

1
2
3
UtilisateurId     Moyenne 
            4     7

Si vous effectuez un INSERT « dans » la vue PROF_VUE :

Code SQL :

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

1
2
INSERT INTO PROF_VUE (UtilisateurId, Nom, Prenom, Sexe, DateEmbauche, FonctionLibelle)
       VALUES (1, 'Naudin', 'Fernand', 'M', '2000-08-01', 'fonction 1') ;

Alors le trigger ci-dessous interceptera l’insert et mettra à jour les tables UTILISATEUR et PROFESSEUR :

Code SQL :

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

1
2
3
4
5
6
7
8
9
CREATE TRIGGER PROF_VUE_TR ON PROF_VUE INSTEAD OF INSERT AS
 
    INSERT INTO UTILISATEUR 
        SELECT UtilisateurId, Nom, Prenom, Sexe
        FROM   INSERTED ;
 
    INSERT INTO PROFESSEUR 
        SELECT UtilisateurId, DateEmbauche, y.FonctionId
        FROM   INSERTED AS x JOIN FONCTION AS y ON x.FonctionLibelle = y.FonctionLibelle ;

Au résultat :

Code SQL :

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

SELECT * FROM UTILISATEUR ;

=>

Code :

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

1
2
3
UtilisateurId    Nom       Prenom     Sexe
            1    Naudin    Fernand    M

Code SQL :

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

SELECT * FROM PROFESSEUR ;

=>

Code :

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

1
2
3
UtilisateurId    DateEmbauche    FonctionId
            1    2000-08-01      1

A noter que les triggers d’exclusion que j’avais proposés valent pour MySQL mais pas pour SQL Server.

Ainsi, pour empêcher qu’un professeur ait un identifiant déjà utilisé pour un élève, avec SQL Server on codera plutôt :

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 TRIGGER PROF_ELEVE_XT ON PROFESSEUR INSTEAD OF INSERT AS
 
DECLARE @Count AS INT, @Err AS VARCHAR(128) ;
 
SET @Count = (SELECT COUNT(*)
              FROM   INSERTED AS x JOIN ELEVE AS y ON x.UtilisateurId = y.UtilisateurId) 
IF @Count > 0 
    BEGIN
        SET @Err = 'Tentative d’insertion d’un professeur ayant même identifiant qu’un élève existant.'                           
        RAISERROR (@Err, 15, 1)
    END
ELSE
    INSERT INTO PROFESSEUR
           SELECT UtilisateurId, DateEmbauche, FonctionId
           FROM   INSERTED ;

Etc.

On retiendra que si l’on émet des requêtes du genre :

Code SQL :

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

1
2
INSERT INTO ELEVE_VUE (UtilisateurId, Nom, Prenom, Sexe, Moyenne)
       VALUES (4, 'Remords', 'Yves', 'M', 17) ;

Ou :

Code SQL :

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

1
2
SELECT UtilisateurId, Nom, Prenom, Sexe, Moyenne 
FROM   ELEVE_VUE ;

Alors c’est le serveur qui se charge de tout, ça se passe sous le capot.

[SQLpro]

...

Puisque vous utilisez SQL Server, vous pouvez mettre à jour les vues. Cela passe par la mise en oeuvre de triggers ad-hoc qui ventileront les données dans les tables de base.

Il peut même le faire en direct vu que les vues (bien vu !) sont d'une seule table et les colonnes sans transformation.

A +

[fsmrel]

Salut Fred,

Il peut même le faire en direct vu que les vues (bien vu !) sont d'une seule table

Il va sans dire que lorsque je parle de mise à jour de vues par triggers, il est sous-entendu qu’il s’agit de vues de jointure, ce qui est bien le cas ici.

[steve-movi]

Merci pour votre aide et vos conseils !
Je vais paramétrer tout ça et voir si mon API me permet d'effectuer des opérations sur des vues.

Steve

[fsmrel]

Bonjour Steve,

Si votre API ne véhicule que des instructions de manipulation de données en mode natif SQL (SELECT, INSERT, UPDATE, DELETE), ou sous forme personnalisée (GET, POST, PUT, DELETE) à traduire en instructions SQL, en quelle mesure serait-elle intéressée de savoir que la cible de ces instructions serait une table dérivée (vue) plutôt qu’une table de base ? Il faudrait déjà qu’elle interroge le catalogue relationnel (la métabase) de SQL Server, pour être au parfum, elle se substituerait à SQL Server, elle sortirait de son rôle, mais il est vrai qu’en matière de bizarreries, on est loin d’avoir tout vu...

En tout cas, je serai intéressé par votre constat...

[steve-movi]

Bon dommage
J'utilise une API Microsoft Azure Mobile Services et cela nécessite d'ajouter les références des tables sur lesquelles va taper l'API et cela plante quand j'ajoute une Vue et non une Table.
Sinon pas de problème en "SQL direct" mais vous vous en doutez

Donc quelle serait la solution "la moins pire" : une seule table Utilisateur ou deux tables (Prof et Eleve) ?

[fsmrel]

Bonsoir Steve,

J'utilise une API Microsoft Azure Mobile Services et cela nécessite d'ajouter les références des tables sur lesquelles va taper l'API et cela plante quand j'ajoute une Vue et non une Table.

Hum... Que raconte le message d’erreur renvoyé par l’API ?

Quelle serait la solution "la moins pire" : une seule table Utilisateur ou deux tables (Prof et Eleve) ?

Dans la mesure où vous traitez soit des profs, soit des élèves, la solution serait plutôt la spécialisation c'est-à-dire deux tables, une table PROF et une table ELEVE, plutôt que le gros pâté truffé de nulls, que vous pouvez du reste inférer par UNION des deux tables :

Code SQL :

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

1
2
3
4
5
SELECT UtilisateurId, Nom, Prenom, Sexe, CAST(DateEmbauche AS CHAR(10)) AS DateEmbauche, FonctionLibelle, 'sans objet' AS Moyenne, 'PROF' AS TypeUtilisateur
FROM   PROFESSEUR
UNION ALL
SELECT UtilisateurId, Nom, Prenom, Sexe, 'sans objet', 'sans objet', CAST(Moyenne AS CHAR(10)), 'ELEVE'
FROM   ELEVE ;

[steve-movi]

En fait ce n'est pas une erreur de l'API mais une erreur dans la configuration du service mobile. En effet les services mobiles Azure ne permettent que de travailler sur des Tables. Donc lorsque je tente d'ajouter une Vue j'ai une erreur 500.

Après je peux garder le modèle optimum (avec héritage) et cela nécessitera deux appels à chaque fois... à voir.

Le problème d'avoir deux tables distinctes c'est que lorsque mon utilisateur se logue il faut que je regarde dans les deux tables pour savoir si c'est un élève ou un prof... pas super optimisé.

[steve-movi]

Autre question : dans le modèle "avec héritage" est-ce qu'il n'est pas conseillé d'avoir un champ type_utilisateur dans la table Utilisateur pour savoir ou requêter pour obtenir les infos complémentaires (élève ou prof) ?

[CinePhil]

Ça peut sembler plus pratique mais sur le plan de la normalisation de la BDD, c'est une redondance puisque la personne sera soit dans la table des profs, soit dans la table des élèves. Une requête sur une union des deux permettra de trouver de toute façon ses informations.

Ça peut aussi présenter un risque, en partant du principe qu'on n'est jamais sûr que la BDD sera alimentée et/ou mise à jour exclusivement par l'application que l'on crée en même temps que la BDD. Vous pourriez alors vous retrouver avec une personne indiquée comme prof dans la table des personnes alors que ses caractéristiques sont en fait dans la table des élèves. Ceci peut cependant être évité à l'aide de triggers.

Pour savoir si une personne est un prof ou un élève, il suffit d'interroger une des deux tables.

Code SQL :

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

1
2
3
4
5
SELECT u.UtilisateurId, p.DateEmbauche
FROM Utilisateur u
LEFT OUTER JOIN Professeur p ON p.UtilisateurId = u.UtilisateurId
WHERE u.Nom = 'Movi'
	AND u.Prenom = 'Steve'

Si la requête retourne NULL sur la date d'embauche, c'est que l'utilisateur est un élève.
Si la requête ne retourne aucun résultat, c'est que ce n'est pas un utilisateur enregistré.

Bien entendu, les critères de recherche peuvent être plus complets que le nom et le prénom. Comme le dit la sagesse populaire : "Il y a plus d'un âne à la foire qui s'appelle Martin."

[steve-movi]

Imaginons que je souhaite lister tous les champs d'un élève je dois donc lister tous les champs de la table utilisateur + de la table élève.
Mais si je ne sais pas si l'utilisateur est un élève ou un prof je suis bien forcé de tenter sur une table et si NULL alors j'essaye l'autre non ?

Ca ne pourrait pas être évité en stockant le type d'utilisateur dans la table Utilisateur ?

[CinePhil]

Parlons un peu de processus...

Puisqu'il s'agit d'un utilisateur, j'imagine que celui-ci s'authentifie pour accéder aux informations délivrées par la BDD, non ?

J'imagine donc le processus suivant :
1) Tentative de connexion d'un utilisateur.
2) Vérification par ma requête de mon précédent message que c'est bien un utilisateur autorisé et déduction de son état de prof ou d'élève.
3) Stockage de son type en session pour le reste de son activité sur l'application, ce qui permet cette fois d'interroger la bonne table à chaque fois.