Relation entre table

**jim53** · 05/03/2013, 14h46

Bonjour,

Je suis débutant dans les bases de données.
Je souhaite créer une application de gestion de projet dont les données seraient sauvegardées dans une base de données.

J'ai commencé à créer quelques tables et établir des relations entre elles mais j'ai l'impression que j'ai une erreur dans mes relations entre tables.

Pouvez-vous me confirmer que mes relations sont mal établies et m'indiquez comment modifier mes relations pour quelles soient cohérentes.

**Babyneedle** · 05/03/2013, 14h52

Les relations semblent correctes selon le peu d'informations qui nous sont données.

Par contre, les noms de tes tables sont trop techniques et ça risque de poser problème.

Mon opinion serait de les appeller:

Projets
Clients
Employes
Equipes

**fsmrel** · 05/03/2013, 15h32

Bonjour,

La table Jonction n'a pas de clé : c'est donc un sac (bag). Mettre en oeuvre la clé {Id_Projet, Id_Contact_Client} (en supposant qu'un contact peut être impliqué dans plus d'un projet et qu'un projet peut faire mention de plus d'un contact).

Selon votre représentation, le projet P1 du client C2 peut faire référence au contact T1 qui lui même fait référence au client C1 : bug, car C1 et C2 ne sont pas le même client...

Envoyé par Babyneedle

les noms de tes tables sont trop techniques et ça risque de poser problème

Trop technique : le problème n'est pas là. En plus, avec ce genre d'affirmation non étayée, vous risquez de vous faire remonter les bretelles par SQLpro et CinePhil, il y a eu des débats à ce propos.

Envoyé par Babyneedle

Mon opinion serait de les appeller:

Projets
Clients
Employes
Equipes

Ça se discute. De préférence au singulier.

"Contact" reste préférable, "Employé" est trop général : un contact est un employé, mais un employé n'est pas forcément un contact.

Pourquoi "Equipe" ? Un contact est seulement chargé d'être au courant de certains projets, de voir où ils en sont.

**fsmrel** · 05/03/2013, 15h49

Qu’un contact puisse s’occuper de plusieurs projets, ça paraît naturel.

Par ailleurs, selon votre représentation graphique, un projet peut faire référence à plusieurs contacts, et ça paraît moins évident. En est-il bien ainsi ?

**Babyneedle** · 05/03/2013, 19h27

Trop technique : le problème n'est pas là. En plus, avec ce genre d'affirmation non étayée, vous risquez de vous faire remonter les bretelles par SQLpro et CinePhil, il y a eu des débats à ce propos.

Utiliser des termes, suffixes et préfixes tels que 'T_' et 'JONCTION' c'est imposer le vocabulaire technique sur quelque chose qui devrait, à moins d'exception, être logique. Ça pollue le champ de vision.

Si vous êtes la police de l'ettayage d'affirmation et que vous jugez que mes interventions mérite un remontage de bretelles, j'irai affirmer ailleurs. C'est tout.

Ça se discute. De préférence au singulier.

"Contact" reste préférable, "Employé" est trop général : un contact est un employé, mais un employé n'est pas forcément un contact.

Pourquoi "Equipe" ? Un contact est seulement chargé d'être au courant de certains projets, de voir où ils en sont.

Personnellement, je trouve 'contact' trop générique. Et puis j'ai choisi 'Equipe' parce qu'un groupe de personnes affectées à un projet, c'est une équipe.

**fsmrel** · 06/03/2013, 00h36

Bonsoir,

Envoyé par jmduchesne

Il peut y avoir plusieurs contacts par client, plusieurs contacts peuvent s'occuper des plusieurs projets.

Je réitère ma question : est-ce que deux contacts (de la même entreprise) peuvent binômer pour un même projet (de leur entreprise) ? En attendant votre réponse, je considère ci-dessous que c’est possible.

Partons maintenant des déclarations des tables (instruction CREATE TABLE) :

Table T_SOCIETE_CLIENT

