Colonne calculée : dans la table ou dans une vue ?

Version imprimable

17/01/2013, 12h40
Kropernic

Colonne calculée : dans la table ou dans une vue ?

Bonjour,

Je viens chercher conseil pour savoir si je fais les choses bien ou pas...

J'ai donc une table nommée T_DOSSIER_DOS qui contient donc une liste de dossier (si si, j'vous jure :mouarf:).

Afin de ne pas devoir à chaque fois déterminé le status d'un dossier "manuellement", j'ai donc créé une fonction chargée de faire cela.

J'aimerais maintenant utiliser cette fonction dans une colonne calculée histoire d'avoir le status du dossier directement en faisant un ordre de type SELECT de manière à pouvoir faire des filtres dessus.

Ce status étant déterminé par "calcul", je pense qu'on entre donc ici dans le cadre de la dénormalisation (mais il n'y avait pas de préfixe pour ça et je me demande du coup si je suis dans le bon forum ^^).

Quel est le meilleur endroit pour créer cette colonne ? Directement dans la table ou bien est-il préférable de créer une vue qui intègrera cette dernière ?
17/01/2013, 14h00
Richard_35

Bonjour Kropernic,

Citation:

Envoyé par Kropernic

.../... Directement dans la table .../...

==> de mon point de vue : surtout pas !... c'est une information dynamique, laissons-la dynamique !

Citation:

Envoyé par Kropernic

créer une vue qui intègrera cette dernière

==> oui, si le SQL de ton SGBD permets d'intégrer une fonction dans le SELECT.
17/01/2013, 14h14
Kropernic

C'est vrai que j'ai oublié de précisé que je suis sous SQL SERVER 2008 R2.

Rah zut, j'avais espéré pouvoir me passer de la vue... Ca va me faire une chiée de procédures stockées à modifier :(

C'est pourtant pratique les vues !

Exemple fictif :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE VIEW v_dossier_complet AS
SELECT d.dos_id, d.dos_titre, d.dos_date_creation,
	c.clt_id AS id_client,
	c.clt_nom AS nom_client,
	cd.cde_total_ht AS montant_commande_hors_taxes
	CASE
		WHEN telle_condition THEN 'Initialisé'
		WHEN telle_autre_condition THEN 'En cours'
		WHEN telle_troisième_condition THEN 'Terminé'
		WHEN encore_une_autre_condition THEN 'Facturé'
		WHEN et_une_derniere_condition THEN 'Payé'
		ELSE 'À traiter'
	END AS statut
FROM te_dossier_dos d
INNER JOIN te_client_clt c ON c.clt_id = d.dos_id_client
LEFT OUTER JOIN te_commande_cde cd ON cd.cde_id_dossier = d.dos_id
	LEFT OUTER JOIN te_facture_fct f ON f.fct_id_commande = cd.cde_id;

Si je veux tous les dossiers terminés, dans l'application, je n'ai besoin que de cette requête :

Code:

1
2
3
SELECT d.dos_id
FROM v_dossier_complet
WHERE statut = 'Terminé'

Si je veux le total de commande par dossier pour le client 12 :

Code:

1
2
3
4
5
SELECT dos_id, SUM(montant_commande_hors_taxes) AS total_commandes
FROM v_dossier_complet
WHERE c.clt_id = 12
	AND statut = 'Facturé'
GROUP BY dos_id

17/01/2013, 17h32
Kropernic

Bah tu peux faire exactement la même chose avec une colonne calculée dans la table non ? ^^
17/01/2013, 18h00
Richard_35

Citation:

Envoyé par Kropernic

Bah tu peux faire exactement la même chose avec une colonne calculée dans la table non ?

==> les deux termes colorés sont contradictoires : si une colonne est "dans la table", alors ce n'est pas une "colonne calculée" (même si elle a été "calculée" avant d'être "dans la table"). A partir du moment où elle est stockée "dans la table" (figée donc), elle ne peut plus être nommée "colonne calculée".
18/01/2013, 10h17
Kropernic

Je comprends le raisonnement mais cela n'est-il pas un peu extrémiste ?
18/01/2013, 10h39
CinePhil

Il y a au moins deux façons de considérer cette histoire de statut d'un dossier.

1) Le statut est une propriété du dossier.
Le statut prend une valeur parmi une liste à la survenance d'un événement.
Par exemple, le dossier est d'abord créé, puis attribué à un responsable, puis en phase d'étude, puis traité, puis soldé.
Le statut est alors déterminé par l'application et la table des dossier contient simplement une clé étrangère référençant l'identifiant de la table des statuts.

2) Le statut est une propriété variable, calculable à tout moment selon la valeur d'autres données de la BDD.
C'est alors une propriété calculable par une requête qu'il convient de ne pas enregistrer en BDD.

Si tu nous décris plus en détail cette histoire de statut et ce qui provoque son changement de valeur, nous pourrons mieux te guider sur la solution conceptuelle qu'il conviendrait de mettre en oeuvre.
18/01/2013, 10h49
Kropernic

Bin c'est un mélange des deux :aie:.

