Garantir l’unicité d’une relation avec des attributs optionnels

Version imprimable

28/06/2012, 18h20
Invité

2 pièce(s) jointe(s)

Garantir l’unicité d’une relation avec des attributs optionnels
Bonjour

Problème:

Voici les règles de gestion du contexte simplifié :
1. Un ARTICLE est obligatoirement lié à une PLANTE et une seule.
2. Une PLANTE peut faire l’objet de un ou plusieurs ARTICLES.
3. Un ARTICLE peut être proposé en une ou plusieurs hauteurs.
Exemples d’articles :
- Pommier
- Poirier
- Poirier 150/200 cm
- Chêne 100/125 cm
- Chêne 125/150 cm
- Noisetier 150/175/200 cm
Je joins un petit diagramme 'schema01.png' pour mieux voir tout ça.

On voit donc que l’unicité d’un article ne peut pas être garanti par la seule plante qui y est liée.
Alors comment faire pour éviter d’avoir des doublons ? Dans les exemples ci-dessus, j’ai déjà le ‘Poirier 150/200’, existe-il une manière d’éviter d’entrer à nouveau ce ‘Poirier 150/200’ avec des contraintes d’intégrité référentielles ? Si oui, quelle-est cette manière ? Si non, quelle serait la solution la plus adéquate pour le faire ?

J’ai bien une petite idée pour la réponse à ma dernière question, il suffirai dans ce cas de vérifier au préalable qu’il n’existe pas déjà de ‘Poirier 150/200’ avec une requête appropriée. Mais cette vérification reste au bon vouloir de l’utilisateur du SGBD (le développeur de l’application), j’aimerais éviter cette confiance aveugle (je suis à la fois le développeur et le DBA sur ce projet et je ne me fais moi-même pas confiance ;-) ou plutôt : pourquoi se priver de sécurités quand on peut en mettre).

Variante:

Pour information, j’ai déjà réfléchi à ce genre de problème dans le cas où la troisième règle aurait été :
Un ARTICLE peut être proposé en une hauteur et seulement une.
Dans ce cas j’aurais modélisé une entité ARTICLE et deux entités spécialisées ARTICLE_AVEC_HAUTEUR et ARTICLE_SANS_HAUTEUR.
J’aurais utilisé pour ARTICLE l’identification relative sur PLANTE, aurait garanti la contrainte de spécialisation à l’aide d’un trigger et ajouté deux contraintes d’unicité permettant d’éviter les doublons. La première dans ARTICLE_AVEC_HAUTEUR garantissant l’unicité du couple (IdPlante, Hauteur) et la seconde dans ARTICLE_SANS_HAUTEUR garantissant l’unicité de IdPlante. Tout ça en image dans le fichier 'schema02.png'.
Tout ça pour en venir au fait que cette solution ne semble pas si adéquate le jour où, par exemple, on ajoute un second attribut optionnel comme la largeur.

Au final mon problème réel mélange à la fois des séries de valeurs optionnelles (Problème) et des valeurs optionnelles (Variante).

Si quelqu’un a une idée, je suis preneur. Merci

Bonsoir titinetine,

Prenons cette partie de votre MCD/MLD :

http://www.fsmwarden.com/developpez_...uteur)by_t.png

Pour éviter d’avoir deux fois la même hauteur pour un article, il suffit au niveau conceptuel de définir un identifiant alternatif {IdArticle, Hauteur} pour l’entité-type HAUTEUR. Au niveau logique, cet identifiant donnera lieu à une clé alternative. Au niveau SQL :
Code:

1 2 3 4 5 6 7 8 CREATE TABLE HAUTEUR ( IdArticle INT NOT NULL , IdHauteur INT NOT NULL , Hauteur INT NOT NULL , CONSTRAINT HAUTEUR_PK PRIMARY KEY (IdArticle, IdHauteur) , CONSTRAINT HAUTEUR_AK UNIQUE (IdArticle, Hauteur) ) ;

Concernant la variante : en principe, le MLD suivant devrait suffire :

http://www.fsmwarden.com/developpez_...uteur)by_f.png