Code SQL :

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

1
2
3
4
5
6
CREATE TABLE T_SOCIETE_CLIENT
(
        Id_Societe_Client        INT          NOT NULL
      , Nom                      VARCHAR(48)  NOT NULL
    , CONSTRAINT T_SOCIETE_CLIENT_PK PRIMARY KEY (Id_Societe_Client)
) ;

Table T_PROJET

Code SQL :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE T_PROJET
(
        Id_Societe_Client        INT          NOT NULL
      , Id_Projet                INT          NOT NULL
      , Description              VARCHAR(48)  NOT NULL
    , CONSTRAINT T_PROJET_PK PRIMARY KEY (Id_Projet)
    , CONSTRAINT T_PROJET_CLIENT_FK FOREIGN KEY (Id_Societe_Client) 
          REFERENCES T_SOCIETE_CLIENT ON DELETE CASCADE
) ;

Cette table est dotée d’une clé étrangère (foreign key) T_PROJET_CLIENT_FK permettant de s’assurer que chaque projet fait obligatoirement référence à un client appartenant à la table T_SOCIETE_CLIENT. La clause ON DELETE CASCADE signifie que lorsqu’un client est supprimé, ses projets doivent être supprimés eux aussi. Par contre, si l’option à retenir est qu’un client ne peut pas être supprimé tant qu’il a au moins un projet, en codant NO ACTION au lieu de CASCADE, la suppression du client est rejetée quand il est référencé par au moins un projet. Ainsi, une base de données a un certain métabolisme, il y a des émissions de stimuli à destination des tables touchées lors des tentatives de suppression d’une ligne d’une table : CASCADE permet la suppression de ses « enfants » quand un « parent » disparaît, tandis que NO ACTION l’interdit.

Table T_CONTACT_CLIENT

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE T_CONTACT_CLIENT
(
        Id_Societe_Client        INT          NOT NULL
      , Id_Contact_Client        INT          NOT NULL
      , Nom                      VARCHAR(48)  NOT NULL
      , Prenom                   VARCHAR(48)  NOT NULL
    , CONSTRAINT T_CONTACT_CLIENT_PK PRIMARY KEY (Id_Contact_Client)
    , CONSTRAINT T_CONTACT_CLIENT_FK FOREIGN KEY (Id_Societe_Client) 
          REFERENCES T_SOCIETE_CLIENT ON DELETE CASCADE
) ;

Ce qui vaut pour la table T_PROJET vaut évidemment pour la table T_CONTACT_CLIENT.

Table JONCTION_T_PROJET_T_CONTACT_CLIENT

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE JONCTION_T_PROJET_T_CONTACT_CLIENT
(
        Id_Contact_Client        INT          NOT NULL
      , Id_Projet                INT          NOT NULL
    , CONSTRAINT JONCTION_PK PRIMARY KEY (Id_Contact_Client, Id_Projet)
    , CONSTRAINT JONCTION_CONTACT_FK FOREIGN KEY (Id_Contact_Client) 
          REFERENCES T_CONTACT_CLIENT ON DELETE CASCADE
    , CONSTRAINT JONCTION_PROJET_FK FOREIGN KEY (Id_Projet) 
          REFERENCES T_PROJET ON DELETE CASCADE
) ;

La situation est intéressante car si l’on conserve CASCADE pour l’ensemble des tables, la suppression d’un client provoque l’émission de stimuli qui touchent T_PROJET et T_CONTACT_CLIENT, puis par « rebond » (transitivité) T_PROJET_T_CONTACT_CLIENT. Ainsi, les stimuli suivent des chemins allant de T_SOCIETE_CLIENT à JONCTION_T_PROJET_T_CONTACT_CLIENT par deux chemins différents, collatéraux (conterminous paths dans la théorie relationnelle), mais ils ne « remontent » pas, parce que les tables T_PROJET et T_CONTACT_CLIENT ne font pas référence à la table JONCTION_T_PROJET_T_CONTACT_CLIENT, pas plus que la table T_SOCIETE_CLIENT ne fait référence aux tables T_PROJET et T_CONTACT_CLIENT. Par conséquent, s’il y a effet de cycle c'est seulement une illusion, une interprétation fausse de nos sens abusés, c'est purement visuel.

