Discussion :

[StringBuilder]

Bonjour,

J'ai un problème qui m'empêche de dormir (du coup je suis pas certain d'avoir les idées très claires...)

J'ai mettons une entreprise qui étudie des dossiers (appels d'offre par exemple).
Pour chaque dossier, elle va émettre une ou plusieurs offres.
Ces offres comportent des "potentiels", à savoir des lignes de produit avec un chiffre d'affaire envisagé.

Le potentiel de toutes les offres d'un dossier en cours est calculé en prenant le max(ca) pour chaque produit parmi toutes les offres d'un dossier.

Cependant, au bout d'un moment, une offre est retenue parmi toutes, et à ce moment le potentiel devient la liste des CA de l'offre gagnante.

Au départ, je suis parti sur cette structure :

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
 
create table dossier
(
	id int primary key identity,
	nom varchar(10) unique,
        id_gagnante int null references offre(id)
);
 
create table offre
(
	id int primary key identity,
	id_dossier int not null references dossier(id),
	nom varchar(10) unique,
	unique (id_dossier, id)
);
go
 
create table potentiel
(
	id int primary key identity,
	id_offre int not null references offre(id),
	produit varchar(10) not null,
	ca int not null
);

Outre la clé étrangère sur dossier.id_gagnante qui n'est pas très pratique à créer, cette solution, pourtant la première qui m'est venue à l'esprit, ne me plaît pas trop dans la mesure où je vais avoir de nombreux NULL dans cette colonne, ce qui ne va pas arranger les performances de ma requête de calcul des potentiels (et ça fait un moment que je lis sur le forum que les NULL c'est pour les nuls).
Et au final, telle qu'elle, cette solution ne garanti pas que l'offre gagnante fait partie du dossier.

Je me suis donc dit "ok, ben c'est facile, il suffit de faire une table "gagnante" qui hérite de dossier, et contient la référence à l'offre du dossier qui est gagnante.

Code sql :

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

1
2
3
4
5
6
 
create table gagnante
(
	id_dossier int primary key references dossier(id),
	id_offre int references offre(id)
);

Sauf que là, je ne garanti pas que l'offre gagnante est bien rattachée à son dossier.
Du coup, je dois rajouter une clé étrangère de plus :

Code sql :

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

1
2
3
4
5
6
7
 
create table gagnante
(
	id_dossier int primary key references dossier(id),
	id_offre int references offre(id),
	foreign key (id_dossier, id_offre) references offre(id_dossier, id)
);

Sauf que offre(id_dossier, id) n'est pas une clé candidate dans offre. Le fait de la créer la rend complètement redondante avec offre(id) dans la mesure où cette dernière est déjà la clé primaire : ça ne me plait pas.

J'ai donc pensé à faire autrement : dériver de offre, et rendre unique le numéro de dossier :

Code sql :

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

1
2
3
4
5
6
 
create table gagnante
(
	id_offre int primary hey references offre(id),
	id_dossier int not null unique
);

Seulement à nouveau, aucun moyen de garantir que id_dossier est bien celui qui vient de l'offre.
J'ai tenté une computed column, mais le fait d'aller chercher l'id du dossier de l'offre dans une autre table n'est pas déterministe, et donc impossible de mettre un index unique dessus.
Je peux toujours m'en sortir en faire une clé étrangère sur offre(id, id_dossier) mais c'est toujours aussi moche.

Reste alors la solution du trigger : aller vérifier, pour chaque modification, que le dossier est bien celui de l'offre gagnante.
Seulement, pas moyen de faire une jointure entre inserted et deleted à coup sûr sans ajouter une clé primaire auto-incrémentée. Ce qui ne me plaît pas du tout non plus vu que ça perd de la place pour rien, et fait sauter mon héritage : du coup je suis obligé de supprimer toutes les lignes de deleted puis d'insérer toutes les lignes de inserted... c'est presque encore plus moche...

Toute suggestion est la bienvenue