Le reproche qu’on peut lui faire : lors de l’exécution d’une requête de consultation, si pour l’article A1 la table HAUTEUR est vide, on ne sait pas si c’est parce que A1 est un article pour lequel la hauteur ne s’applique pas, ou bien si c’est un article pour lequel la hauteur s’applique, mais que l’on a omis de renseigner. Pour lever les doutes, il faudrait définir deux vues à l’usage des applications :
Code:

1 2 3 4 5 6 7 8 9 CREATE VIEW ARTICLE_SANS_HAUTEUR (IdArticle, IdPlante, Prix) AS SELECT IdArticle, IdPlante, Prix FROM ARTICLE ; CREATE VIEW ARTICLE_AVEC_HAUTEUR (IdArticle, IdPlante, Prix, Hauteur) AS SELECT x.IdArticle, x.IdPlante, x.Prix, y.Hauteur FROM ARTICLE AS x JOIN HAUTEUR AS y ON x.IdPlante = y.IdPlante AND x.IdArticle = y.IdArticle ;
Les mises à jour des tables seraient aussi contrôlées par les vues (sauf avec MySQL lequel, si je me souviens, ne sait pas faire :moinsser:).

Question relative à une discussion précédente : avez-vous choisi votre SGBD ?

29/06/2012, 08h53
Invité

Bonjour fsmrel et merci de votre aide

Je n’ai peut-être pas assez expliqué mon souci. Dans votre premier message, vous indiquez comment éviter d’avoir plusieurs hauteurs identiques pour le même article et je compte effectivement garantir l’unicité de la clé alternative (ainsi que de toutes les autres clés alternatives de mon projet). Cependant, j’aimerais également éviter d’avoir deux occurrences d’articles correspondant à une même plante qui possèdent la même série de hauteur. Si c’était le cas, j’aurais donc deux occurrences distinctes en base de données qui représenteraient finalement le même article, le même concept.

Pour votre second message, le modèle que vous proposez empêche les doublons des articles qui possèdent une hauteurs (un ‘Chêne 125’ ne pourra pas être en double) mais est-ce qu’il empêche d’entrer deux (ou plusieurs) articles d’une même plante qui n’ont pas de hauteur ? On pourrait se retrouver avec le ‘Pommier’ puis avec un autre ‘Pommier’ mais cela doit être empêché.