Pour sa part, MS SQL Server n’a pas bien compris le problème du conterminous path, MS devrait s’inspirer de ce qu’à écrit C.J. Date dans le chapitre 13 / « Inclusion Dependencies and Foreign Keys » de l’ouvrage Database Explorations qu’il a commis en collaboration avec Hugh Darwen. En attendant que MS revienne à la raison, il faut glisser un NO ACTION quelque part sur un des deux chemins, pour éviter ainsi de se prendre dans les dents le message auquel vous avez eu droit :

L'introduction d'une contrainte FOREIGN KEY 'FK_Jonction_T_Projet_T_Contact_Client_T_Projet' sur la table 'Jonction_T_Projet_T_Contact_Client' peut provoquer des cycles ou des accès en cascade multiples. Spécifiez ON DELETE NO ACTION ou ON UPDATE NO ACTION, ou modifiez d'autres contraintes FOREIGN KEY.

En notant que c’est la partie a priori sibylline « accès en cascade multiples » qui normalement vous concerne dans ce message.

Attention ! Je n’ai pas dit que sorti de MS SQL Server il fallait systématiquement coder CASCADE. C’est la sémantique qui doit nous guider : Est-il logique de supprimer les contacts si on supprime un client ? A vous de voir. Dans un contexte de gestion, faut-il supprimer les factures si l’on tente de supprimer un client ? Si vous dites aux comptables de l’entreprise : « Pourquoi pas ? », leur réaction risquerait d’être violente ! En revanche, ils ne seront pas émus si vous supprimez de facto les lignes d’une facture qui est légitimement à supprimer : ces lignes ne sont après tout qu’une propriété multivaluée de la facture, faisant qu’au stade de la modélisation on les a externalisées pour respecter la théorie relationnelle.

Il ne faut pas non plus passer d’un extrême à l’autre et bannir ON DELETE CASCADE. Si l'on code systématiquement ON DELETE NO ACTION, la base de données risque d'être pétrifiée, vous vous sentirez les pieds comme pris dans le béton, la moindre suppression pourra devenir très lourde à programmer : il faut de la mesure en toutes choses, trouver le juste équilibre.

Cas de ON UPDATE

On a traité de ON DELETE CASCADE et ON DELETE NO ACTION, mais les SGBD SQL permettent de coder au choix ON UPDATE CASCADE ou ON UPDATE NO ACTION : autrement dit, le remplacement d'une valeur de clé faisant l’objet d’une référence effective — par exemple la clé primaire {Id_Societe_Client} de la table T_SOCIETE_CLIENT, référencée par les clés étrangères {Id_Societe_Client} des tables T_PROJET et T_CONTACT_CLIENT — est accepté ou pas. Disons que le problème ne doit pas se poser et que la seule option à retenir est ON UPDATE NO ACTION (qui est l’option par défaut et qui vaut donc pour les déclarations de tables ci-dessus), car si l’on modélise proprement et si l'on suit Yves Tabourier, on n’a aucune raison d’avoir à remplacer une valeur de clé primaire.

Je vais essayer de trouver le temps de rédiger plus avant, notamment sur le problème du projet qui pourrait être affecté à des contacts d’une entreprise qui n’est pas celle à laquelle appartient le projet...

**fsmrel** · 06/03/2013, 04h05

Au sujet de la clé primaire de la table JONCTION_T_PROJET_T_CONTACT_CLIENT

Je rappelle qu’une table peut mais ne doit pas être un sac (bag). Si vous définissez les clés par le biais d’un « Database Diagram », définissez la clé primaire qui va bien (personnellement je ne suis pas familier avec ce diagramme...) :

Au résultat :

Envoyé par jmduchesne