Ce qui serait vraiment top, c'est un mécanisme permettant de créer un index unique sur gagnante(offre.id_dossier), offre.id_dossier étant déductible de gagnante.id_offre sans même avoir à recopier l'info dans la table gagnante...
En gros, une linked column plutôt qu'une computed column. J'imagine que ça existe pas...
Pourtant ça résoudrait mon problème de manière élégante et éviterait de coller un trigger tout moche pour calculer le dossier en fonction de l'offre

Voici ce que j'ai pour le moment :

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
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
 
drop view v_potentiel;
drop table potentiel;
drop table gagnante;
drop table offre;
drop table dossier;
go
 
create table dossier
(
	id int primary key identity,
	nom varchar(10) unique
);
 
create table offre
(
	id int primary key identity,
	id_dossier int not null references dossier(id),
	nom varchar(10) unique,
	unique (id_dossier, id)
);
go
 
create table gagnante
(
	id_dossier int primary key references dossier(id),
	id_offre int references offre(id),
	foreign key (id_dossier, id_offre) references offre(id_dossier, id)
);
go
 
create table potentiel
(
	id int primary key identity,
	id_offre int not null references offre(id),
	produit varchar(10) not null,
	ca int not null
);
go
 
create trigger trg_gagnante_dossier
on gagnante
instead of insert, update
as
begin
	delete gagnante
	where id_offre in (select id_offre from deleted);
 
	insert into gagnante (id_offre, id_dossier)
	select i.id_offre, o.id_dossier
	from inserted i
	inner join offre o on o.id = i.id_offre;
end;
go
 
create view v_potentiel (dossier, produit, ca)
as
select d.nom, p.produit, max(p.ca)
from dossier d
inner join offre o on o.id_dossier = d.id
inner join potentiel p on p.id_offre = o.id
where not exists
(
	select null
	from gagnante g
	where g.id_dossier = d.id
)
group by d.nom, p.produit
union all
select d.nom, p.produit, p.ca
from gagnante g
inner join dossier d on d.id = g.id_dossier
inner join offre o on o.id = g.id_offre
inner join potentiel p on p.id_offre = o.id
go
 
insert into dossier (nom) values ('Dossier 1'), ('Dossier 2');
insert into offre (id_dossier, nom) values (1, 'Offre 1.1'), (1, 'Offre 1.2'), (2, 'Offre 2.1');
insert into potentiel (id_offre, produit, ca) values (1, 'P1', 500), (1, 'P2', 100), (1, 'P3', 200), (2, 'P1', 1000), (2, 'P3', 50), (3, 'P1', 800), (3, 'P2', 1500);
 
select dossier, produit, ca
from v_potentiel
order by dossier, produit, ca;
 
insert into gagnante (id_offre) values (2);
 
select dossier, produit, ca
from v_potentiel
order by dossier, produit, ca;
 
update gagnante set id_offre = 1 where id_dossier = 1;
 
select dossier, produit, ca
from v_potentiel
order by dossier, produit, ca;
 
select * from gagnante
update gagnante set id_offre = 3 where id_dossier = 1;
select * from gagnante
update gagnante set id_dossier = 1 where id_offre = 3;
select * from gagnante

[jcdentons]

Bonjour StringBuilder,

Vous pourriez conserver votre structure de base, avec la foreign key de dossier.id_gagnante -> offre.id en mode "not null".
Vous pouvez insérer une offre par défaut ayant comme id "0".
Puis lors de la création de la table dossier vous faites référence par défaut à cette offre.

Ce qui donnerait, grosso modo :

Code :

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
create table dossier
(
	id int primary key identity,
	nom varchar(10) unique,
        id_gagnante int null references offre(id)
);
 
create table offre
(
	id int primary key identity,
	id_dossier int not null references dossier(id),
	nom varchar(10) unique,
	unique (id_dossier, id)
);
go
 
create table potentiel
(
	id int primary key identity,
	id_offre int not null references offre(id),
	produit varchar(10) not null,
	ca int not null
);
go
 
-- Insertion des données par défaut
insert into dossier(nom, id_gagnante) values ("DOSSIER PAR DEFAUT", null); -- ID = 0
insert into offre(id_dossier, nom) values (0, "OFFRE PAR DEFAUT"); -- ID = 0
update dossier set id_gagnante = 0 where id = 0;
go
 
