Discussion :

[yoshï]

Bonjour à tous,

Je viens solliciter votre expertise en matière de conception.

Il me semble qu'il est formellement déconseillé d'avoir des champs redondants dans une base.
Je pense que la principale raison vient du risque d'intégrité lors d'une maj partielle des champs.
Que se passe t'il si 2 champs sémantiquement identiques n'indiquent plus la même valeur?!
En outre, avoir 2 champs identiques dans 2 tables différentes semble relever d'une erreur de conception car ça ne présente, à priori, aucun intérêt.

Mon problème est un peu différent. Il existe des données que je peux avoir par le biais de requêtes lourdes et complexes (multiples jointures avec groupement, tri et comptage).
Dès lors pour éviter d'avoir à lancer ces requêtes en permanence, la solution d'un champs stockant l'information peut être utile même si
il existe une alternative pour avoir la donnée sans la présence de ce champs.

Je vais essayer d'illustrer avec un exemple. Imaginons le cas suivant :

Dans une BDD répertoriant les business sur une plateforme B2B, on veut connaître le volume d'affaire généré par un commercial.
Il y a une table qui répertorie les transactions avec leurs montants. On part de cette table pour remonter vers la société associée à la transaction puis vers la table
des commerciaux et des sociétés associées. En gros il y a 2 jointures à faire et il faut ensuite faire la somme de toutes les transactions renvoyés par la requête.

Bon dans cet exemple ça passe encore mais vous voyez la problématique. Quand les requêtes commencent à être trop lourdes n'est il pas préférable de stocker l'info directement dans un champs spécifique?

Dans mon exemple, je pourrai créer un champs volume d'affaire dans la table des commerciaux et à chaque fois qu'une nouvelle transaction concernant une société associée à ce commercial est créée
j'ajoute le montant de la transaction à son volume d'affaire.
Qu'en pensez vous?

Merci pour vos conseils

[StringBuilder]

Bonjour,