il me semblait bien qu'il y avait une sorte de boucle sans fin

Comme je l’ai signalé dans mon message précédent, il n’y a pas de boucle sans fin, juste une conséquence de l’existence d’un conterminous path : pour aller de la table T_SOCIETE_CLIENT à la table JONCTION_T_PROJET_T_CONTACT_CLIENT, il y a deux chemins et comme on l’a vu, MS SQL Server n’aime pas trop que l’on supprime tout ce qui traîne sur le passage, aussi vous a-t-il bombardé d’ un message sujet à quiproquo, mais, quoi qu’il en soit injustifié du point de vue la théorie relationnelle.

Supposons donc que ce message soit bien dû au fait que vous avez systématiquement codé ON DELETE CASCADE (volontairement ou « à l'insu de votre plein gré » comme dit l'autre...) Supposons encore (cas d’école) que vous teniez absolument à cette option. De toutes façons, SQL Server oblige, il faudra coder un ON DELETE NO ACTION pour une clé étrangère, par exemple celle qui lie les tables JONCTION_T_PROJET_T_CONTACT_CLIENT et T_CONTACT_CLIENT :

Code SQL :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE JONCTION_T_PROJET_T_CONTACT_CLIENT
(
        Id_Contact_Client        INT          NOT NULL
      , Id_Projet                INT          NOT NULL
    , CONSTRAINT JONCTION_PK PRIMARY KEY (Id_Contact_Client, Id_Projet)
    , CONSTRAINT JONCTION_CONTACT_FK FOREIGN KEY (Id_Contact_Client) 
          REFERENCES T_CONTACT_CLIENT ON DELETE NO ACTION
    , CONSTRAINT JONCTION_PROJET_FK FOREIGN KEY (Id_Projet) 
          REFERENCES T_PROJET ON DELETE CASCADE) ;

Je rappelle que ON DELETE NO ACTION est l’option par défaut, elle peut donc être omise.

Après tout, éviter CASCADE à cet endroit ça n’est pas plus mal, car au plan sémantique on peut se poser la question : « Que faire si le contact Bastien ne fait plus partie de l’entreprise alors qu’il est partie prenante dans les projets P1,..., Pn ? » Si la réponse est : « Il faut lui trouver un remplaçant pour ces projets avant de le faire disparaître de la base de données », alors ON DELETE NO ACTION répond en fait à ce besoin fonctionnel : impossibilité de supprimer Bastien tant qu’il est partie prenante dans des projets, c’est ceinture, bretelles et épingles à nourrice. Si c’est Henri qui doit prendre le relais, on lui rattachera par INSERT les projets P1,..., Pn, que l’on détachera (DELETE) ensuite de Bastien. Comme celui-ci ne trempera plus dans le moindre projet, le SGBD ne s’opposera pas à sa suppression.

Envoyé par fsmrel

Selon votre représentation, le projet P1 du client C2 peut faire référence au contact T1 qui lui même fait référence au client C1 : bug, car C1 et C2 ne sont pas le même client...

En fait, on a affaire à une conséquence du conterminous path, donnant lieu à une variation sur le thème du chemin.

C’est un problème qui refait surface régulièrement...

Sémantisons un peu. Un projet n’est jamais qu’une propriété d’une société, il est à elle et à elle seulement : au niveau conceptuel on peut considérer qu’un projet n’existe que par le client auquel il est attaché, il n’est pas autonome, sans client il n’a pas de sens. Conceptuellement on peut voir T_PROJET comme une entité-type faible (weak entity-type) ce qu’on traduit techniquement par ce qu’on appelle l’identification relative au niveau du Modèle Conceptuel de Données (ou fait l’objet d’une agrégation dans le cas d’un diagramme de classes).

Au niveau tabulaire, la clé primaire de la table T_PROJET n’est plus le singleton {Id_Projet}, mais la paire {Id_Societe_Client, Id_Projet} :