-- Modification de la définition de la table dossier
alter table dossier alter column id_gagnante int not null default value 0;

[hmira]

Bonjour StringBuilder,

Je vous propose la solution décrite dans les scripts ci-dessous :

Quelques remarques

- Pour plus de clarté, j'ai renommé la colonne "Id" de chacune des table "Dossier", "Offre" et "Potentiel" respectivement en "Id_Dossier", "Id_Offre" et "Id_Potentiel".
- J'ai renommé également la table "gagnante" en "OffreRetenue". En effet, j'ai horreur des mots comme "gagnante" vs "Perdante" etc , je préfère les mots comme Solidaire, Philanthrope, Altruiste etc.
- J'ai supprimé de la table Offre la contrainte unique (id_dossier, Id_Offre) inutile puisque Id_Offre est déjà une clé primaire de la table Offre ! Vous l'avez aussi remarqué et déploré !
- J'ai utilisé une vue matérialisée "VW_Unique_OffreRetenue_par_Dossier" pour respecter la contrainte de cardinalité (0 ou 1) de la relation entre "Dossier" et "Offre retenue", c.à.d. "Au plus, une et une seule offre pourra être retenue pour un dossier donné".
- Le modèle que je vous propose me parait strict et ficelé. A charge à vous de voir ensuite comment mettre en œuvre les Insert, Update, Delete notamment dans la table OffreRetenue. Cela peut être effectuée soit par l'Application elle-même, soit par des Trigger de type Instead of soit par d'autres techniques. En tout cas, quelque soit la méthode utilisée, le modèle tel que décrit est strict et interdirait d'outrepasser les règles de gestion.

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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
DROP VIEW dbo.VW_Unique_OffreRetenue_par_Dossier
GO
DROP TABLE dbo.Potentiel;
GO
DROP TABLE dbo.OffreRetenue;  -- Il s'agit de la même table "gagnante" renommée en "OffreRetenue"
GO
DROP TABLE dbo.Offre;
GO
DROP TABLE dbo.Dossier;
GO
 
 
CREATE TABLE dbo.Dossier
(
	Id_Dossier int primary key identity,
	Nom varchar(10) unique,
);
GO
 
CREATE TABLE dbo.Offre
(
	Id_Offre int primary key identity,
	Id_Dossier int not null references dossier(Id_Dossier),
	Nom varchar(10) unique
);
GO
 
CREATE TABLE dbo.Potentiel
(
	Id_Potentiel int primary key identity,
	Id_Offre int not null references Offre(Id_Offre),
	Produit varchar(10) not null,
	CA int not null
);
GO
 
 
 -- Il s'agit de la table gagnante que j'ai renommé en OffreRetenue
 -- En effet, j'ai horreur des mots comme "gagnante" vs "Perdante" etc , je préfère les  mots comme Solidaire, Philanthrope , Altruiste etc.
 
CREATE TABLE dbo.OffreRetenue
(
	Id_OffreRetenue int primary key identity,    --  il s'agit d'une clé primaire technique
	Id_Offre int references Offre(Id_Offre),
	unique (Id_Offre),  -- cette contrainte unique créera automatiquement un index physique sous-jacent et qui peut être très utile
	foreign key (Id_Offre ) references Offre(Id_Offre)
);
GO
 
-- J'ai utilisé une vue matérialisée "VW_Unique_OffreRetenu_par_Dossier" pour respecter la contrainte de cardinalité (0 ou 1)
--  de la relation entre Dossier et Offre retenue, c.à.d. "Au plus une seule offre pourra être retenue pour un dossier donné".
CREATE VIEW dbo.VW_Unique_OffreRetenue_par_Dossier
WITH SCHEMABINDING
AS
SELECT r.Id_Offre, o.Id_Dossier
FROM dbo.OffreRetenue r
INNER JOIN dbo.Offre o
        ON r.Id_Offre = o.Id_Offre
 
GO
 
CREATE UNIQUE CLUSTERED INDEX IX_VW_Unique_OffreRetenue_par_Dossier ON dbo.VW_Unique_OffreRetenue_par_Dossier(Id_Dossier);
GO
 
