Discussion :

[jmnicolas]

Bonjour,

j'ai créé un MCD dans lequel j'ai une relation 1,n / 1,1 comme illustré sur le jpeg suivant :



Donc une entreprise est logée dans 1 à n adresse(s).
Toutefois dans ma base, je peux créer une entreprise sans adresse ce qui va à l'encontre du MCD.

Existe il une possibilité d'écrire une contrainte SQL pour représenter le fait qu'une entreprise devrait avoir AU MOINS une adresse.

Bien sûr c'est pas un problème capital (je pourrais vérifier ça dans mon code), mais par curiosité j'aurais aimé savoir si ça pouvait se transcrire en SQL (en PLSQL c'est sûr, mais ça me parait bof).

Merci

[fsmrel]

Bonjour,

Sur la base des structures suivantes :

ENTREPRISE (EntrepriseId, EntrepriseNom) ;
ADRESSE (EntrepriseId, AdresseId, CodePostal, Ville) ;

En passant par une vue, on pourrait imposer que la 1re adresse d’une entreprise soit créée en même temps que celle-ci :

Code :

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

1
2
3
4
5
6
7
CREATE VIEW ENTREPRISE_V (EntrepriseId, EntrepriseNom
                          , AdresseId, CodePostal, Ville
                         ) 
     AS SELECT   x.EntrepriseId, x.EntrepriseNom
               , y.AdresseId, y.CodePostal, y.Ville
        FROM     ENTREPRISE AS x JOIN ADRESSE AS y 
                 ON  x.EntrepriseId = y.EntrepriseId  ;

Instruction INSERT correspondante (création de l’entreprise Yadupour) :

Code :

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

INSERT INTO ENTREPRISE_V VALUES (1, 'Yadupour', 1, '90000', 'Belfort') ;

Pour les autres adresses de l’entreprise Yadupour :

Code :

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

1
2
INSERT INTO ADRESSE VALUES (1, 2, '75001', 'Paris') ;
...

Maintenant, soit le SGBD accepte les mises à jour des vues de jointure, soit il les refuse. Comme j’utilise SQL Server 2005, c’est niet, je passe donc par un trigger pour intercepter les inserts et effectuer les ventilations qui vont bien :

Code :

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

1
2
3
4
5
6
7
CREATE TRIGGER ENTREPRISE_V_INSERT ON ENTREPRISE_V INSTEAD OF INSERT AS
    INSERT INTO ENTREPRISE (EntrepriseId, EntrepriseNom) 
           SELECT  EntrepriseId, EntrepriseNom
           FROM    INSERTED
    INSERT INTO ADRESSE (EntrepriseId, AdresseId, CodePostal, Ville) 
           SELECT  EntrepriseId, AdresseId, CodePostal, Ville
           FROM    INSERTED ;

Par ailleurs, si l’on supprime les adresses de l’entreprise, il faut en conserver au moins une, d’où la mise en œuvre d’un trigger ad-hoc (toujours dans le contexte SQL Server) :

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE TRIGGER ADRESSE_DELETE ON ADRESSE AFTER DELETE AS
  SELECT ''
  FROM   ENTREPRISE AS x 
          JOIN ADRESSE AS y ON x.EntrepriseId = y.EntrepriseId
  IF @@rowcount = 0
  BEGIN 
    Raiserror ('entreprise sans adresse après suppression d''adresse(s) !',16,1) 
    ROLLBACK
  RETURN
  END

Tout ceci n'est évidemment qu'une ébauche reposant sur l'idée de l'utilisation d'une vue contraignante. Les cracks de SQL auront peut-être des solutions moins lourdes.

[estofilo]

La question ne porte pas sur le MCD mais voici quand même une réponse orientée vers une modification du MCD.

Dans un cas réaliste on ne va pas se contenter de vouloir que l'entreprise ait au moins une adresse, mais on aura besoin de savoir laquelle est la principale (le siège social).
Du coup il y a une relation entreprise 1,1 [SIEGE SOCIAL] 1,1 adresse

Cette relation se traduira dans la table entreprise par une colonne du genre id_adresse_siege_social qui est NOT NULL et qui est une clef étrangère vers adresse, ce qui du coup assure la contrainte demandée.

Autre remarque en passant: à propos de la cardinalité (1,1) du côté adresse de la relation loger, le fait que deux entreprises ne puissent pas partager la même adresse ne reflète pas la réalité car c'est une pratique courante pour des petites entreprises.
Pour moi cette cardinalité serait plutôt (0,n)

[fsmrel]

Bonsoir,

Dans un cas réaliste on ne va pas se contenter de vouloir que l'entreprise ait au moins une adresse, mais on aura besoin de savoir laquelle est la principale (le siège social).
Du coup il y a une relation entreprise 1,1 [SIEGE SOCIAL] 1,1 adresse
Cette relation se traduira dans la table entreprise par une colonne du genre id_adresse_siege_social qui est NOT NULL et qui est une clef étrangère vers adresse, ce qui du coup assure la contrainte demandée.

Cela suppose que le modèle de jmnicolas prenne en compte le concept de Siège social (ou d’adresse principale). Admettons qu’il en soit ainsi. Au niveau SQL, vous définissez donc une clé étrangère côté ENTREPRISE :

Code SQL :

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

1
2
3
ENTREPRISE {EntrepriseId, AdresseId, EntrepriseNom, ...}
    PRIMARY KEY {EntrepriseId}
    FOREIGN KEY {AdresseId} REFERENCES ADRESSE {AdresseId} ;

Mais une adresse fait référence à une entreprise :

Code SQL :

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

1
2
3
ADRESSE {EntrepriseId, AdresseId, CodePostal, Ville, ...}
    PRIMARY KEY {AdresseId}
    FOREIGN KEY {EntrepriseId} REFERENCES ENTREPRISE {EntrepriseId} ;

Et cette-fois-ci on crée à un cycle entre ENTREPRISE et ADRESSE, ce qui n’est pas recommandé, tant s’en faut.

Autre remarque en passant: à propos de la cardinalité (1,1) du côté adresse de la relation loger, le fait que deux entreprises ne puissent pas partager la même adresse ne reflète pas la réalité car c'est une pratique courante pour des petites entreprises.
Pour moi cette cardinalité serait plutôt (0,n)

L’adresse d’une personne est une propriété de cette dernière, au même titre que ses prénoms, mais ça n’est pas parce que des personnes différentes ont en commun un prénom que l’on va mettre en œuvre une table PRENOM des prénoms (avec une cardinalité 0,N côté PRENOM).
Que dans telle entreprise (qui gère par exemple des copropriétés) on mette en œuvre une cardinalité 0,N portée par la patte connectant l’entité-type ADRESSE et l’association-type LOGER, soit. En effet, cette entreprise peut considérer les personnes comme des propriétés des adresses. Il n’en demeure pas moins que dans le cas général, l’adresse n’est pas sur un plan d’égalité avec l’entreprise, elle n’en est qu’une propriété multivaluée (adresse principale comme vous dites, adresse de facturation, adresse de livraison, etc.) que l’on externalise pour des raisons de normalisation en première forme normale (dans le cadre de la théorie relationnelle, cette externalisation n’est du reste même pas nécessaire, sans pour autant que la 1NF soit violée). Autrement dit, la cardinalité 1,1 portée par la patte connectant l’entité-type ADRESSE et l’association-type LOGER et figurant dans le MCD de jmnicolas est tout à fait pertinente.

Maintenant, mettre en œuvre une cardinalité 0,N portée par la patte connectant l’entité-type ADRESSE et l’association-type LOGER offre, d'un point de vue technique, l’avantage de rompre le cycle évoqué précédemment, car la situation deviendrait la suivante, LOGER servant pour les adresses disons secondaires et le rôle qu’elles jouent :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
ADRESSE {AdresseId, CodePostal, Ville, ...}
    PRIMARY KEY {AdresseId} ;
 
ENTREPRISE {EntrepriseId, AdressePrincipaleId, EntrepriseNom, ...}
    PRIMARY KEY {EntrepriseId}
    FOREIGN KEY {AdressePrincipaleId} REFERENCES ADRESSE {AdresseId} ; 
 
LOGER {EntrepriseId, AdresseId, RoleAdresse)
    PRIMARY KEY {EntrepriseId, AdresseId}
    FOREIGN KEY {EntrepriseId} REFERENCES ENTREPRISE {EntrepriseId}
    FOREIGN KEY {AdresseId} REFERENCES ADRESSE {AdresseId} ;

A jmnicolas de réfléchir à tout cela...

[estofilo]

Il n’en demeure pas moins que dans le cas général, l’adresse n’est pas sur un plan d’égalité avec l’entreprise, elle n’en est qu’une propriété multivaluée (adresse principale comme vous dites, adresse de facturation, adresse de livraison, etc.) que l’on externalise pour des raisons de normalisation en première forme normale (dans le cadre de la théorie relationnelle, cette externalisation n’est du reste même pas nécessaire, sans pour autant que la 1NF soit violée).

En fait je n'ai pas du tout fait la même interprétation de l'adresse dans le MCD
Moi j'ai compris ça comme les différents établissements d'une entreprise, au sens où l'entend l'urssaf par exemple, avec son numéro de siret différent par établissement d'une même entreprise.
Par exemple une chaine de magasins, qui serait une seule entreprise, peut avoir 1 siège social, 2 entrepôts et 10 magasins, tout ça à des adresses différentes. De nouvelles adresses peuvent apparaitre, d'autres disparaitrent...
Là clairement on n'a pas affaire à une propriété multi-valuée mais à une relation entre une entreprise et N lieux géographiques.

Mais si on doit interpréter les différentes adresses en termes d'adresse de facturation, adresse de livraison, etc... le raisonnement est différent.

[jmnicolas]

Bonjour,

merci pour vos propositions, ça confirme ce que je pensais, il n'y a pas de SQL "simple" qui puisse assurer la contrainte directement.

Je pense que je vais gérer ça dans le code de mon application.

En ce qui concerne l'adresse unique par entreprise, c'est un choix volontaire de ma part : cette base de donnée permet de gérer la maintenance de notre matériel embarqué, et ces adresses sont des adresses de retour chez les fournisseurs.

Bien que certains aient sans doute plusieurs adresses, ils n'ont tous qu'une seule adresse où retourner le matériel.
Seule cette adresse est pertinente dans l'application que je développe, c'est pour imprimer une étiquette à coller sur le carton ...

[fsmrel]

Bonsoir,

ces adresses sont des adresses de retour chez les fournisseurs.

Votre modélisation est donc pertinente (à condition de savoir comment reconnaître la bonne adresse parmi l’ensemble des adresses de retour).

il n'y a pas de SQL "simple" qui puisse assurer la contrainte directement

Il y a une instruction SQL qui permet d’éviter la complication engendrée par l’utilisation des triggers (par exemple, nous devons penser à ne pas oublier de mettre en œuvre un trigger pour surveiller la suppression des adresses). Cette instruction s’appelle CREATE ASSERTION et fait partie de la norme (elle reprend l’instruction ASSERT du langage SEQUEL —par la suite renommé SQL— utilisé avec le prototype SYSTEM R d’IBM dans les années soixante-dix) :

Code SQL :

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

1
2
3
4
5
6
7
8
9
CREATE ASSERTION AssertAdresse CHECK 
    (NOT EXISTS 
        (SELECT  '' 
         FROM ENTREPRISE AS x 
         WHERE NOT EXISTS 
              (SELECT  '' 
               FROM ADRESSE AS y 
               WHERE x.EntrepriseId = y.EntrepriseId)))
      DEFERRABLE INITIALLY DEFERRED ;

Mais on attend toujours que les SGBDR nous fournissent cette instruction...

Moi j'ai compris ça comme les différents établissements d'une entreprise, au sens où l'entend l'urssaf par exemple, avec son numéro de siret différent par établissement d'une même entreprise.

Le scénario URSSAF n’est qu’un scénario parmi d’autres et manifestement ne répond pas pour le MCD de jmnicolas. Quoi qu’il en soit, si l’on souhaite modéliser non seulement les fournisseurs, mais aussi leurs filiales, avec SIRET et tout ça, on peut très bien définir au besoin une entité-type du genre FOURNISSEUR que l’on peut spécialiser en ENTREPRISE et ETABLISSEMENT, avec bien entendu une relation permettant de connecter ces entités-types, et avec la possibilité de modéliser les adresses comme on l’entend, c'est-à-dire en fonction des besoins des applications en terme de structure des données.

Pour quitter le niveau conceptuel, j’ai effectué pas mal d’audits de performance des requêtes SQL hébergées par des programmes utilisés pour les traitements de type batch, requêtes effectuant des opérations du type ENTREPRISE JOIN ADRESSE (de l’ordre de quelques millions de lignes). Quand la table ADRESSE avait sa propre clé, régulièrement les traitements se traînaient. Si l’index branché sur la clé étrangère reliant ADRESSE à ENTREPRISE (attribut EntrepriseId ci-dessus) pouvait être rendu CLUSTER (au sens DB2 for z/OS du terme), la performance était enfin au rendez-vous. Dans le cas où ENTREPRISE et ADRESSE étaient en relation par le biais d’une table intermédiaire (à cause d’un lien de type plusieurs à plusieurs), il était nécessaire de commencer par décharger ADRESSE dans une table temporaire à trier sur EntrepriseId. Quand vous avez 10 tables pour lesquelles il faut procéder ainsi, ça lasse. Vive les propriétés multivaluées, car elles ne sont pas à l’origine de contre-performances.

De nouvelles adresses peuvent apparaitre, d'autres disparaitrent...
Là clairement on n'a pas affaire à une propriété multi-valuée mais à une relation entre une entreprise et N lieux géographiques.

Ça n’est pas un déménagement (une mise à jour reste une mise à jour) ou la localisation géographique qui font qu’il faille transformer l’entité-type ADRESSE faible (weak entity-type au sens de Chen) en entité-type forte.

En tout cas, la modélisation des données n’étant pas une science exacte mais plutôt un art qui s’acquiert avec l’expérience, à chacun de capitaliser au fil des projets, de tenir compte des retours des Directions informatiques quant au succès de la mise en production de ce qu’il a modélisé (et prototypé quant aux performances, car ces Directions savent mitonner des contrats où nous nous engageons financièrement à ce sujet...)