En ce qui concerne le SGBD, ma décision n’est pas arrêtée mais je pense me diriger vers PostgreSQL. Je n’ai qu’une modeste expérience en SGBD sur MySQL et Oracle mais après avoir brièvement regardé une page comparative (http://en.wikipedia.org/wiki/Compari...gement_systems) et réfléchi à mes contraintes (gratuité, polyvalence), PostgreSQL me semble intéressant.
29/06/2012, 13h41
fsmrel

Bonjour titinetine,

Citation:

Envoyé par titinetine

j’aimerais également éviter d’avoir deux occurrences d’articles correspondant à une même plante qui possèdent la même série de hauteur.

Je retire déjà de ceci la règle suivante :

Si l’article A1 de la plante P1 a la hauteur H1 et si l’article A2 de cette plante P1 a aussi la hauteur H1 alors A1 et A2 sont le même article.

C'est-à-dire la dépendance fonctionnelle {PLANTE, HAUTEUR} -> ARTICLE

Est-ce cela nécessaire ? Si oui, est-ce suffisant ?
29/06/2012, 14h11
Invité
Alors je corrige ma phrase en ajoutant un s à :

Citation:

série de hauteurs

Dans le cas du problème initial, on a une série optionnelle d’une ou plusieurs hauteurs (cf Régle de gestion n°1). On peut donc dire :

Citation:

Si l’article A1 de la plante P1 a la hauteur H1 et si l’article A2 de cette plante P1 a aussi la hauteur H1 alors A1 et A2 sont le même article.

mais on peut aussi dire :
- Si l’article A1 de la plante P1 a les hauteurs H1, H2, ..., Hn et si l’article A2 de cette plante P1 a aussi la hauteur H1, H2, ..., Hn alors A1 et A2 sont le même article.
- Si l’article A1 de la plante P1 n’a pas de hauteur et si l’article A2 de cette plante P1 n’a pas non plus de hauteur alors A1 et A2 sont le même article.
Dans le cas de la variante, on a une hauteur optionnelle. On peut donc dire :

Citation:

Si l’article A1 de la plante P1 a la hauteur H1 et si l’article A2 de cette plante P1 a aussi la hauteur H1 alors A1 et A2 sont le même article.

mais on peut aussi dire :
- Si l’article A1 de la plante P1 n’a pas de hauteur et si l’article A2 de cette plante P1 n’a pas non plus de hauteur alors A1 et A2 sont le même article.
Pour répondre à votre question dans le cas de la variante du problème, la DF proposée est donc nécessaire mais pas suffisante.
29/06/2012, 16h19
fsmrel

Avant d’aller plus loin, je formule à nouveau la règle suivante :

Si l’article A1 de la plante P1 a la hauteur H1 et si l’article A2 de cette plante P1 a aussi la hauteur H1 alors A1 et A2 sont le même article.

Cette règle est-elle valide, oui ou non ?

Si elle n’est pas valide, c’est par exemple que (pour la plante P1) A1 et A2 ont en commun par exemple H1, H2, H3, mais A1 a H4 que n’a pas A2, auquel cas A1 et A2 ne seraient pas le même article, même en ayant en commun H1, H2 et H3. Ils ne seraient le même article que si et seulement si A1 et A2 avaient en commun exactement les mêmes Hi (i = 1, ..., n).

Qu’en est-il ?
29/06/2012, 16h38
Invité

Citation:

Envoyé par fsmrel

Si l’article A1 de la plante P1 a la hauteur H1 et si l’article A2 de cette plante P1 a aussi la hauteur H1 alors A1 et A2 sont le même article.

Cette règle est-elle valide, oui ou non ?

Non, dans le cas du problème initial, cette règle n'est pas valide car effectivement :

Citation:

Envoyé par fsmrel

Ils ne seraient le même article que si et seulement si A1 et A2 avaient en commun exactement les mêmes Hi (i = 1, ..., n).

petit bug (assertion)

Bonsoir,

Citation:

Envoyé par titinetine

effectivement :

Citation:

Envoyé par fsmrel

Ils ne seraient le même article que si et seulement si A1 et A2 avaient en commun exactement les mêmes Hi (i = 1, ..., n).

D’accord.
Il faut donc en passer par une assertion pour interdire les doublons (un trigger si le SGBD ne propose pas l’instruction CREATE ASSERTION).

Exemple :

Jeu de tables :

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
CREATE TABLE PLANTE
(
        PlanteId               INT              NOT NULL
      , PlanteNom              VARCHAR(128)     NOT NULL
    , CONSTRAINT PLANTE_PK PRIMARY KEY (PlanteId)
) ;
 
CREATE TABLE ARTICLE
(
        PlanteId               INT              NOT NULL
      , ArticleId              INT              NOT NULL
      , ArticlePrix            VARCHAR(64)      NOT NULL
    , CONSTRAINT ARTICLE_PK PRIMARY KEY (PlanteId, ArticleId)
    , CONSTRAINT ARTICLE_PLANTE_FK FOREIGN KEY (PlanteId)
                 REFERENCES PLANTE (PlanteId) 
) ;
 
CREATE TABLE HAUTEUR
(
        PlanteId               INT              NOT NULL
      , ArticleId              INT              NOT NULL
      , HauteurId              INT              NOT NULL
      , Hauteur                INT              NOT NULL
    , CONSTRAINT HAUTEUR_PK PRIMARY KEY (PlanteId, ArticleId, HauteurId)
    , CONSTRAINT HAUTEUR_AK UNIQUE (PlanteId, ArticleId, Hauteur)
    , CONSTRAINT HAUTEUR_ARTICLE_FK FOREIGN KEY (PlanteId, ArticleId)
                 REFERENCES ARTICLE (PlanteId, ArticleId) 
) ;

:nono: A noter la clé alternative HAUTEUR_AK (PlanteId, ArticleId, Hauteur) pour la table HAUTEUR, clé sans laquelle on aurait des problèmes en cas de doublons sur le triplet d’attributs {PlanteId, ArticleId, Hauteur}.

Un début de jeu d’essai :

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
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
 
INSERT INTO PLANTE (PlanteId, PlanteNom) VALUES (1, 'Princess of Relationland') ;
INSERT INTO PLANTE (PlanteId, PlanteNom) VALUES (2, 'La reine des pommes') ;
INSERT INTO PLANTE (PlanteId, PlanteNom) VALUES (3, 'Le doux cactus') ;
INSERT INTO PLANTE (PlanteId, PlanteNom) VALUES (4, 'La rose d’orient') ;
INSERT INTO PLANTE (PlanteId, PlanteNom) VALUES (5, 'La poire à Mimile') ;
 
INSERT INTO ARTICLE (PlanteId, ArticleId, ArticlePrix) VALUES (1, 1, 110) ;
INSERT INTO ARTICLE (PlanteId, ArticleId, ArticlePrix) VALUES (1, 2, 120) ;
INSERT INTO ARTICLE (PlanteId, ArticleId, ArticlePrix) VALUES (1, 3, 130) ;
INSERT INTO ARTICLE (PlanteId, ArticleId, ArticlePrix) VALUES (1, 4, 140) ;
INSERT INTO ARTICLE (PlanteId, ArticleId, ArticlePrix) VALUES (1, 5, 150) ;
INSERT INTO ARTICLE (PlanteId, ArticleId, ArticlePrix) VALUES (1, 6, 160) ;
INSERT INTO ARTICLE (PlanteId, ArticleId, ArticlePrix) VALUES (1, 7, 170) ;
INSERT INTO ARTICLE (PlanteId, ArticleId, ArticlePrix) VALUES (1, 8, 180) ;
INSERT INTO ARTICLE (PlanteId, ArticleId, ArticlePrix) VALUES (1, 9, 190) ;
INSERT INTO ARTICLE (PlanteId, ArticleId, ArticlePrix) VALUES (1, 10, 1000) ;
INSERT INTO ARTICLE (PlanteId, ArticleId, ArticlePrix) VALUES (1, 11, 1100) ;
INSERT INTO ARTICLE (PlanteId, ArticleId, ArticlePrix) VALUES (1, 12, 1200) ;
 
INSERT INTO ARTICLE (PlanteId, ArticleId, ArticlePrix) VALUES (2, 1, 210) ;
INSERT INTO ARTICLE (PlanteId, ArticleId, ArticlePrix) VALUES (2, 2, 220) ;
INSERT INTO ARTICLE (PlanteId, ArticleId, ArticlePrix) VALUES (2, 3, 230) ;
 
INSERT INTO ARTICLE (PlanteId, ArticleId, ArticlePrix) VALUES (3, 1, 310) ;
INSERT INTO ARTICLE (PlanteId, ArticleId, ArticlePrix) VALUES (3, 2, 320) ;
INSERT INTO ARTICLE (PlanteId, ArticleId, ArticlePrix) VALUES (3, 3, 330) ;
 
INSERT INTO ARTICLE (PlanteId, ArticleId, ArticlePrix) VALUES (4, 1, 410) ;
INSERT INTO ARTICLE (PlanteId, ArticleId, ArticlePrix) VALUES (4, 2, 420) ;
INSERT INTO ARTICLE (PlanteId, ArticleId, ArticlePrix) VALUES (4, 3, 430) ;
 
INSERT INTO ARTICLE (PlanteId, ArticleId, ArticlePrix) VALUES (5, 1, 300) ;
 
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (1, 1, 1, 100) ;
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (1, 1, 2, 200) ;
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (1, 1, 3, 300) ;
--INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (1, 1, 4, 100) ; -- Rejet
--INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (1, 1, 4, 114) ;
 
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (1, 2, 1, 200) ;
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (1, 2, 2, 300) ;
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (1, 2, 3, 100) ;
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (1, 2, 4, 400) ;
 
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (1, 3, 1, 131) ;
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (1, 3, 2, 200) ;
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (1, 3, 3, 100) ;
 
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (1, 4, 1, 300) ;
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (1, 4, 2, 100) ;
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (1, 4, 3, 200) ;
--INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (1, 4, 4, 144) ;
 
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (1, 5, 1, 300) ;
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (1, 5, 2, 152) ;
 
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (1, 6, 1, 300) ;
 
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (1, 7, 1, 300) ;
 
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (1, 8, 1, 300) ;
 
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (1, 9, 1, 100) ;
 
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (1, 10, 1, 100) ;
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (1, 10, 2, 200) ;
 
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (1, 11, 1, 200) ;
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (1, 11, 2, 300) ;
 
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (1, 12, 1, 200) ;
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (1, 12, 2, 300) ;
 
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (2, 1, 1, 211) ;
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (2, 1, 2, 212) ;
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (2, 1, 3, 213) ;
 
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (2, 2, 1, 221) ;
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (2, 2, 2, 222) ;
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (2, 2, 3, 223) ;
 
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (2, 3, 1, 231) ;
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (2, 3, 2, 232) ;
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (2, 3, 3, 233) ;
 
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (3, 1, 1, 311) ;
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (3, 1, 2, 312) ;
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (3, 1, 3, 313) ;
 
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (3, 2, 1, 321) ;
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (3, 2, 2, 322) ;
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (3, 2, 3, 323) ;
 
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (3, 3, 1, 331) ;
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (3, 3, 2, 332) ;
INSERT INTO HAUTEUR (PlanteId, ArticleId, HauteurId, Hauteur) VALUES (3, 3, 3, 333) ;
 
 
SELECT '' AS PLANTE, * FROM PLANTE ;
SELECT '' AS ARTICLE, * FROM ARTICLE ;
SELECT '' AS HAUTEUR, * FROM HAUTEUR ;

Mise en oeuvre d’une vue pour simplifier la tâche de rejet des doublons : pour chaque hauteur d'un article, on adjoint le nombre de hauteurs associées à l'article :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
CREATE VIEW HAUTEUR_CARD_ARTICLE (PlanteId, ArticleId, Hauteur, Card) AS
 
SELECT x.PlanteId, x.ArticleId, x.Hauteur, y.Card
FROM
(
         SELECT DISTINCT i.PlanteId, i.ArticleId, i.Hauteur      
         FROM   HAUTEUR AS i JOIN HAUTEUR as j
                ON i.PlanteId = j.PlanteId
                AND i.ArticleId = j.ArticleId
) AS x
JOIN 
(
         SELECT PlanteId, ArticleId, COUNT(*) AS Card
         FROM   HAUTEUR
         GROUP BY PlanteId, ArticleId
) AS y
ON x.PlanteId = y.PlanteId AND x.ArticleId = y.ArticleId ;

L’assertion :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
CREATE ASSERTION Assert_01 CHECK 
 (UNIQUE
    ( 
     SELECT DISTINCT i.PlanteId, i.ArticleId, COUNT (DISTINCT i.Hauteur) AS Card 
     FROM   HAUTEUR_CARD_ARTICLE AS i JOIN HAUTEUR_CARD_ARTICLE as j
            ON i.PlanteId = j.PlanteId
            AND i.ArticleId <> j.ArticleId
            AND i.Hauteur = j.Hauteur
            AND i.Card = j.Card         
     GROUP BY i.PlanteId, i.ArticleId
 
     INTERSECT 
 
     SELECT PlanteId, ArticleId, COUNT(*) AS Card
     FROM   HAUTEUR
     GROUP BY PlanteId, ArticleId 
    )
 ) ;

Décomposition de l’assertion :

1er temps :

A chaque ligne HAUTEUR(i) on adjoint le nombre de hauteurs que comporte l'article auquel cette ligne fait référence. Appelons R1 le résultat intermédiaire. Pour simplifier les requêtes qui suivront, on passe par la vue HAUTEUR_CARD_ARTICLE pour produire R1.

2e temps :

Pour chaque plante, on recherche dans R1 les articles qui ont même cardinalité (même nombre de hauteurs) et qui ont au moins une hauteur en commun. Appelons R2 le résultat intermédiaire.

3e temps :

On ne retient de R2 que les lignes qui ne constituent pas des sous-ensembles stricts de HAUTEUR.

Je ne doute pas que la requête soit améliorable (elle contient même un petit bug qu'il faudrait évacuer), à vous de jouer... :D

Au sujet de la variante :

Il est vrai qu’on pourrait avoir des doublons dans le cas des articles sans hauteur. Pour reprendre votre diagramme, le plus simple pour éviter cela est de ne pas faire participer l’attribut IdArticle à la clé primaire de ArticleSansHauteur :

http://www.fsmwarden.com/developpez_...eur)by_f_2.png

Vous noterez la contrainte (informelle mais parlante) de partitionnement (XT = exclusion et totalité) : un article est soit avec hauteur, soit sans.

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
 
CREATE TABLE PLANTE
(
        PlanteId               INT              NOT NULL
      , PlanteNom              VARCHAR(128)     NOT NULL
    , CONSTRAINT PLANTE_PK PRIMARY KEY (PlanteId)
) ;
 
CREATE TABLE ARTICLE
(
        PlanteId               INT              NOT NULL
      , ArticleId              INT              NOT NULL
      , ArticlePrix            VARCHAR(64)      NOT NULL
    , CONSTRAINT ARTICLE_PK PRIMARY KEY (PlanteId, ArticleId)
    , CONSTRAINT ARTICLE_PLANTE_FK FOREIGN KEY (PlanteId)
                 REFERENCES PLANTE (PlanteId) 
) ;
 
CREATE TABLE ARTICLE_SANS_HAUTEUR
(
        PlanteId               INT              NOT NULL
      , ArticleId              INT              NOT NULL
    , CONSTRAINT ARTICLE_SANS_HAUTEUR_PK PRIMARY KEY (PlanteId)
    , CONSTRAINT ARTICLE_SANS_HAUTEUR_PLANTE_FK FOREIGN KEY (PlanteId, ArticleId)
                 REFERENCES ARTICLE (PlanteId, ArticleId) 
) ;
CREATE TABLE ARTICLE_AVEC_HAUTEUR
(
        PlanteId               INT              NOT NULL
      , ArticleId              INT              NOT NULL
      , Hauteur                INT              NOT NULL
    , CONSTRAINT ARTICLE_AVEC_HAUTEUR_PK PRIMARY KEY (PlanteId, ArticleId)
    , CONSTRAINT ARTICLE_AVEC_HAUTEUR_PLANTE_FK FOREIGN KEY (PlanteId, ArticleId)
                 REFERENCES ARTICLE (PlanteId, ArticleId) 
) ;

Pour l’exclusion :

Code:

1
2
3
4
5
6
7
8
9
10
CREATE ASSERTION Assert_02 CHECK 
 (NOT EXISTS
    (  
     SELECT '' AS XT, PlanteId, ArticleId
     FROM   ARTICLE_SANS_HAUTEUR
     INTERSECT
     SELECT '' AS XT, PlanteId, ArticleId
     FROM   ARTICLE_AVEC_HAUTEUR
    )
 ) ;

Bonjour et merci pour vos conseils
Désolé pour cette réponse un peu tardive.

Il y a une partie que je ne comprends pas dans la vue ci-dessous :

Citation:

Envoyé par fsmrel

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
CREATE VIEW HAUTEUR_CARD_ARTICLE (PlanteId, ArticleId, Hauteur, Card) AS
 
SELECT x.PlanteId, x.ArticleId, x.Hauteur, y.Card
FROM
(
         SELECT DISTINCT i.PlanteId, i.ArticleId, i.Hauteur      
         FROM   HAUTEUR AS i JOIN HAUTEUR as j
                ON i.PlanteId = j.PlanteId
                AND i.ArticleId = j.ArticleId
) AS x
JOIN 
(
         SELECT PlanteId, ArticleId, COUNT(*) AS Card
         FROM   HAUTEUR
         GROUP BY PlanteId, ArticleId
) AS y
ON x.PlanteId = y.PlanteId AND x.ArticleId = y.ArticleId ;

Pourquoi cette jointure de HAUTEUR avec HAUTEUR ?

Bonsoir,

Citation:

Envoyé par titinetine

Pourquoi cette jointure de HAUTEUR avec HAUTEUR ?

On ne se surveille jamais assez... Il y a effectivement une scorie là-dedans, j’ai dû m'embrouiller dans les copier/coller, il faut remplacer la partie :
Code:

1 2 3 4 SELECT DISTINCT i.PlanteId, i.ArticleId, i.Hauteur FROM HAUTEUR AS i JOIN HAUTEUR AS j ON i.PlanteId = j.PlanteId AND i.ArticleId = j.ArticleId
Tout simplement par :
Code:

1 2 SELECT PlanteId, ArticleId, Hauteur FROM HAUTEUR
Quant à la chasse aux doublons, la clause UNIQUE de l’assertion est à remplacer, par quoi je ne sais pas trop, mais la partie intersection produit bien (normalement !) tous les articles en double :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 SELECT DISTINCT '' AS Assert_01, i.PlanteId, i.ArticleId, COUNT (DISTINCT i.Hauteur) AS Card FROM HAUTEUR_CARD_ARTICLE AS i JOIN HAUTEUR_CARD_ARTICLE as j ON i.PlanteId = j.PlanteId AND i.ArticleId <> j.ArticleId AND i.Hauteur = j.Hauteur AND i.Card = j.Card GROUP BY i.PlanteId, i.ArticleId INTERSECT SELECT '', PlanteId, ArticleId, COUNT(*) AS Card FROM HAUTEUR GROUP BY PlanteId, ArticleId
Merci de continuer à vérifier de votre côté...

Et j'espère qu'il ne traîne pas trop d'autres copier/coller mal-t-à propos !

09/08/2012, 16h53
Invité

Bonjour

Je reviens ajouter la solution pour laquelle j'ai optée.

Je travaille avec PostgreSQL 8.4 et n'est donc pas de possibilité d'effectuer des assertions.

Pour le cas du problème initial ("Un ARTICLE peut être proposé en une ou plusieurs hauteurs."), afin d'éviter les doublons, j'effectue une division relationnelle exacte qui me permet de trouver, parmi les articles liés à la même plante qu'un article X, excepté X lui-même, tous les articles ayant exactement la même série de hauteurs que X possède.

Cette vérification est déclenchée sur l'un des événements suivants :
- INSERT d'un article,
- INSERT, UPDATE, DELETE d'une hauteur

Les hauteurs et les articles étant dans des tables différentes, on ne peut donc pas insérer un nouvel article et ses hauteurs au moyen d'une seule requête SQL et cela peut poser problème si je veux ajouter un article A2 avec les hauteur 100 et 200 si un autre article A1 existe déjà pour la même plante sans aucune hauteur, par exemple. En effet, juste après avoir insérer A2 et juste avant d'insérer ses hauteurs, ma vérification va montrer que A2 est en conflit avec A1.

Pour pallier ce souci, il faut donc effectuer la vérification une fois toute les hauteurs insérées. Sur PostgreSQL 8.4, cela peut se faire à l'aide de trigger différé (ONSTRAINT TRIGGER).

Pour le cas de la variante du problème ("Un ARTICLE peut être proposé en une hauteur et seulement une."), je n'ai pas choisi de modéliser un article général ainsi que deux spécialisations (ArticleAvecHauteur et ArticleSansHauteur) mais seulement d'ajouter une table hauteur avec pour clé primaire la même clé primaire que celle d'article.
De même, afin d'éviter les doublons, j'effectue une vérification qui me permet de trouver, parmi les articles liés à la même plante qu'un article X, excepté X lui-même, tous les articles ayant exactement la même hauteur que X possède.
Et de même cette vérification est différée.

Ce choix me permet ainsi d'ajouter beaucoup plus facilement d'autres attributs optionnels comme largeur par exemple.