insert into dossier (nom)  values ('Dossier 1'), ('Dossier 2');
-- OK : (2 row(s) affected)
 
insert into offre (id_dossier, nom) values (1, 'Offre 1.1'), (1, 'Offre 1.2'), (2, 'Offre 2.1');
-- OK : (3 row(s) affected)
 
insert into potentiel (id_offre, produit, ca) values (1, 'P1', 500), (1, 'P2', 100), (1, 'P3', 200), (2, 'P1', 1000), (2, 'P3', 50), (3, 'P1', 800), (3, 'P2', 1500);
-- OK (7 row(s) affected)
 
insert into OffreRetenue (id_offre) values (2); -- c.à.d. Dossier : 1   'Offre 1.2'
--OK (1 row(s) affected)
 
 
insert into OffreRetenue (id_offre) values (3); -- c.à.d. Dossier : 2   ''Offre 2.1'
-- OK : (1 row(s) affected)
 
insert into OffreRetenue (id_offre) values (1); -- c.à.d Dossier : 1   'Offre 1.1'
/* Résultat : !!!  KO !!! Et c'est normal !  En effet le dossier n° 1  a déjà une offre retenue 'Offre 1.2'
 Msg 2601, Level 14, State 1, Line 79
Cannot insert duplicate key row in object 'dbo.VW_Unique_OffreRetenue_par_Dossier' with unique index 'IX_VW_Unique_OffreRetenue_par_Dossier'.
The duplicate key value is (1).
The statement has been terminated.
*/

A+

[escartefigue]

Ce genre de sujet est beaucoup plus facile a aborder lors de l'étude conceptuelle.
Le bon endroit pour poster eut été le forum modélisation, en effet, le choix de la BDD (ici SQL Server) n'est pas impactant sur la démarche.

[StringBuilder]

Je vous propose la solution décrite dans les scripts ci-dessous :

Merci pour votre réponse.
J'avais en effet pensé à faire une vue indexée, mais je ne pensais pas qu'il était possible d'y adjoindre un index unique !
C'est donc la solution qui me plaît le plus

Ce genre de sujet est beaucoup plus facile a aborder lors de l'étude conceptuelle.
Le bon endroit pour poster eut été le forum modélisation, en effet, le choix de la BDD (ici SQL Server) n'est pas impactant sur la démarche.

Moyennement d'accord : certains SGBD ne supportent pas de clés alternatives pour les clé étranges, de vues indexées, et encore moins d'index unique sur des vues (donc avec contrainte supplémentaire indépendante des tables elles-mêmes)...

Ceci dit, je m'étais posé la question de l'endroit où poster la question, mais c'est ni MCD ni MPD, c'est bel et bien au moment de la transformation du MPD en instructions SQL qu'il y avait un souci, donc spécifique au SGBD retenu. Le MPD était très clair dès le départ, et les contraintes parfaitement identifiées.

[SQLpro]

Moyennement d'accord : certains SGBD ne supportent pas de clés alternatives pour les clé étranges;

Lequels ? Parce que cela fait partie de le norme SQL dès l'origine. C'est même dans le papier de 1970 de franck edgar codd !
A +

[StringBuilder]

Les très mauvais

Genre Access, je doute qu'il supporte ça.
Vu votre question, j'ai l'impression que MySQL le supporte, ce qui m'étonne presque (faudrait déjà qu'il sache gérer correctement les index uniques pour ça).
J'imagine que d'autres, types, Hyperbase, comportent ce genre de limitation.

Dans tous les cas, les vues indexées et index uniques sur une vue, certain, la moitié des SGBD ne le supportent pas. Donc autant au moment de la conception initiale, le SGBD importe peu, autant dans la recherche d'une méthode d'implémentation, sans connaître le SGBD cible on ne pourra pas faire les mêmes choses.

J'imagine que j'aurais eu de toutes autres réponses dans le forum MySQL, et peut-être même aussi dans le forum Oracle.

[SQLpro]

Pour ce qui est des vues "matérialisée" c'est un autre débat. Comme c'est du physique, la norme n'en parle pas, pas plus que les index ou les "storage"...

A +