Code SQL :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE T_PROJET
(
        Id_Societe_Client        INT          NOT NULL
      , Id_Projet                INT          NOT NULL
      , Description              VARCHAR(48)  NOT NULL
    , CONSTRAINT T_PROJET_PK PRIMARY KEY (Id_Societe_Client, Id_Projet)
    , CONSTRAINT T_PROJET_CLIENT_FK FOREIGN KEY (Id_Societe_Client) 
          REFERENCES T_SOCIETE_CLIENT
) ;

Id_Projet n’est qu’un séquenceur prenant les valeurs 1,..., N en relation avec les N projets d’une société cliente : cette numérotation repart à 1 pour chaque société.

Le principe est le même pour la table T_CONTACT_CLIENT :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE T_CONTACT_CLIENT
(
        Id_Societe_Client        INT          NOT NULL
      , Id_Contact_Client        INT          NOT NULL
      , Nom                      VARCHAR(48)  NOT NULL
      , Prenom                   VARCHAR(48)  NOT NULL
    , CONSTRAINT T_CONTACT_CLIENT_PK PRIMARY KEY (Id_Societe_Client, Id_Contact_Client)
    , CONSTRAINT T_CONTACT_CLIENT_FK FOREIGN KEY (Id_Societe_Client) 
          REFERENCES T_SOCIETE_CLIENT ON DELETE CASCADE
) ;

Ainsi que pour la table JONCTION_T_PROJET_T_CONTACT_CLIENT, avec la présence d’une seule colonne Id_Societe_Client, ce qui fait que le client référencé est le même, que la contrainte référentielle porte sur la table T_PROJET ou sur la table T_CONTACT_CLIENT :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE JONCTION_T_PROJET_T_CONTACT_CLIENT
(
        Id_Societe_Client        INT          NOT NULL
      , Id_Projet                INT          NOT NULL
      , Id_Contact_Client        INT          NOT NULL
    , CONSTRAINT JONCTION_PK PRIMARY KEY (Id_Societe_Client, Id_Contact_Client, Id_Projet)
    , CONSTRAINT JONCTION_CONTACT_FK FOREIGN KEY (Id_Societe_Client, Id_Contact_Client) 
          REFERENCES T_CONTACT_CLIENT
    , CONSTRAINT JONCTION_PROJET_FK FOREIGN KEY (Id_Societe_Client, Id_Projet) 
          REFERENCES T_PROJET 
) ;

Un bout de jeu d’essai pour vérifier :

Table T_SOCIETE_CLIENT

Code SQL :

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

1
2
3
4
INSERT INTO T_SOCIETE_CLIENT (Id_Societe_Client, Nom) VALUES (1, 'Etablissements Naudin') ;
INSERT INTO T_SOCIETE_CLIENT (Id_Societe_Client, Nom) VALUES (2, 'Volfoni Frères') ;
 
SELECT '' AS 'T_SOCIETE_CLIENT', * FROM T_SOCIETE_CLIENT ;

Table T_PROJET

Code SQL :

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

1
2
3
4
5
INSERT INTO T_PROJET (Id_Societe_Client, Id_Projet, Description) VALUES (1, 1, 'projet Biglotron') ;
INSERT INTO T_PROJET (Id_Societe_Client, Id_Projet, Description) VALUES (1, 2, 'projet Pastis') ;
INSERT INTO T_PROJET (Id_Societe_Client, Id_Projet, Description) VALUES (2, 1, 'Appolo') ;
 
SELECT '' AS 'T_PROJET', * FROM T_PROJET ;

Rappel : avec l’identification relative, la valeur prise par la colonne Id_Projet commence à 1 pour chaque société.

Table T_CONTACT_CLIENT

Code SQL :

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