Au départ, c'est déterminé par l'application (status "en cours" et "finalisé") et ensuite, c'est via un calcul.

Un dossier contient des gifts (encore eux^^) et suivant qu'ils sont tous ou en partie activés ou, tous ou en partie désactivé, le status change.

Je pense que la "vraie" bonne solution est d'utilisé la vue (ce que j'ai fait). Mais j'aimerais quand même comprendre en quoi est-ce si "catastrophique" d'utilisé une colonne calculée dans la table ?

D'après Richard, une colonne calculée dans une table n'a pas de sens. Cela reste cependant possible et il est même possible de faire persisté la donnée pour éviter qu'elle soit recalculée à chaque fois pour les cas où cela ne varie pas (et j'imagine que ça peut être indexé du coup).

Je crois que je viens d'avoir un déclic. Il y a les colonnes calculées dont la valeur ne changera jamais (genre une colonne pour avoir l'année à partir de la date de création de la ligne (ça sert à rien mais c'est pour l'exemple)) et les colonnes calculées où la valeur change au fil du temps (ce qui est mon cas).

Suis-je dans le bon ?
18/01/2013, 11h49
CinePhil

Dans ton cas, je ferais une fonction SQL qui calcule le statut en vérifiant si tous les gifts sont activés ou non et je stockerais effectivement le statut dans une colonne de la table. Cette fonction serait appelée notamment par les triggers lors de l'action sur les gifts. Ou alors dans une procédure stockée si tu fais du traitement de masse des gifts.

Je fais un parallèle avec la gestion de stock. Conceptuellement, la quantité d'un produit P en stock est calculable par la différence entre les quantités achetées et les quantités vendues mais dans la pratique :
- au bout d'un certain temps, après des milliers d'achats et de ventes, ce calcul va finir par devenir coûteux en temps, surtout s'il y a des milliers de produits différents en stock ;
- il peut y avoir de la casse, de la perte, du vol... qui entraînent la réalisation d'un inventaire annuel pour justement mettre à jour manuellement ces quantités en stock.
18/01/2013, 11h53
Kropernic

Citation:

Envoyé par CinePhil

Dans ton cas, je ferais une fonction SQL qui calcule le statut en vérifiant si tous les gifts sont activés ou non et je stockerais effectivement le statut dans une colonne de la table. Cette fonction serait appelée notamment par les triggers lors de l'action sur les gifts. Ou alors dans une procédure stockée si tu fais du traitement de masse des gifts.

Ah bin c'est ce que j'avais fait au départ XD. Mais pas besoin d'appeler la fonction à chaque fois. C'est la fonction qui définit la colonne en fait. Si, dans SMSS, on ouvre la table en mode design, la colonne calculée n'a même pas de type de donnée. Il est définit par la fonction.

Je vais garder la vue pour le moment (pas envie de refaire les modifs en sens inverse) mais je ne crois pas que cela posera de problèmes de performances avec des tables correctement indexées non?
18/01/2013, 13h16
Richard_35

Bonjour Kropernic et Philippe,

Citation:

Envoyé par Kropernic

Je comprends le raisonnement mais cela n'est-il pas un peu extrémiste ?

==> un peu, si !... mais nous sommes dans l'optique : "il y a la loi et l'esprit de la loi".

L'exemple de Philippe est significatif : la quantité en stock d'un article peut être retrouvée par l'analyse de tous les mouvements d'entrée et de sortie... mais, quand il y a beaucoup de mouvements, cela devient impossible, d'où le stockage légitime de la quantité en stock.

Citation:

Envoyé par CinePhil

une fonction SQL qui calcule le statut en vérifiant si tous les gifts sont activés ou non et je stockerais effectivement le statut dans une colonne de la table

==> cela tient la route. Attention, néanmoins, à prévoir le cas d'une dévalidation d'un gift qui modifierait le statut. S'il n'y a pas beaucoup de gift, il vaut mieux ne pas stocker ce statut et le conserver dans une vue (dynamique).

Par expérience, au bout d'un moment, tu auras des dossiers "finalisés" avec des gifts "non activés" et inversement. Tu devras, alors faire des recherches liées à cette incohérence.

Pour reprendre l'exemple de CiniPhil, les inventaires ont été créés, en partie, pour gérer ces incohérences : la quantité en stock ne correspond pas aux mouvements d'entrée/sortie enregistrés... alors que, sur le papier, c'est impossible...
18/01/2013, 21h52
pachot

Bonjour,

Si le 'status' est calculé à partir des autres colonnes de la même ligne, SQL Server a des computed columns. Ce qui est intéressant, contrairement à une vue, c'est qu'on peut l'indexer. Et ce serait étonnant de ne pas avoir envie de mettre le status dans un index.

Si le calcul se fait à partir d'autres lignes, de la même table ou d'autre table, c'est intéressant de l'avoir ailleurs dans une Indexed View.

Ce sont toutes les deux des fonctionnalités qui permettent de stocker une valeur calculée, pour des raisons de performance, tout en confiant sa mise à jour au SGBD qui tient compte de l'intégrité, de l'isolation des transactions, de la performance, etc. Exactement comme un index.

Cordialement,
Franck.