Selon les SGBD, vous avez accès aux "vue stockées" (c'est à dire des vues, au détail près que les données sont stockées physiquement, et mise à jour dynamiquement en fonction des tables référencées).

Et aussi mais surtout, vous avez accès aux "colonnes calculées", qui peuvent être persistantes et résultantes d'une fonction stockée, mais en plus, peuvent être indexées !

Et ça, c'est un remède élégant à votre problématique !

Seul souci, c'est que ces colonnes calculées (en tout cas sous SQL Server) ne peuvent être persistante que si elle ne font pas d'aggrégat, ce qui semble être le cas chez vous. Dans ce cas, le moteur devra malheureusement recalculer les valeurs à chaque fois. Cependant, votre requête restera parfaitement lisible, ce qui est déjà pas mal !

Autre alternative, qui devrait fonctionner sur à peut près tous les SGBD : gérer le recalcul de façon manuelle, avec des triggers. Seul hic, ça peut être lourd à mettre en place, surtout si les règles évoluent dans le temps (par exemple, le calcul du prix total d'une facture peut s'enrichir à chaque débat politique, ils aiment bien nous rajouter des taxes à la con... Cela implique de repasser dans les X triggers simultanément, ce qu n'est pas forcément très simple à suivre.

Voici un exemple sous SQL Server 2014 pour avoir une idée de ce que ça donne.

L'exemple doit marcher depuis 2008 au moins, peut-être 2005.

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
 
drop table facture_ligne;
drop table facture;
 
drop function facture_nblignes;
drop function facture_total;
 
go
 
create table facture
(
	id int primary key not null identity,
	reference varchar(50) not null unique
);
 
create table facture_ligne
(
	id int primary key not null identity,
	facture_id int not null references facture(id),
	quantite numeric not null,
	prix money not null,
	total as (quantite * prix) persisted not null
);
go
 
create function facture_total
(
	@id int
)
returns money
as
begin
	return (select sum(total) from facture_ligne where facture_id = @id);
end;
go
 
create function facture_nblignes
(
	@id int
)
returns int
as
begin
	return (select count(id) from facture_ligne where facture_id = @id);
end;
go
 
alter table facture
add total as (dbo.facture_total(id));
go
 
alter table facture
add nblignes as (dbo.facture_nblignes(id));
go
 
declare @newid table(id int);
 
insert into facture (reference) output inserted.id into @newid values ('Facture 1');
insert into facture_ligne (facture_id, quantite, prix) values ((select top 1 id from @newid), 10, 20.49);
insert into facture_ligne (facture_id, quantite, prix) values ((select top 1 id from @newid), 100, 1.73);
insert into facture_ligne (facture_id, quantite, prix) values ((select top 1 id from @newid), 30, 139.99);
delete @newid;
 
insert into facture (reference) output inserted.id into @newid values ('Facture 2');
insert into facture_ligne (facture_id, quantite, prix) values ((select top 1 id from @newid), 10, 20.49);
delete @newid;
 
insert into facture (reference) output inserted.id into @newid values ('Facture 3');
insert into facture_ligne (facture_id, quantite, prix) values ((select top 1 id from @newid), 30, 139.99);
insert into facture_ligne (facture_id, quantite, prix) values ((select top 1 id from @newid), 100, 1.73);
insert into facture_ligne (facture_id, quantite, prix) values ((select top 1 id from @newid), 5, 14.99);
insert into facture_ligne (facture_id, quantite, prix) values ((select top 1 id from @newid), 30, 139.99);
insert into facture_ligne (facture_id, quantite, prix) values ((select top 1 id from @newid), 100, 1.73);
delete @newid;
 
insert into facture (reference) output inserted.id into @newid values ('Facture 4');
insert into facture_ligne (facture_id, quantite, prix) values ((select top 1 id from @newid), 10, 20.49);
insert into facture_ligne (facture_id, quantite, prix) values ((select top 1 id from @newid), 5, 14.99);
insert into facture_ligne (facture_id, quantite, prix) values ((select top 1 id from @newid), 100, 1.73);
insert into facture_ligne (facture_id, quantite, prix) values ((select top 1 id from @newid), 30, 139.99);
insert into facture_ligne (facture_id, quantite, prix) values ((select top 1 id from @newid), 5, 14.99);
insert into facture_ligne (facture_id, quantite, prix) values ((select top 1 id from @newid), 100, 1.73);
delete @newid;
 
select * from facture;

[yoshï]

Merci pour ces informations. Je vais étudier tout ça de près.
Pour information j'utilise MySQL comme SGBD.

[fatbob]

Bonjour,

Autre alternative, qui devrait fonctionner sur à peut près tous les SGBD : gérer le recalcul de façon manuelle, avec des triggers. Seul hic, ça peut être lourd à mettre en place, surtout si les règles évoluent dans le temps (par exemple, le calcul du prix total d'une facture peut s'enrichir à chaque débat politique, ils aiment bien nous rajouter des taxes à la con... Cela implique de repasser dans les X triggers simultanément, ce qu n'est pas forcément très simple à suivre.

Ne serait-il pas possible d'appeler une procédure stockée à partir des triggers ? Du coup il suffit de repasser dans la procédure en question...

[Flodelarab]

Bonjour,

mysql??? Est-il raisonnable d'entamer une discussion d'intégrité avec un SGBD qui n'a même pas l'obligation d'assurer l'intégrité référentielle? Tout dépend du moteur qu'il y a derrière, mais mysql n'a pas l'obligation de le faire.

[StringBuilder]

Ne serait-il pas possible d'appeler une procédure stockée à partir des triggers ? Du coup il suffit de repasser dans la procédure en question...

Effectivement, un trigger qui appelle une procédure stockée fera en partie l'affaire.

Cependant, si le calcul est complexe, selon depuis quelle table on lance la procédure stockée, on risque d'avoir des traitements différents (recalcul en cascade de données intermédiaires par exemple).

Dans mon exemple (très simple) : on n'aura pas les mêmes traitements si on supprime une ligne dans les lignes de facture (simple recalcul de l'entête) ou si on ajoute une nouvelle ligne (calcul du total de la ligne puis recalcul de l'entête).

Je vous laisse imaginer ce que ça donne avec une table de tarifs dégressifs couplé à une table de remises selon critères complexes : il faut déclencher si on change une remise, si on change une condition d'application de remise, si on change un tarifs, si on change un seuil d'application du tarif, si on change une ligne de facture, si on change un élément de la facture (ou du client ou autre) qui concerne un élément de déclenchement d'une remise...

Bref, très lourd à mettre en place, là où une colonne calculée saura toujours faire le traitement avec une unique fonction.

[SQLpro]

Bonjour à tous,

Je viens solliciter votre expertise en matière de conception.

Il me semble qu'il est formellement déconseillé d'avoir des champs redondants dans une base.
Je pense que la principale raison vient du risque d'intégrité lors d'une maj partielle des champs.

Il y en as beaucoup d'autres, notamment concernant la baisse des performances :
- accroissement du volume de données => diminution des données en cache (du fait de la redondance)
- augmentation du nombre de colonnes d'une table => difficulté d'indexation
...

Que se passe t'il si 2 champs sémantiquement identiques n'indiquent plus la même valeur?!

C'est un défaut d'intégrité et vous êtes dans la merde !

En outre, avoir 2 champs identiques dans 2 tables différentes semble relever d'une erreur de conception car ça ne présente, à priori, aucun intérêt.

Exact

Mon problème est un peu différent. Il existe des données que je peux avoir par le biais de requêtes lourdes et complexes (multiples jointures avec groupement, tri et comptage).
Dès lors pour éviter d'avoir à lancer ces requêtes en permanence, la solution d'un champs stockant l'information peut être utile même si
il existe une alternative pour avoir la donnée sans la présence de ce champs.

Je vais essayer d'illustrer avec un exemple. Imaginons le cas suivant :

Dans une BDD répertoriant les business sur une plateforme B2B, on veut connaître le volume d'affaire généré par un commercial.
Il y a une table qui répertorie les transactions avec leurs montants. On part de cette table pour remonter vers la société associée à la transaction puis vers la table
des commerciaux et des sociétés associées. En gros il y a 2 jointures à faire et il faut ensuite faire la somme de toutes les transactions renvoyés par la requête.

sur un bon SGBDR, avec une base bien conçue, mêmes des requêtes avec 15 ou 20 jointures ne posent aucun problème !

Bon dans cet exemple ça passe encore mais vous voyez la problématique. Quand les requêtes commencent à être trop lourdes n'est il pas préférable de stocker l'info directement dans un champs spécifique?

Dans mon exemple, je pourrai créer un champs volume d'affaire dans la table des commerciaux et à chaque fois qu'une nouvelle transaction concernant une société associée à ce commercial est créée
j'ajoute le montant de la transaction à son volume d'affaire.
Qu'en pensez vous?

Merci pour vos conseils

Dans le pire des cas il existe les vues indexées (SQL Server) ou les vues matérialisées (Oracle). Ceci pré-calcule des données en fonction d'une requête.

Mais avant tout commencez à maquettez votre problème sur un vrai SGBDR avec un vrai modèle et une bonne base bien indexée avec quelques centaines de milliers de lignes (minimum) dans vos tables. Vous risquez d'avoir d'agréables surprises !

A +

[yoshï]

Merci pour votre message. Je réponds avec un petit peu de retard.

Dans le pire des cas il existe les vues indexées (SQL Server) ou les vues matérialisées (Oracle). Ceci pré-calcule des données en fonction d'une requête.

A votre connaissance, est ce que MySQL dispose de ce type de fonctionnalité?

Mais avant tout commencez à maquettez votre problème sur un vrai SGBDR

Est ce que MySQL rentre dans la catégorie des "vrais SGBDR"?

quelques centaines de milliers de lignes (minimum) dans vos tables

Il y a très peu de chance pour que ma BDD atteigne un jour ce nombre d'entrées (tout au plus quelques dizaines de milliers) mais je comprends l'intérêt de faire des tests de performance sur un très grand nombre de données.

[CinePhil]

Dans le pire des cas il existe les vues indexées (SQL Server) ou les vues matérialisées (Oracle). Ceci pré-calcule des données en fonction d'une requête.

A votre connaissance, est ce que MySQL dispose de ce type de fonctionnalité?

À ma connaissance, non.

Est ce que MySQL rentre dans la catégorie des "vrais SGBDR"?

SQLPro dira que non.
http://blog.developpez.com/sqlpro/p9...oudre_aux_yeux

Il y a très peu de chance pour que ma BDD atteigne un jour ce nombre d'entrées (tout au plus quelques dizaines de milliers) mais je comprends l'intérêt de faire des tests de performance sur un très grand nombre de données.

Alors si votre BDD est bien modélisée et bien indexée et des requêtes bien écrites, même avec le mauvais MySQL, vous ne devriez pas avoir de problème de performance.

[Rei Ichido]

Bon dans cet exemple ça passe encore mais vous voyez la problématique. Quand les requêtes commencent à être trop lourdes n'est il pas préférable de stocker l'info directement dans un champs spécifique?

Dans mon exemple, je pourrai créer un champs volume d'affaire dans la table des commerciaux et à chaque fois qu'une nouvelle transaction concernant une société associée à ce commercial est créée
j'ajoute le montant de la transaction à son volume d'affaire.
Qu'en pensez vous?

La dénormalisation est un éternel débat ... même si eon arrive à de meilleurs résultats en normalisant _bien_ , quand on part d'un système plus ou moins pourri (et dieu sait si c'est fréquent quand on arrive sur des projets déjà bien en place) pour lequel on ne peut pas repartir de zéro, il est régulièrement avantageux de mettre en place des éléments de dénormalisation (le terme étant un peu impropre parce qu'on part dans ces cas là de quelque chose de pas du tout normalisé).

Il y a aussi le cas particulier des bases dites décisionnelles, avec aucune écriture sauf le rafraîchissement, et dans ce cas précis on ne se gêne pas pour pré-calculer toute sorte de valeurs.

[SQLpro]

La dénormalisation est un éternel débat ... même si eon arrive à de meilleurs résultats en normalisant _bien_ , quand on part d'un système plus ou moins pourri (et dieu sait si c'est fréquent quand on arrive sur des projets déjà bien en place) pour lequel on ne peut pas repartir de zéro, il est régulièrement avantageux de mettre en place des éléments de dénormalisation (le terme étant un peu impropre parce qu'on part dans ces cas là de quelque chose de pas du tout normalisé).

Non, ça c''est TOUJOURS stupide. Il faut tout simplement refactoriser. Je vous renvoie aux ouvrages suivants :
http://www.amazon.com/Refactoring-Da.../dp/0321774515
http://www.amazon.com/Refactoring-SQ...ne+faroult+sql
Par exemple, dans SQL Server, en utilisant des index avec clause INCLUDE, vous pouvez facilement factoriser "à l'envers"...

Il y a aussi le cas particulier des bases dites décisionnelles, avec aucune écriture sauf le rafraîchissement, et dans ce cas précis on ne se gêne pas pour pré-calculer toute sorte de valeurs.

SQL est le langage des bases de données relationnelles donc OLTP (One Line Transaction Processing) et vous êtes dans le forum consacré à SQL... Rien à voir avec le decisionnel (OLAP : On Line Analytical Process) pour lesquels les moteurs de stockage et le langage de requêtes (généralement MDX) n'ont rien à voir (même si certains continue d'utiliser du SQL et un SGBDR pour faire du décisionnel de manière parfaitement stupide !)

A +

[Waldar]

Sauf que le MDX ça ne prend pas, ça reste au sein de Microsoft.

Le SQL dans le décisionnel enterrera le MDX pour sûr.
Je pense même que les bases 100% OLAP avec des cubes vont disparaître.