1
2
3
4
5
6
INSERT INTO T_CONTACT_CLIENT (Id_Societe_Client, Id_Contact_Client, Nom, Prenom) VALUES (1, 1, 'Trancène', 'Jean') ;
INSERT INTO T_CONTACT_CLIENT (Id_Societe_Client, Id_Contact_Client, Nom, Prenom) VALUES (1, 2, 'Quatre', 'Henri') ;
INSERT INTO T_CONTACT_CLIENT (Id_Societe_Client, Id_Contact_Client, Nom, Prenom) VALUES (2, 1, 'Paraboum', 'Pascal') ;
INSERT INTO T_CONTACT_CLIENT (Id_Societe_Client, Id_Contact_Client, Nom, Prenom) VALUES (2, 2, 'Piouf', 'Bastien') ; 
 
SELECT '' AS 'T_CONTACT_CLIENT', * FROM T_CONTACT_CLIENT ;

Table JONCTION_T_PROJET_T_CONTACT_CLIENT

Code SQL :

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

1
2
3
4
5
INSERT INTO JONCTION_T_PROJET_T_CONTACT_CLIENT (Id_Societe_Client, Id_Projet, Id_Contact_Client) VALUES (1, 1, 1) ;
INSERT INTO JONCTION_T_PROJET_T_CONTACT_CLIENT (Id_Societe_Client, Id_Projet, Id_Contact_Client) VALUES (1, 2, 1) ;
INSERT INTO JONCTION_T_PROJET_T_CONTACT_CLIENT (Id_Societe_Client, Id_Projet, Id_Contact_Client) VALUES (2, 1, 1) ;
 
SELECT '' AS 'JONCTION_T_PROJET_T_CONTACT_CLIENT', * FROM JONCTION_T_PROJET_T_CONTACT_CLIENT ;

**jim53** · 05/03/2013, 15h57

Merci pour votre réponse,

Voici quelques informations sur la base de données que je souhaite réaliser :

- La table projet vise à regrouper l'ensemble des donnée d'un projet de conception de pièces mécaniques.
- La table Société_Client doit permettre d'enregistrer les clients de tous les projet sachant qu'un projet ne peut avoir qu'un seul client.
- La table Contact_Client est une table qui regroupe des informations sur les contact chez le client. Ces contacts font partie de la société client. Il peu y avoir plusieurs contacts par client, plusieurs contacts peuvent s'occuper des plusieurs projets. Certains contacts s'occupent de certains projets.

Lorsque j'essaie d'enregistrer ce schéma sous SQLserver, il me renvoie l'erreur suivante mais je ne comprends quoi faire pour résoudre le problème :

- Impossible de créer la relation «*FK_Jonction_T_Projet_T_Contact_Client_T_Projet*».
L'introduction d'une contrainte FOREIGN KEY 'FK_Jonction_T_Projet_T_Contact_Client_T_Projet' sur la table 'Jonction_T_Projet_T_Contact_Client' peut provoquer des cycles ou des accès en cascade multiples. Spécifiez ON DELETE NO ACTION ou ON UPDATE NO ACTION, ou modifiez d'autres contraintes FOREIGN KEY.
Impossible de créer la contrainte. Voir les erreurs précédentes.

**fsmrel** · 05/03/2013, 16h29

Je prends l'affaire en main et vous tiens au courant. Je vais reproduire votre modèle et générer les tables.

**jim53** · 05/03/2013, 17h11

Je vous remercie beaucoup car il me semblait bien qu'il y avait une sorte de boucle sans fin comme vous l'avez écrit :

"La table Jonction n'a pas de clé : c'est donc un sac (bag). Mettre en oeuvre la clé {Id_Projet, Id_Contact_Client} (en supposant qu'un contact peut être impliqué dans plus d'un projet et qu'un projet peut faire mention de plus d'un contact).

Selon votre représentation, le projet P1 du client C2 peut faire référence au contact T1 qui lui même fait référence au client C1 : bug, car C1 et C2 ne sont pas le même client..."

J'attends vos prochains commentaires avec impatience pour tenter de comprendre quelles sont les techniques qui peuvent être mises en œuvre pour contourner ces cas de figure.

Relation entre table

MS SQL Server

Vue hybride

Discussions similaires

Partager

Partager