Discussion :

[tavarlindar]

Bonjour à tous,

Avant de mettre en place la base de données, je vous sollicite pour valider le MCD et MLD associé pour répondre à la problématique suivante :

L’objectif est de gérer des opérations bancaires d'une façon similaire à celle implémentée dans Microsoft Money pour ceux qui connaissent.
Une fois construit le moteur « base de données », suivra la phase de développement de l’interphase utilisateur qui permettra de saisir les données. Sachant que je connais en gros à quoi ressemblera cette interface utilisateur, j’ai mis en exemple à quoi le système ressemblera.
Je ne me trompe pas de forum, je souhaite savoir si mon MCD et donc MLD tient la route pour le résultat que je souhaite obtenir.
Je précise aussi que j’ai une contrainte à respecter. Je dois pouvoir gérer une notion de TVA. Raisonnant dans un cadre international très complexe, j’ai une multitude de taux. En raison de cette complexité, il a été décidé de ne pas gérer la TVA comme étant le résultat d’un montant HT multiplié par un Taux donné, mais de rentrer dans le système des montants globaux avec possibilité de préciser le montant HT et la partie TVA. Voir la suite pour mieux comprendre.

J’ai défini 4 entités principales :
Opération : qui se caractérise entre autre par :
- une date
- un libellé
- un montant global (TTC)
- un tiers (nom de la personne / société à qui on a payer la somme ou de qui on a reçu l’argent)

Détail opération :
- un montant global (TTC)
- un descriptif optionnel

Catégorie financière
- Un nom
Sous Catégorie financière
- Un nom

Une opération peut regrouper plusieurs « detail_operation » comme elle peut ne pas en regrouper.
Une opération bancaire appartient obligatoirement à un catégorie financière (ex : Santé, Impôt, Loisir, etc.)
Chaque catégorie financière peut se subdiviser en plusieurs sous catégories :
Prenons par exemple : 2 catégories financières que nous nommons :
1) Loisirs/culte/Sport
2) Santé

La première peut se décomposer en Livres, Jardinage et DVD donc 3 sous catégories
La seconde (Santé) contient les sous catégories : Analyses, Pharmacie non remboursée, dentiste, etc.

MCD


MLD


Exemple 1 :
Exemple j’achète pour 100 euros à la pharmacie. J’affecte ces 100 à la catégorie Santé et je précise que cela concerne de la pharmacie non remboursée.




Ici, il n’y a pas de notion de ventilation. Mon opération est affectée à une sous catégorie.

Exemple 2 Cas d’une ventilation

J’achète dans un magasin pour 100 euros qui se décompose en :
50 euros (catégorie Santé – sous catégorie : pharmacie non remboursée)
20 euros (catégorie Loisirs - sous catégorie : Livres)
30 euros (catégorie Loisirs - sous catégorie : Jardinage)





Pour l’heure, je suis parti pour définir une catégorie « spéciale » : la catégorie « Ventilation ». Ainsi, si on saisit une opération, et qu’on choit la catégorie financière « Ventilation », l’interface utilisateur proposera alors de saisir des lignes de « détail_operation » avec la possibilité de préciser pour chaque ligne sa catégorie financière voir sa sous catégorie.

Concernant la TVA, je suis parti pour définir la TVA comme étant une Catégorie financière.

Ainsi si je dois rentrer dans le système que j’ai acheté 200 euros de pharmacie (analyses) dont 40 de tva, il suffira de rentrer dans le système une opération dont le montant global est de 200 qui appartient à la catégorie financière « ventilation » se rattachant à 2 lignes « détail_operation » :
1 pour 160 classé en Santé : Pharmacie -> catégorie Santé / sous catégorie analyse
2 pour 40 classé en TVA

Voyez-vous une faille dans tout cela ? D’après votre expérience, qu’en pensez vous ?

Par avance merci pour votre aide. Si j'ai posté, c'est que je ne suis pas sûre de mon MCD. Je ne souhaite pas découvrir en prod un truc non prévu ....

Bien à vous

Tavar

[f-leb]

bonsoir,

comme personne n'est passé par là, je vais essayer de t'aider.

Je propose un brouillon de MCD+MLD un peu différent:


avec une spécialisation de l'entité Operation en sous-type Ope_SansVentil et Ope_AvecVentil avec une contrainte de partition selon la règle de gestion: une Opération est soit une opération sans ventilation, soit une opération avec ventilation.

Ensuite, j'ai considéré qu'une sous-catégorie était une catégorie fille associée à une catégorie parente directe. D'où l'association réflexive (Rel_1) sur l'entité Catégorie:

Categorie:

Code :

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

1
2
3
4
idCategorie   NomCategorie   idCategorieParent
      1        Loisirs
      2        Livre                1
      3        Jardinage            1

Livre et Jardinage sont des sous-catégories qui ont pour catégorie parente la catégorie 1 (Loisirs).

voilà mes remarques pour l'instant.

[tavarlindar]

Bonjour,
Tout d’abord, merci f-leb d’avoir pris de votre temps pour m’aider.

J’ai lu attentivement et essayer de comprendre votre proposition.

Je dois reconnaitre que dès qu’on me parle de spécialisation, je suis réfractaire. Je maitrise peu cette notion pour tout dire. De plus, cette solution qui consiste à mettre en place 2 voir 3 tables opération remet en cause l’architecture de la base actuellement en production.

De plus je ne comprends pas la relation Rel_3. Si Ope_AvevVentil existe alors pourquoi ne pas mettre :

Ope_AvevVentil ….[1,n]………….(rel_3)…………..[0,n]…DétailVentil

Pour ce qui concerne les catégories et sous catégories, pourquoi pas, mais je comprends pas l’avantage. On simplifie l’architecture, on n’a plus qu’une table, mais avec des valeurs null pour les catégories mères.

Globalement, je ne comprends pas pourquoi ma solution est mauvaise.

Dans l’attente d’un éclairage,

Bien à Vous
Tavar

[JPhi33]

Bonjour Tavar,

Il y a plusieurs erreurs dans la modélisation.

1) Cardinalité 1,1 de la patte Opération---Appartenir_2.
Dans le cas de l'exemple 2, l'opération est ventilée en 3 détails sur 2 catégories. Il est donc impossible de rattacher l'opération à une seule catégorie.
2 solutions :
a) Remplacer 1,1 par 0,1
b) Supprimer Appartenir_2 mais dans ce cas, il faut que chaque opération ait au moins 1 détail.

2) Sous-catégories
Dans l'exemple 1, l'opération est affectée à la catégorie Santé, sous-catégorie Pharmacie non remboursée. Comment est-ce possible ? Il n'y a aucun lien entre Opération et Sous-catégorie.


Bon, il faut remettre de l'ordre dans tout ça.

Voici les règles de gestion telles que je les ai comprises (à valider) :
- Une opération a obligatoirement une catégorie financière (sauf en cas de ventilation)
- Elle peut appartenir à une sous-catégorie de celle-ci
- Une opération peut être ventilée
- Dans ce cas, chaque ligne de ventilation fait l'objet d'un détail d'opération
- Chaque détail appartient à une catégorie financière et éventuellement à une sous-catégorie de celle-ci

D'après ces règles, je propose le modèle ci-dessous (cliquer pour agrandir).

[tavarlindar]

Bonjour,

Un grand merci JPhi33 pour ton analyse.
Ta solution me semble pal mal du tout, même si certains points m’ont dérouté à la première lecture.

En effet, pour moi, une opération appartient forcément à une catégorie financière. Dans le cas d’une ventilation, cette catégorie financière s’appelait « ventilation ». C’était une catégorie spéciale, une catégorie financière qui n’en était pas une, mais qui permettait d’indiquer la présence ou non de ligne de détail d’opération.

Concernant les règles à valider, je suis d’accord.

Ce qui me surprend c’est les 2 relations qui lient l’entité opération avec categ_financière et sous_categ_financière : 2 pattes « 0,1 »

En construisant le MLD, on s’aperçoit que la table opération peut contenir des valeur null pour les clés étrangère id_sous_taeg_fi et id_categ_fi dans le cas d’une ventilation.
Dans le cas d’une ventilation où une ligne « détail opération » ne se rattache pas à une sous catégorie, on peut avoir une valeur null dans la table detail_operation .


Est-ce acceptable ? Je dois faire des tests pour savoir si avec cette architecture je retrouve mes petits.

[fsmrel]

Bonsoir,

Je dois reconnaitre que dès qu’on me parle de spécialisation, je suis réfractaire. Je maitrise peu cette notion pour tout dire. De plus, cette solution qui consiste à mettre en place 2 voir 3 tables opération remet en cause l’architecture de la base actuellement en production.

f-leb a la bonne approche. Vous devriez vous familiariser avec les concepts de spécialisation / généralisation (ce ne sont pas les exemples qui manquent sur ce forum). Ces concepts font partie des piliers de la modélisation.
Quant à l’architecture de la base en production, considérons la comme une V1, mais qui ne doit pas interférer avec le diagnostic que l’on fait de votre MCD et les aménagements proposés.

Avant de consulter les différentes observations qui vous ont été faites, je suis parti moi aussi sur une spécialisation des ventilations...

Ce qui me surprend c’est les 2 relations qui lient l’entité opération avec categ_financière et sous_categ_financière : 2 pattes « 0,1 »

Je comprends votre surprise, car au niveau tabulaire, cela se traduira par la présence du bonhomme NULL, alors que le défi est justement de lui interdire de se manifester. C’est là qu’intervient la spécialisation : un détail opération (entité-type DetailOper dans le MCD ci-dessous) est soit un détail à catégorie (entité-type DetailACat), soit un détail à sous-catégorie (entité-type DetailAScat).


MCD (Power AMC V11, ébauche à compléter)


A noter que je considère que chaque sous-catégorie est une propriété multivaluée d’une catégorie, donc j’utilise l’identification relative (association-type Contenir), exprimant ainsi une relation de composition.

Même principe concernant les opérations et le détail de celles-ci.

La croix soulignée dans la demi-lune symbolise une contrainte d’exclusion entre les entités-types spécialisées DetailACat et DetailAScat.

Je n’ai pas mis en relation les entités-types Operation et Categorie (et SousCategorie). En effet, ces relations sont redondantes car elles peuvent être inférées à partir de DetailOper (en fait de ses spécialisations).


MLD



A propos de la TVA :

Concernant la TVA, je suis parti pour définir la TVA comme étant une Catégorie financière.

Il y a là un mélange sémantique : les clients sont intéressés par des produits de santé, de loisir, mais certainement pas des impôts. Il faut faire apparaître le concept de TVA dans le MCD, en relation avec les prix des produits (que ce soit sous forme d’entité-type ou d’attribut).

Est-ce acceptable ?

Vous précisez que la table detail_operation peut comporter des NULL, or le défi est d’éviter cela.

Par ailleurs, cette table viole la 3NF (troisième forme normale), car il existe une dépendance fonctionnelle mettant en jeu des attributs ne participant pas à la clé de la table :

{id_sous_categ_fi} → {id_categ_fi}

Il en va de même pour la table operation.

[JPhi33]

Bonjour à tous,

Je reviens un instant sur la solution de fsmrel qui est tout à fait pertinente (et qui, il faut bien le dire, m'a échappée).

La spécialisation repose sur le principe qu'on distingue deux groupes disjoints de détails d'opérations, ceux associés à une catégorie, et ceux associés à une sous-catégorie.
Je suis d'accord également avec les identifications relatives Détail opération / Opération et Sous-catégorie / Catégorie

Je n'ai qu'une petite imprécision à relever : la cardinalité mini 0 de la patte Opération---Composer devrait être 1. En effet, dans le cas où elle est 0, l'opération n'a pas de détail et ne peut donc être affectée ni à une catégorie, ni à une sous-catégorie.

Toutefois, cette solution diverge par rapport à l'énoncé et au MCD de tavarlindar, dans lequel seules les opérations ventilées ont un(des) détail(s), par le fait que toute opération a au moins un détail. A lui d'accepter ou non cet écart.

[fsmrel]

Bonsoir,

la cardinalité mini 0 de la patte Opération---Composer devrait être 1

Absolument. Et cette contrainte est à mettre en œuvre au niveau logique. Cela est simple à réaliser dans le cadre du Modèle Relationnel de Données, qui permet dans le même paquet de prévoir une instruction de création d’une opération et d’une instruction de création d’un détail avant que les contrôles ne soient effectués, mais ça l’est beaucoup moins en SQL : si le SGBDR ne fournit pas l’instruction CREATE ASSERTION (qui permet de différer les contrôles), alors la contrainte est à programmer applicativement.

Dans le même sens, s’il est facile de garantir la contrainte d’exclusion entre DetailACat et DetailAScat, concernant la contrainte de totalité, dans le cas de SQL on est ramené au problème précédent.

seules les opérations ventilées ont un(des) détail(s)

Il s’agit effectivement de compléter l’ébauche de MCD. On pourrait renommer l’entité-type Operation en OperVentilee et en faire une spécialisation d’une nouvelle entité-type Operation, spécialisée par ailleurs en OperNonVentilee, cette dernière étant directement en relation avec l’entité-type Categorie (dans la mesure où les sous-catégories ne la concernent pas). Quelque part, on rejoint pour partie le MCD proposé par f-leb.


MCD (vue partielle)




Votre avis ?

[f-leb]

bonjour à tous,

finalement si on recolle les morceaux à partir du dernier MCD de fsmrel:

- Operation est spécialisée en OpeNonVentilee et OpeVentilee
- DetailOper est spécialisée en DetailACat et DetailASCat

Exemple 1 :
Exemple j’achète pour 100 euros à la pharmacie. J’affecte ces 100 à la catégorie Santé et je précise que cela concerne de la pharmacie non remboursée...

...Ici, il n’y a pas de notion de ventilation. Mon opération est affectée à une sous catégorie.

Si j'ai bien compris (à confirmer tout de même), il faudrait donc en plus spécialiser OpeNonVentilee en OpeNonVentileeACat et OpeVentileeASCat.

[EDIT] quoique OpeNonVentileeACat, OpeVentileeASCat ... au niveau sémantique c'est pas très jojo, hein? [/EDIT]


Je me demandais pourquoi finalement vous ne me rejoindriez pas non plus sur le point qui suit (on ne sait jamais).

Ensuite, j'ai considéré qu'une sous-catégorie était une catégorie fille associée à une catégorie parente directe. D'où l'association réflexive (Rel_1) sur l'entité Catégorie:

Categorie:

Code :

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

1
2
3
4
idCategorie   NomCategorie   idCategorieParent
      1        Loisirs
      2        Livre                1
      3        Jardinage            1

Livre et Jardinage sont des sous-catégories qui ont pour catégorie parente la catégorie 1 (Loisirs).

Étant donnés:
- qu'une opération ventilée où non concerne indifféremment une catégorie où une sous-catégorie (dans les règles de gestion, cette partie là ne me parait pas très claire et demande à être confirmée);

-que catégorie et s/catégorie ne sont caractérisés que par les seuls attributs "NomCategorie" et "NomSCategorie";

je suis tenté de croire que Categorie et S/Categorie désigne le même objet de gestion (que j'ai finalement appelé Categorie) en association hiérarchique.

En espérant avoir été clair, il y aurait-il une faille dans ce raisonnement ?

[fsmrel]

Bonsoir,

Si j'ai bien compris (à confirmer tout de même), il faudrait donc en plus spécialiser OpeNonVentilee en OpeNonVentileeACat et OpeVentileeASCat.

Avec le MCD que je propose, je souhaiterais que tavarlindar et al. comprennent l’intérêt de la généralisation / spécialisation. Disons que le but est pédagogique, en mettant complètement à plat la représentation des concepts.
Dans cette optique, la spécialisation de OpeNonVentilee en OpeNonVentileeACat et OpeVentileeASCat est tout à fait légitime.

Je me demandais pourquoi finalement vous ne me rejoindriez pas non plus sur le point qui suit (on ne sait jamais).

Votre solution est celle que l’on utilise pour traiter des nomenclatures et elle est tout à fait valable. On est ici dans le cas le plus simple, c'est-à-dire celui où le nombre de niveaux est égal à 2 (mais qui peut le plus peut le moins). Le système est replié puisqu’on ne voit plus sur le MCD les entités-types Categorie et SousCategorie, mais c’est aussi le plus ouvert. En effet, supposons qu’un jour l’entreprise ait besoin que le nombre de niveaux passe à 3 (ou plus) : c’est certainement votre modèle « récursif » qui en pâtirait le moins. Si pour ma part je devais mettre en œuvre le projet, c’est vraisemblablement une solution comme la vôtre que je retiendrais. Le MCD que j’ai proposé, je le répète, a plus un objet didactique qu’autre chose.

Pour reprendre votre MCD : la patte reliant DetailsVentil et Rel_4 devrait normalement être porteuse d’une cardinalité minimale 1 (un détail détermine soit une catégorie, soit une sous-catégorie). De même, comme l’a fait observer JPhi33, une opération avec ventilation est composée d’au moins un détail (association-type Rel_3).
Quant à l’association-type Rel_1, il faudra veiller, lors de la production du MLD par l’AGL à ce qu’elle fasse l’objet d’une table pour interdire la présence du bonhomme NULL (en faire une entité-type par exemple). En ce sens, pour cette partie, le schéma tabulaire devrait être le suivant :

[JPhi33]

Bonjour,

On pourrait renommer l’entité-type Operation en OperVentilee et en faire une spécialisation d’une nouvelle entité-type Operation, spécialisée par ailleurs en OperNonVentilee, cette dernière étant directement en relation avec l’entité-type Categorie (dans la mesure où les sous-catégories ne la concernent pas).

On s'éloigne des règles de gestion initiales car une opération non ventilée peut être associée à une sous-catégorie. C'est d'ailleurs le cas dans l'exemple 1 de tavarlindar : les 100€ (non ventilés) sont affectés à la sous-catégorie Pharmacie non remboursée de la catégorie Santé.

Ici, il n’y a pas de notion de ventilation. Mon opération est affectée à une sous catégorie.

il faudrait donc en plus spécialiser OpeNonVentilee en OpeNonVentileeACat et OpeVentileeASCat

Comme le fait remarquer f-leb, il va donc falloir spécialiser OperNonVentilee. De plus, il ne faut pas oublier d'introduire dans cette entité les propriétés absentes de l'entité Operation : le montant TTC, et peut-être, finalement, toutes les propriétés qui figurent dans DetailOper.

Avec ces nouvelles spécialisation, on arrive à un degré de complexité qui risque d'être disproportionné par rapport au besoin initial.


Mon avis est que le 1er MCD de fsmrel est le bon compromis entre complexité et exactitude.
Dans le cas d'une opération ventilée, on considère que l'entité Operation représente l'en-tête de l'opération financière et que l'entité DetailOper contient la liste des lignes de détail. Dans le cas d'une opération non ventilée, on peut faire exactement la même considération si ce n'est qu'on se limite à une seule ligne de détail. Il s'agit d'un à-peu-près sémantique que, personnellement, j'estime acceptable.

La conséquence est, comme je l'ai déjà dit, de passer de 0 à 1 la cardinalité mini de la patte Operation---Composer. Il faut aussi typer l'opération (ventilée / non ventilée) au moyen d'une entité (TypeOperation) ou d'un booléen (ventilée oui/non) dans Operation. Mais le calcul du montant de l'opération sera toujours la somme des montants des détails, que l'opération soit ventilée (n détails) ou non (1 détail).

[fsmrel]

Bonjour,

Avec ces nouvelles spécialisation, on arrive à un degré de complexité qui risque d'être disproportionné par rapport au besoin initial.

C’est pourquoi, comme je l’ai suggéré dans mon précédent message, il faudrait s’intéresser de plus près au MCD proposé par f-leb dans son 1er message (tout en tenant compte des observations que j’ai faites).

[tavarlindar]

Bonjour à tous,
J’ai lu attentivement vos propositions. Si je ne suis pas aussi réfractaire à la spécialisation, je ne comprends toujours pas l’intérêt de la chose.

J’ai fait des tests à partir du MCD ci-dessous. C’est le MCD de Jphi33 avec une modification importante. Je considère qu’une opération appartient obligatoirement à une catégorie financière. Dans le cas d’une opération qui fait l’objet de plusieurs lignes de détail opération, l’opération se rattache à une catégorie financière spéciale « Ventillation ».

Ce qui donne le MLD suivant :

Voici les tests que j’ai faits. J’ai construit une mini base avec les tables en question. Elles sont précédé de la lettre « t » uniquement pour signifier qu’il s’agit de table « test ».
J’utilise MySQL.

A partir delà, quelle sont les questions aux quelle devra répondre la base de données ?
1) Avoir la liste des opérations.
2) Connaitre la somme des opérations (montant global)
3) Connaitre pour une opération ventilée donnée, les lignes de détail de l’opération
4) Connaitre la somme des lignes de détail

J’ai repris les 2 exemples du départ et j’ai rentré les données dans la base. Ici, s’agissant de débit (dépenses), j’ai saisi les montant en négatif.

Table Catégorie Financière

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
-- 
-- Structure de la table `t_categorie_fi`
-- 

CREATE TABLE `t_categorie_fi` (
  `id_categorie_fi` int(11) NOT NULL auto_increment,
  `nom_categorie_fi` varchar(50) NOT NULL,
  PRIMARY KEY  (`id_categorie_fi`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

-- 
-- Contenu de la table `t_categorie_fi`
-- 

INSERT INTO `t_categorie_fi` (`id_categorie_fi`, `nom_categorie_fi`) VALUES (1, 'Ventillation'),
(2, 'Loisir'),
(3, 'Santé'),
(4, 'Communication');

Table Sous catégorie financière

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
-- 
-- Structure de la table `t_sous_categorie_fi`
-- 

CREATE TABLE `t_sous_categorie_fi` (
  `id_sous_categorie` int(11) NOT NULL auto_increment,
  `nom_sous_categorie_fi` varchar(50) NOT NULL,
  `categorie_fi_id` tinyint(11) NOT NULL,
  PRIMARY KEY  (`id_sous_categorie`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

-- 
-- Contenu de la table `t_sous_categorie_fi`
-- 

INSERT INTO `t_sous_categorie_fi` (`id_sous_categorie`, `nom_sous_categorie_fi`, `categorie_fi_id`) VALUES (1, 'analyse', 3),
(2, 'Pharmacie non remboursée', 3),
(3, 'Livres', 2),
(4, 'Jardinage', 2);

Table opération

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
-- 
-- Structure de la table `t_operation`
-- 

CREATE TABLE `t_operation` (
  `id_operation` int(11) NOT NULL auto_increment,
  `date_valeur_op` date NOT NULL,
  `lib_op` varchar(50) NOT NULL,
  `sous_categorie_fi_id` int(11) NOT NULL,
  `categorie_fi_id` int(11) NOT NULL,
  `montant_global` decimal(10,2) NOT NULL,
  PRIMARY KEY  (`id_operation`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

-- 
-- Contenu de la table `t_operation`
-- 

INSERT INTO `t_operation` (`id_operation`, `date_valeur_op`, `lib_op`, `sous_categorie_fi_id`, `categorie_fi_id`, `montant_global`) VALUES (1, '2009-11-26', 'Pharmacie', 2, 3, -100.00),
(2, '2009-11-03', 'Auchan', 0, 1, -100.00);

Table détail opération

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
-- 
-- Structure de la table `t_detail_operation`
-- 

CREATE TABLE `t_detail_operation` (
  `id_detail_op` int(11) NOT NULL auto_increment,
  `montant_detail_op` decimal(10,2) NOT NULL,
  `note_detail_op` varchar(10) NOT NULL,
  `operation_id` int(11) NOT NULL,
  `sous_categorie_fi_id` int(11) NOT NULL,
  `categorie_fi_id` int(11) NOT NULL,
  PRIMARY KEY  (`id_detail_op`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

-- 
-- Contenu de la table `t_detail_operation`
-- 

INSERT INTO `t_detail_operation` (`id_detail_op`, `montant_detail_op`, `note_detail_op`, `operation_id`, `sous_categorie_fi_id`, `categorie_fi_id`) VALUES (1, -50.00, '', 2, 2, 3),
(2, -20.00, '', 2, 3, 2),
(3, -30.00, '', 2, 4, 2);

Question 1 - Liste des opérations

Code :

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

1
2
3
4
5
6
SELECT date_valeur_op, lib_op, nom_categorie_fi,  nom_sous_categorie_fi , montant_global FROM t_operation  o 
LEFT JOIN `t_detail_operation` dop ON o.id_operation = dop.operation_id 
LEFT JOIN t_categorie_fi cf ON o.categorie_fi_id=cf.id_categorie_fi 
LEFT JOIN t_sous_categorie_fi sscf  ON o.sous_categorie_fi_id =  sscf.id_sous_categorie
Group BY id_operation

Question 2 – somme des opérations

Code :

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

1
2
SELECT sum(`montant_global`) FROM `t_operation`

Question 3 – Liste du détail pour une opération donnée
SELECT nom_categorie_fi, nom_sous_categorie_fi ,note_detail_op, montant_detail_op
FROM t_detail_operation dop LEFT JOIN t_categorie_fi cf ON dop.categorie_fi_id=cf.id_categorie_fi
LEFT JOIN t_sous_categorie_fi sscf ON dop.sous_categorie_fi_id = sscf.id_sous_categorie
WHERE operation_id='2'


Question 4 : la somme du détail d’une opération

Code :

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

1
2
3
4
5
SELECT sum( montant_detail_op )
FROM t_detail_operation dop LEFT JOIN t_categorie_fi cf ON dop.categorie_fi_id=cf.id_categorie_fi 
LEFT JOIN t_sous_categorie_fi sscf  ON dop.sous_categorie_fi_id =  sscf.id_sous_categorie
WHERE operation_id='2'

A partir de là, à priori le MCD répond au besoin. Je ne vois pas ce que va apporter la spécialisation de plus.

[fsmrel]

Bonsoir,

Si je ne suis pas aussi réfractaire à la spécialisation, je ne comprends toujours pas l’intérêt de la chose.

Considérons la partie suivante du MCD de f-leb (dans sa version Power AMC, je n’ai pas Win’Design) :


D’une manière générale, la spécialisation permet d’appeler un chat un chat et de ne pas laisse les choses dans l’ombre. Par exemple, le principe de ventilation des opérations n’est plus noyé dans la masse. Quelques observations :

1) Avec votre MCD, puisque toutes les opérations ne sont pas ventilées, la cardinalité entre Operation et Detail_Operation est nécessairement 0,N. Dans le cas de la spécialisation, la cardinalité entre les entités-types OperAvecVentil et DetailOper est 1,N. Il y a un gain sémantique important (JPhi33 l'avait déjà fait observer) : c’est comme passer de la quantification existentielle à la quantification universelle.

2) Chaque chose est à sa place :

a) Dans le cas d’une opération sans ventilation, le montant de cette opération fait l’objet d’un attribut de l’entité-type OperSansVentil (en l’occurrence OperMontant). Pour mémoire, votre MCD ne fait pas mention d’un attribut de cette nature (alors que vous l’avez rajouté au niveau SQL, avec l’instruction CREATE TABLE Operation).

b) Dans le cas d’une opération avec ventilation, le montant est ventilé dans chaque détail de cette opération (attribut DetailMontant). Votre MCD fait mention d’un attribut de cette nature (entité-type detail_operation, attribut montant_detail_op), c’est bien. En revanche, au niveau SQL, vous avez créé une redondance : le montant défini pour la table Operation (attribut montant_global) redonde avec la somme des montants de niveau détail.

=> Sans spécialisation, la redondance est inévitable et en garantir la validité est de votre ressort, à vos risques et périls.

3) La spécialisation permet de représenter les contraintes classiques de totalité / exclusion entre sous-types (une opération est soit sans ventilation, soit avec). Sémantiquement, c’est net, précis et très parlant.

Remarque concernant la représentation graphique ci-dessus : la cardinalité 1,1 mise entre parenthèses est un mickey propre à Power AMC permettant de faire comprendre que l’entité-type DetailOper est une propriété multivaluée de l’entité-type OperAvecVentil (donc de l’entité-type Operation). C’est l’équivalent du mickey « 1,1 (R) » du MCD de f-leb, mickey propre à Win’Design.

J’ai fait des tests à partir du MCD ci-dessous. C’est le MCD de Jphi33 avec une modification importante. Je considère qu’une opération appartient obligatoirement à une catégorie financière. Dans le cas d’une opération qui fait l’objet de plusieurs lignes de détail opération, l’opération se rattache à une catégorie financière spéciale « Ventillation ».

Vous mélangez données et métadonnées : Ventilation est une métadonnée, elle ne se situe pas au même niveau que Loisir, Jardinage etc. Rien que pour cela, Bertrand Russel vous aurez donné 0. Si f-leb a proposé un MCD en spécialisant les opérations en opérations avec / sans ventilation, c’est qu’il tient à ne pas faire d’amalgame sémantique.

Incidemment, cet amalgame que vous avez voulu conduit à l’apparition de NULL (cf. votre résultat en réponse à votre question 1 - Liste des opérations), inacceptable du point de vue de la théorie relationnelle (même si SQL — le Sorry Query Language — n’est pas regardant).



Questions subsidiaires :

Vos énoncés sont formulés en langue vernaculaire et comportent de facto des ambiguïtés : rassurez-vous, nous sommes tous concernés par ce phénomène et je ne vous jetterai pas la pierre. Par exemple, quand vous énoncez qu’un détail d’opération fait toujours référence à une catégorie financière, voulez-vous dire que cette dépendance est toujours directe (pas de sous-catégorie en jeu) ou bien qu'il arrive que cette dépendance puisse être transitive, via une sous-catégorie ?

Supposons ainsi que la liste complète des catégories financières est celle que vous fournissez avec votre jeu d’essai :

Loisir, Santé, Communication.

Ce sont les catégories financières naturelles, "métier", contrairement à Ventilation que j’exclue donc de la liste. Dans ces conditions, je regroupe et pose les questions suivantes (sachant que certaines réponses sont connues grâce à votre jeu d’essai) :

Question 1 :

Une opération sans ventilation peut-elle faire référence à une sous-catégorie financière ? (auquel cas la catégorie financière est connue via la sous-catégorie). Si oui, donner un exemple.

Question 2 :

Une opération sans ventilation peut-elle faire référence directement à une catégorie financière ? (auquel cas il n’existe pas de relation avec une sous-catégorie). Si oui, donner un exemple.

Question 3 :

Un détail d’opération financière peut-il faire référence à une sous-catégorie financière ? (auquel cas la catégorie financière de ce détail est connue via la sous-catégorie). La réponse est affirmative au vu de votre jeu d’essai.

Question 4 :

Un détail d’opération financière peut-il ne pas faire référence à une sous-catégorie, mais faire référence directement à une catégorie financière ? Si oui, donner un exemple.

Question 5 :

A supposer que deux détails D1 et D2 d’une même opération O1 fassent référence l’un à une sous-catégorie S1, l’autre à une sous-catégorie S2, ces deux sous-catégories peuvent-elles faire référence à deux catégories distinctes C1 et C2 ? La réponse est affirmative au vu de votre jeu d’essai.

Observations complémentaires :

— Les instructions SQL CREATE TABLE que vous fournissez ne comportent aucune clause FOREIGN KEY, en conséquence de quoi vous pouvez violer l’intégrité référentielle (ce que du reste vous faites, l’opération 2 (Auchan) faisant référence à la sous-catégorie inexistante 0).

— Il y a une contradiction entre le MCD et le code SQL : à chaque fois que le MCD affiche une cardinalité 0,1, le code SQL correspondant affiche NOT NULL.

— Concernant votre Question 1 (Liste des opérations), telle qu’elle est codée, la liste des colonnes de la clause GROUP BY est incomplète au sens de la norme SQL (votre GROUP BY est illégal même s'il est légitime...) Vous pourriez de préférence utiliser une clause DISTINCT (pensez à la portabilité d’un SGBD à un autre).

— Le MLD comporte des tables qui violent la troisième forme normale (3NF), à savoir operation et detail_operation.

[tavarlindar]

Bonjour,

Merci fsmrel encore une fois pour votre intervention. Vous allez finir par me convaincre de passer à la spécialisation, même si je suis sceptique sur le fond et me pose des questions sur sa mise en œuvre. Vous m’auriez prouvé que ma solution n’était pas viable ou qu’elle ne permettait pas d’avoir tel ou tel résultat, j’aurais dit ok, mais là …. Je suis néanmoins d’accord sur certains de vos arguments. On appelle un chat un chat.

Réponse à vos questions dans un premier temps

Q1) Une opération sans ventilation peut-elle faire référence à une sous-catégorie financière ?

Oui. J’achète 100 euros à la pharmacie. Je peux dire qu’il s’agit d’une dépense que j’affecte à la catégorie Santé (catégorie mère), comme je peux préciser qu’il s’agit de Pharmacie non remboursée. Dans le second cas, qui dit Pharmacie non remboursée, dit obligatoirement Catégorie : Santé et Sous catégorie : Pharmacie non remboursée. La catégorie financière est connue via la sous-catégorie, puisqu’une sous catégorie appartient obligatoirement à une et une seul catégorie financière.

Q2) Une opération sans ventilation peut-elle faire référence directement à une catégorie financière ?
Oui. Voir exemple de la question 1. Une opération non ventilée appartient obligatoirement à une catégorie financière. On n’est donc pas obliger de rattacher une opération à une sous catégorie.

Q3) Un détail d’opération financière peut-il faire référence à une sous-catégorie financière ?
Oui. Un détail opération se comporte exactement comme une opération. Un détail opération est obligatoirement rattaché à une catégorie financière et peut être rattaché à une sous catégorie.

Q4) Un détail d’opération financière peut-il ne pas faire référence à une sous-catégorie, mais faire référence directement à une catégorie financière ?

Oui. Un détail d’opération financière peut ne pas faire référence à une sous-catégorie.

Q5) A supposer que deux détails D1 et D2 d’une même opération O1 fassent référence l’un à une sous-catégorie S1, l’autre à une sous-catégorie S2, ces deux sous-catégories peuvent-elles faire référence à deux catégories distinctes C1 et C2 ?

Oui. J’achète pour 300 euros dans une grande surface. 100 euros de nourriture, 100 de loisirs et 100 de vêtement. Je reparti mes 300 dans 3 catégories financière distinctes. Libre à l’utilisateur après de préciser s’il le souhaite ou non les sous catégories. Les 100 en loisir peuvent se décomposer de 75 en Jardinage et 25 en livres.
Ce qui donnerait par exemple la décomposition suivante (4 lignes de détail opération) :
100 en Alimentation
100 en Habillement
75 en Loisir : Jardinage
25 en Loir : Livres



Commentaires sur « ma » solution:

Effectivement, il existe une redondance au niveau des montants. Dans la table opération, j’ai un champ « montant_global ». Par ailleurs, dans la table détail_opération, je gère un champ " montant_detail_op ». A moi de m’assurer que la somme des « montant_detail_op » correspond bien au montant global. Il y a un risque, mais j’assume. C’est volontaire.

Cela me facilitera à priori la tâche. Pour gérer les comptes bancaires, je vais devoir afficher par compte, toutes les opérations pour un compte bancaire donnée. D’un point de vu SQL, rien de plus simple, je fais appel à une 2 tables. La table opération et la table catégorie.
Avec ma solution, peut importe que l’opération soit détaillé ou non, un simple select * from operation peut permet d’afficher le tableau des opération en précisant la date, le libellé, le montant, la catégorie. Dans le cas d’une ventilation, étant donné que « ventilation » est considéré comme une catégorie, pas de problème.

Dans le cas où je mettrai en place la spécialisation, je vais devoir sauf erreur de ma part, faire une requête intégrant un UNION. Je vais devoir faire une requete qui dit : liste de toutes les operations sans detail + toutes les opérations avec détail. De plus si je ne gère pas au niveau de la table opération le montant global, je vais devoir le faire calculer par le système. C’est plus compliquer.

Ok, avec mon système, j’ai des valeurs nulles, et alors ? Je sais que cela n’est pas bien, car j’ai toujours lu, mais je ne vois pas où est vraiment le problème. Les requêtes fonctionnent.

Néanmoins, je veux bien étudier concrètement la solution à base de spécialisation.
Pour l’heure, si je me base sur votre MCD précédent (qui fait abstraction des catégories) j’aboutirai à la construction de 3 tables.

Par convention, j’ai l’habitude de nommer mes clés primaires Id_nom_de_la_table et les clés étrangères par nom_champ_externe_id

Première table : Opération
Id_operation
OperLibelle
OperDateValeur



2nd Table : OperSansVentil
Id_OperSansVentil (clé primaire qui ne sert à rien, hormis avoir une clé !)
Oper_id (clé etrangère = id_operation de l a table opération)
OperMontant

3ème table : DetailOper
Id_detail_operation
DetailMontant
DetailNotes
Oper_id(clé etrangère = id_operation de l a table opération)


J’ai zappé la table OperAvecDetail, car je ne vois pas l’intérêt de créer un table constitué d’une clé primaire Id_OperAvecDetail et d’une clé étrangère Oper_id.

Maintenant quid des catégories et sous catégories ?
Quid des requêtes SQL ?

Concernant le viol de certaines règles, je plaide coupable :
Les instructions SQL CREATE TABLE que vous fournissez ne comportent aucune clause FOREIGN KEY, en conséquence de quoi vous pouvez violer l’intégrité référentielle (ce que du reste vous faites, l’opération 2 (Auchan) faisant référence à la sous-catégorie inexistante 0).
Que cela ne tienne, je crée une sous catégorie dont l’id sera 0 et dont la valeur sera « aucune ». C’est une mauvaise réponse, je sais ….

Comme vous le savez déjà, je ne suis pas un pro comme vous ni un puriste. Maintenant, je veux bien apprendre et j’apprécie plus que vous pouvez l’imaginer votre dévouement et vos compétences à aider les novices de mon espèce.

PS : Ne dites pas à Bertrand Russel tout ce que vous savez … je n’aimerais pas avoir un 0 !!!!!

[JPhi33]

Bonjour,

A mon tour, après f-leb, j'essaie de recoller les morceaux. Dans le schéma ci-dessous, j'ai tenu compte de tous les arguments dont nous avons débattu.

J'ai seulement écarté la proposition de f-leb d'association réflexive sur Catégorie. Bien que, comme l'a souligné fsmrel, cette modélisation soit tout à fait valable, je pense que ce concept introduit une complexité qu'il vaut mieux réserver en réponse aux cas présentant effectivement cette complexité : hiérarchies à plus de 2 niveaux, nomenclatures, etc. A mon sens, ce n'est pas justifié ici.


On y retrouve :
- la spécialisation de l'opération sur le pivot Ventilation (i.e. en Opération ventilée et Opération non ventilée)
- la spécialisation du détail d'opération sur le pivot Catégorie
- la spécialisation de l'opération non ventilée sur le pivot Catégorie
- l'identification relative de Detail_operation par rapport à Operation_ventilée
- l'identification relative de Sous_categorie par rapport à Categorie

Ce modèle est une réponse formelle au cahier des charges. Il prémunit des NULL et est robuste aux évolutions.
Néanmoins, je comprends qu'il puisse dérouter Tavarlindar puisqu'il compte 6 entités "vides" sur 10. De plus, il comporte un inconvénient majeur. Dans l'hypothèse où il faudrait le faire évoluer, il faut bien reconnaître qu'il faut une certaine maîtrise pour retoucher ce MCD. De son propre aveu, Tavarlindar n'est pas un "pro".

Le modèle est la traduction des règles de gestion fournies. Sa complexité traduit celle des règles. Pour le simplifier, il suffirait peut-être de simplifier les règles. Je vais donc me répéter :

Dans le cas d'une opération ventilée, on considère que l'entité Operation représente l'en-tête de l'opération financière et que l'entité DetailOper contient la liste des lignes de détail. Dans le cas d'une opération non ventilée, on peut faire exactement la même considération si ce n'est qu'on se limite à une seule ligne de détail. Il s'agit d'un à-peu-près sémantique que, personnellement, j'estime acceptable.

L'à-peu-près sémantique disparaît si on énonce les nouvelles règle suivantes :
- Une opération a au moins un détail
- Une opération ayant plus d'un détail est dite ventilée
- Chaque détail d'une opération est rattaché soit à une catégorie financière sans plus de précision, soit à une sous-catégorie de catégorie financière (la spécialisation sur Categorie est inévitable !)

Le MCD correspondant est le suivant.


Ce modèle rejoint celui de fsmrel (message #6) dont j'avais déjà dit :

Mon avis est que le 1er MCD de fsmrel est le bon compromis entre complexité et exactitude.

On n'a plus maintenant que 2 entités "vides" sur 6. On notera l'indicateur d'opération ventilée (indic_op_ventilee) permettant de savoir si on s'attend à 1 ou à plusieurs détails. L'inconvénient réside dans la présence de 2 montants (opération et détail), nécessaire dans le cas d'une opération ventilée mais redondante pour une opération simple. Pour une telle opération, il conviendra de valoriser 2 fois le même montant (surtout pas de NULL !)

[fsmrel]

Bonsoir,

A mon tour, après f-leb, j'essaie de recoller les morceaux. Dans le schéma ci-dessous, j'ai tenu compte de tous les arguments dont nous avons débattu.

J'ai seulement écarté la proposition de f-leb d'association réflexive sur Catégorie. Bien que, comme l'a souligné fsmrel, cette modélisation soit tout à fait valable, je pense que ce concept introduit une complexité qu'il vaut mieux réserver en réponse aux cas présentant effectivement cette complexité : hiérarchies à plus de 2 niveaux, nomenclatures, etc. A mon sens, ce n'est pas justifié ici.

J’étais prêt à envoyer ce qui suit, quand j’ai vu votre message. Je m’étais orienté dans le sens f-leb, avec SQL à la clé. Ne m’en veuillez donc pas de faire parvenir mes cogitations en l’état, sans retouches en relation avec votre proposition.

Quoi qu’il en soit, tavarlindar va avoir de quoi titiller sa réflexion...

Ne dites pas à Bertrand Russel tout ce que vous savez … je n’aimerais pas avoir un 0 !!!!!

Sir Bertrand Russell nous a quittés il y aura quarante ans dans deux mois, mais faites attention, comme dit la chanson de Georges Brassens, si vous fouettez mes chats, son fantôme pourrait bien vous persécuter...



Dans ce qui suit, je traite d’abord du montant des détails des opérations, en essayant de montrer que, grâce aux vues (qui sont des tables, virtuelles certes, mais manipulables comme les tables de base), il est inutile de définir un attribut redondant, dédié au montant global des détails d’une opération. Ça n’est certes pas Guillaume d’Ockham qui m’en voudra (« Pluralitas non est ponenda sine necessitate »).

Ensuite, je défends l’approche de f-leb, en montrant comment, à partir de son MCD, implanter un MLD permettant de faire en sorte qu’une opération non ventilée ou un détail d’opération puissent indifféremment faire référence à une catégorie ou à une sous-catégorie, sans qu’il soit besoin de parasiter des données sémantiquement homogènes (Alimentation, Habillement, Loisir, Santé, etc.) par des métadonnées (Ventilation). Et bien entendu, le bonhomme NULL est interdit de séjour.

Problème de la redondance des montants des détails

Effectivement, il existe une redondance au niveau des montants. Dans la table opération, j’ai un champ « montant_global ». Par ailleurs, dans la table détail_opération, je gère un champ " montant_detail_op ». A moi de m’assurer que la somme des « montant_detail_op » correspond bien au montant global. Il y a un risque, mais j’assume. C’est volontaire.

Du temps où les SGBD relationnels n’existaient pas, nous procédions ainsi et nous étions à la merci d’erreurs de manipulation au stade de la préparation des travaux ou des incidents de production (la qualité de la programmation ne suffit pas). Depuis que les SGBD relationnels sont arrivés, on a pu vérifier qu’il était dommage de se passer des facilités qu’ils apportaient, notamment en relation avec la redondance des données.

Considérons à nouveau la partie Opérations du MCD de f-leb (toujours dans sa version Power AMC) :


Le MLD correspondant est le suivant :



Vous noterez en passant que la table OperAvecVentil n’a pas été générée. En effet, l’AGL met à notre disposition une case à cocher permettant d’éviter la génération de la table, et comme elle n’est pas porteuse d’attributs particuliers, inutile donc de demander cette génération.

Par ailleurs, s’il est simple de tout savoir sur une opération sans détails :

Requête R1 :

Code SQL :

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

1
2
3
SELECT a.OperId, b.OperMontant as MontantGlobal, a.OperLibelle, a.OperDateValeur 
FROM        Operation AS a
          INNER JOIN  OperSansVentil as b ON a.OperId = b.OperId ;

On ne peut quand même pas dire qu’il soit bien compliqué de tout savoir sur une opération avec détails (montants cumulés) :

Requête R2 :

Code SQL :

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

1
2
3
4
SELECT a.OperId, SUM(b.DetailMontant) AS MontantGlobal, a.OperLibelle, a.OperDateValeur
FROM   Operation AS a                  
          INNER JOIN  DetailOper AS b ON a.OperId = b.OperId
GROUP BY a.OperId, a.OperLibelle, a.OperDateValeur ;

A partir du de la valeur suivante de la table DetailOper :

Code :

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

1
2
3
4
5
6
7
8
 
OperId  DetailId  DetailMontant
------  --------  -------------
   1       1         -100
   2       1          -50
   2       2          -20
   2       3          -30
   2       4          -70

Le résultat de la requête est le suivant :

Code :

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

1
2
3
4
5
 
OperId  MontantGlobal  OperLibelle  OperDateValeur
------  -------------  -----------  --------------
   1       -100        Pharma Toto   2009-11-26
   2       -170        Auchan        2009-11-03

Dans le cas où je mettrai en place la spécialisation, je vais devoir sauf erreur de ma part, faire une requête intégrant un UNION. Je vais devoir faire une requete qui dit : liste de toutes les operations sans detail + toutes les opérations avec détail. De plus si je ne gère pas au niveau de la table opération le montant global, je vais devoir le faire calculer par le système. C’est plus compliquer.

Il est exact que l’UNION s’impose, mais une fois de plus ça reste une opération basique. Cela dit, il suffit de créer une vue pour avoir l’équivalent de votre table Operation (compte non tenu des attributs concernant les comptes bancaires et les catégories, mais on complètera cela par la suite). Cette vue est l’union des requêtes R1 et R2 :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
CREATE VIEW OPERATION_UNION (OperId, MontantGlobal, OperLibelle, OperDateValeur)
AS
  SELECT a.OperId, b.OperMontant AS MontantGlobal, a.OperLibelle, a.OperDateValeur 
  FROM   Operation AS a
            INNER JOIN  OperSansVentil AS b ON a.OperId = b.OperId 
 UNION
  SELECT a.OperId, SUM(b.DetailMontant) AS MontantGlobal, a.OperLibelle, a.OperDateValeur
  FROM   Operation AS a                  
            INNER JOIN  DetailOper AS b ON a.OperId = b.OperId
  GROUP BY a.OperId, a.OperLibelle, a.OperDateValeur ;

La requête suivante permet d’obtenir l’ensemble des opérations :

Requête R3 :

SELECT * FROM OPERATION_UNION

Code :

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

1
2
3
4
5
6
7
8
 
OperId  MontantGlobal  OperLibelle  OperDateValeur
------  -------------  -----------  --------------
1         -100.00      Pharma Toto    2009-11-26
2         -170.00      Auchan         2009-11-03
3         -250.00      Maurice        2009-11-15
4          -30.00      Albert         2009-11-10
5        -1000.00      Mezigue        2009-11-10

(Les opérations 3, 4 et 5 ne font pas l’objet de détails).

Comme vous parlez de comptes bancaires, j’ai ajouté une table Compte et le MLD devient :


La vue OPERATION_UNION peut être remplacée par la suivante, OPERATION_V, avec laquelle on tient compte de l’attribut CompteId :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
CREATE VIEW OPERATION_V (OperId, MontantGlobal, OperLibelle, OperDateValeur, CompteId)
AS
  SELECT a.OperId, b.OperMontant AS MontantGlobal, a.OperLibelle, a.OperDateValeur, a.CompteId 
  FROM   Operation AS a
            INNER JOIN  OperSansVentil AS b ON a.OperId = b.OperId 
 UNION
  SELECT a.OperId, SUM(b.DetailMontant) AS MontantGlobal, a.OperLibelle, a.OperDateValeur, a.CompteId
  FROM   Operation AS a                  
            INNER JOIN  DetailOper AS b ON a.OperId = b.OperId
  GROUP BY a.OperId, a.OperLibelle, a.OperDateValeur, a.CompteId ;

Si l’on veut connaître les opérations pour l’ensemble des comptes (on ne traite pas encore des catégories) :

Code SQL :

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

1
2
3
4
SELECT a.CompteTitulaire, b.MontantGlobal, b.OperLibelle, b.OperDateValeur
FROM     Compte AS a
           INNER JOIN OPERATION_V AS b ON a.COmpteId = b.CompteId
ORDER BY a.CompteTitulaire ;

Code :

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

1
2
3
4
5
6
7
8
 
 CompteTitulaire  MontantGlobal  OperLibelle  OperDateValeur
----------------  -------------  -----------  --------------
   Titulaire 1      -100.00      Pharma Toto    2009-11-26
   Titulaire 1      -250.00      Maurice        2009-11-15
   Titulaire 2      -170.00      Auchan         2009-11-03
   Titulaire 2     -1000.00      Mezigue        2009-11-10
   Titulaire 3       -30.00      Albert         2009-11-10

Si l’on veut connaître les opérations pour un compte en particulier :

Code SQL :

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

1
2
3
4
SELECT a.CompteTitulaire, b.MontantGlobal, b.OperLibelle, b.OperDateValeur
FROM     Compte AS a
           INNER JOIN OPERATION_V AS b ON a.COmpteId = b.CompteId
WHERE a.CompteTitulaire = 'Titulaire 2' ;

La vue OPERATION_V permet d’encapsuler au niveau logique la prétendue complexité résultant de la spécialisation des opérations et facilitera donc la tâche. Le but de la manœuvre est de faire en sorte que la vue OPERATION_V équivaille à votre table OPERATION, sans qu’il soit besoin de se soucier des redondances (entre autres choses). Il devient très simple d’afficher toutes les opérations pour un compte bancaire donné ou pour l’ensemble des comptes.
Peu importe que l’opération soit détaillée ou non, un simple SELECT * FROM OPERATION_V permet d’afficher les opérations avec tous leurs attributs (une fois de plus, aux catégories près, mais on va y venir).


Problème de la relation avec les catégories

Je traite d’abord des réponses à mes questions.

Question 1 :
Une opération sans ventilation peut-elle faire référence à une sous-catégorie financière ? (auquel cas la catégorie financière est connue via la sous-catégorie).

Vous répondez :

Oui. J’achète 100 euros à la pharmacie. Je peux dire qu’il s’agit d’une dépense que j’affecte à la catégorie Santé (catégorie mère), comme je peux préciser qu’il s’agit de Pharmacie non remboursée. Dans le second cas, qui dit Pharmacie non remboursée, dit obligatoirement Catégorie : Santé et Sous catégorie : Pharmacie non remboursée. La catégorie financière est connue via la sous-catégorie, puisqu’une sous catégorie appartient obligatoirement à une et une seul catégorie financière.

Donc vous insérerez dans votre table t_operation une ligne pour laquelle la colonne categorie_fi_id prendra la valeur 3 (Santé) et la colonne sous_categorie_fi_id prendra la valeur 12 (Pharmacie non remboursée). Et comme une « sous catégorie appartient obligatoirement à une et une seul catégorie financière », il existe la dépendance fonctionnelle :

{sous_categorie_fi_id} → {categorie_fi_id}

qui est la conséquence du fait qu’une sous-catégorie détermine une catégorie : la troisième forme normale (3NF) est mise à mal. A l’occasion de votre 1er message, vous souhaitiez un avis sur votre modèle, et il se trouve que malmener la 3NF révèle un défaut de modélisation...

A ma question :

Question 2 :
Une opération sans ventilation peut-elle faire référence directement à une catégorie financière ? (auquel cas il n’existe pas de relation avec une sous-catégorie).

Vous répondez :

Oui. Voir exemple de la question 1. Une opération non ventilée appartient obligatoirement à une catégorie financière. On n’est donc pas obliger de rattacher une opération à une sous catégorie.

Si on ne rattache pas l’opération à une sous-catégorie, la colonne sous_categorie_fi_id de votre table t_operation devra en l’occurrence être marquée NULL (ce que confirme la cardinalité 0,1 portée par la patte connectant operation et appartenir_scf, selon votre MCD). Vous connaissez ma position quant au bonhomme NULL : par une bonne modélisation, le défi est de lui interdire de se manifester dans les tables (en passant, vous avez retenu l’option NOT NULL pour la colonne sous_categorie_fi_id de la table t_operation...)

A ma question :

Question 3 :
Un détail d’opération financière peut-il faire référence à une sous-catégorie financière ? (auquel cas la catégorie financière de ce détail est connue via la sous-catégorie). La réponse est affirmative au vu de votre jeu d’essai.

Vous répondez :

Oui. Un détail opération se comporte exactement comme une opération. Un détail opération est obligatoirement rattaché à une catégorie financière et peut être rattaché à une sous catégorie.

J’en déduis que, si on a une opération pour un montant de 100 euros, portant uniquement sur de la pharmacie, on peut :

1) Rattacher directement cette opération à la catégorie Santé (opération sans ventilation) ;
2) Créer un détail de 100 euros et rattacher celui-ci directement à la catégorie Santé.
3) Créer un détail de 100 euros et rattacher celui-ci directement à la sous-catégorie Pharmacie non remboursée, donc rattacher ce détail indirectement à la catégorie Santé.

Une opération est donc obligatoirement rattachée à une catégorie financière, mais c’est soit directement, soit indirectement. Dans le cas d’un rattachement indirect, transitif, l’opération ne doit pas faire référence à la catégorie (ceci vaut du reste pour les opérations sans ventilation quand elles font référence à une sous-catégorie, cf. votre réponse à ma Question 1).

Et surtout, ne me parlez pas de rattacher les détails à la fausse catégorie « ventilation », ce qui est incompatible avec le travail de modélisation (mélange de données et de métadonnées). Le défi est bien de modéliser en appelant un chat un chat, ce dont vous êtes du reste convenu.

A ma question :

Question 4 :
Un détail d’opération financière peut-il ne pas faire référence à une sous-catégorie, mais faire référence directement à une catégorie financière ? Si oui, donner un exemple.

Vous répondez :

Oui. Un détail d’opération financière peut ne pas faire référence à une sous-catégorie.

A défaut d’exemple de votre part, j’e m’appuie sur votre réponse à la question 5 : J’achète pour 100 euros de nourriture, donnant lieu à un détail de 100 euros, référençant directement la catégorie Alimentation.

A ma question :

Question 5 :
A supposer que deux détails D1 et D2 d’une même opération O1 fassent référence l’un à une sous-catégorie S1, l’autre à une sous-catégorie S2, ces deux sous-catégories peuvent-elles faire référence à deux catégories distinctes C1 et C2 ? La réponse est affirmative au vu de votre jeu d’essai.

Vous répondez :

Oui. J’achète pour 300 euros dans une grande surface. 100 euros de nourriture, 100 de loisirs et 100 de vêtement. Je reparti mes 300 dans 3 catégories financière distinctes. Libre à l’utilisateur après de préciser s’il le souhaite ou non les sous catégories. Les 100 en loisir peuvent se décomposer de 75 en Jardinage et 25 en livres.
Ce qui donnerait par exemple la décomposition suivante (4 lignes de détail opération) :
100 en Alimentation
100 en Habillement
75 en Loisir : Jardinage
25 en Loisir : Livres

On est bien d’accord. Toutefois, pour une opération, vous faites mention de 3 catégories distinctes. Votre MCD n’est pas en phase, puisqu’il exprime le fait qu’une opération fait référence à exactement une catégorie.
Vous allez me dire que dans le cas d’une opération ventilée, la catégorie « ventilation » remplace les autres, mais vous connaissez ma position....

En tout état de cause, sur la base de vos réponses, je pars du principe qu’une opération non ventilée ou un détail d’opération peuvent indifféremment faire référence à une catégorie ou à une sous-catégorie.

Pour cette raison, je persiste à m’appuyer sur l’approche de f-leb.

Je propose de compléter ainsi le MCD précédent :


L’entité-type CategorieNoeud représente l’ensemble des catégories et sous-catégories. Le lien « Est_un » entre CategorieNoeud et SousCategorie permet d’identifier SousCategorie relativement à CategorieNoeud (cardinalités 0,1/1,1) tout en évitant de provoquer la génération d’un cycle entre les deux entités-types (la représentation est du type IE plutôt que Merise, sinon il y génération effective d’un cycle). Le mickey « (D) » marque la dépendance de SousCategorie.

Le MLD correspondant est le suivant :



Supposons que l’on veuille lister les opérations par compte, en prenant en compte des catégories (et sous-catégories). Pour faciliter la suite du travail, on peut commencer par définir une vue concernant les détails des opérations ventilées, puis une vue du niveau opération ventilée, ainsi qu’une vue du niveau opération non ventilée, puis une vue du niveau opération :

Vue DetailOper_V, du niveau détail opération ventilée :

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
CREATE VIEW DetailOper_V (OperId, DetailId, DetailMontant, DetailNote, Categorie, SousCategorie)
AS
     SELECT h.OperId, h.DetailId, h.DetailMontant, h.DetailNote, f.CategNom, c.CategNom
     FROM   DetailOper AS h
              INNER JOIN CategorieNoeud AS c ON h.CategId = c.CategNoeudId
              INNER JOIN SousCategorie AS e ON e.SousCategId = c.CategNoeudId
              INNER JOIN CategorieNoeud AS f ON e.CategParenteId = f.CategNoeudId
   UNION
     SELECT h.OperId, h.DetailId, h.DetailMontant, h.DetailNote, c.CategNom, '---' 
     FROM   DetailOper AS h   
              INNER JOIN CategorieNoeud AS c ON h.CategId = c.CategNoeudId
     WHERE NOT EXISTS
              (SELECT *
               FROM   SousCategorie AS e
               WHERE  c.CategNoeudId = e.SousCategId) ;

Au résultat :

SELECT * FROM DetailOper_V

Code :

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

1
2
3
4
5
6
7
8
 
OperId  DetailId  DetailMontant  DetailNote  Categorie     SousCategorie           
------  --------  -------------  ----------  ---------     ------------------------
  1        1          -100                   Santé         Pharmacie non remboursée  
  2        1           -50                   Santé         Pharmacie non remboursée  
  2        2           -20                   Loisir        Livres                    
  2        3           -30                   Loisir        Jardinage                 
  2        4           -70                   Alimentation   ---

(Les 4 premiers détails font référence à une sous-catégorie et le dernier fait directement référence à une catégorie).

Vue OperAvecVentil_V, du niveau opération ventilée :

Code SQL :

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

1
2
3
4
5
6
CREATE VIEW OperAvecVentil_V (OperId, MontantGlobal, OperLibelle, OperDateValeur, CompteId, Categorie, SousCategorie)
AS
   SELECT y.OperId, SUM(x.DetailMontant) AS MontantGlobal, y.OperLibelle, y.OperDateValeur, y.CompteId, x.Categorie, x.SousCategorie
   FROM   DetailOper_V as x
             INNER JOIN  Operation as y ON x.OperId = y.OperId
   GROUP BY y.OperId, y.OperLibelle, y.OperDateValeur, y.CompteId, x.Categorie, x.SousCategorie ;

Au résultat :

SELECT * FROM OperAvecVentil_V

Code :

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

1
2
3
4
5
6
7
8
  
OperId  MontantGlobal  OperLibelle  OperDateValeur  CompteId  Categorie    SousCategorie
------  -------------  -----------  --------------  --------  ---------    -------------
   1        -100       Pharma Toto  2009-11-26        1      Santé         Pharmacie non remboursée
   2         -70       Auchan       2009-11-03        2      Alimentation   ---
   2         -30       Auchan       2009-11-03        2      Loisir        Jardinage
   2         -20       Auchan       2009-11-03        2      Loisir        Livres
   2         -50       Auchan       2009-11-03        2      Santé         Pharmacie non remboursée

Vue OperSansVentil_V, du niveau opération non ventilée :

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
CREATE VIEW OperSansVentil_V (OperId, MontantGlobal, OperLibelle, OperDateValeur, CompteId, Categorie, SousCategorie)
AS
     SELECT a.OperId, b.OperMontant, a.OperLibelle, a.OperDateValeur, a.CompteId, f.CategNom, c.CategNom
     FROM   Operation AS a
              INNER JOIN OperSansVentil AS b ON a.OperId = b.OperId
              INNER JOIN CategorieNoeud AS c ON b.CategId = c.CategNoeudId
              INNER JOIN SousCategorie AS e ON e.SousCategId = c.CategNoeudId
              INNER JOIN CategorieNoeud AS f ON e.CategParenteId = f.CategNoeudId
   UNION
     SELECT a.OperId, b.OperMontant, a.OperLibelle, a.OperDateValeur, a.CompteId, c.CategNom, '---' 
     FROM   Operation AS a
              INNER JOIN OperSansVentil AS b ON a.OperId = b.OperId
              INNER JOIN CategorieNoeud AS c ON b.CategId = c.CategNoeudId
     WHERE NOT EXISTS
              (SELECT *
               FROM   SousCategorie AS e
               WHERE  c.CategNoeudId = e.SousCategId) ;

Au résultat :

SELECT * FROM OperSansVentil_V

Code :

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

1
2
3
4
5
6
  
OperId  MontantGlobal  OperLibelle  OperDateValeur  CompteId  Categorie     SousCategorie
------  -------------  -----------  --------------  --------  ---------     -------------
   3      -250.00     Maurice       2009-11-15        1    Communication    ---
   4       -30.00     Albert        2009-11-10        3    Loisir           ---
   5     -1000.00     Mezigue       2009-11-10        2    Loisir         Jardinage

(Les 2 premières opérations font directement référence à une catégorie et la suivante fait référence à une sous-catégorie).

Vue OperQuelconque_V du niveau opération :

Code SQL :

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

1
2
3
4
5
6
7
CREATE VIEW OperQuelconque_V (OperId, MontantGlobal, OperLibelle, OperDateValeur, CompteId, Categorie, SousCategorie)
AS
   SELECT OperId, MontantGlobal, OperLibelle, OperDateValeur, CompteId, Categorie, SousCategorie
   FROM   OperAvecVentil_V
UNION
   SELECT OperId, MontantGlobal, OperLibelle, OperDateValeur, CompteId, Categorie, SousCategorie
   FROM   OperSansVentil_V

Au résultat :

SELECT * FROM OperQuelconque_V

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
OperId  MontantGlobal  OperLibelle  OperDateValeur  CompteId  Categorie     SousCategorie
------  -------------  -----------  --------------  --------  ---------     -------------
   1       -100.00     Pharma Toto  2009-11-26        1      Santé         Pharmacie non remboursée
   2        -70.00     Auchan       2009-11-03        2      Alimentation   ---
   2        -50.00     Auchan       2009-11-03        2      Santé         Pharmacie non remboursée
   2        -30.00     Auchan       2009-11-03        2      Loisir        Jardinage
   2        -20.00     Auchan       2009-11-03        2      Loisir        Livres
   3       -250.00     Maurice      2009-11-15        1      Communication    ---
   4        -30.00     Albert       2009-11-10        3      Loisir           ---
   5      -1000.00     Mezigue      2009-11-10        2      Loisir       Jardinage

Si maintenant on désire la liste des opérations par compte :

Code SQL :

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

1
2
3
4
SELECT a.CompteTitulaire, b.OperId, b.MontantGlobal, b.OperLibelle, b.OperDateValeur, b.Categorie, b.SousCategorie
FROM     Compte AS a
           INNER JOIN OperQuelconque_V AS b ON a.CompteId = b.CompteId
ORDER BY a.CompteTitulaire, b.OperId ;

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CompteTitulaire  OperId  MontantGlobal  OperLibelle  OperDateValeur  Categorie     SousCategorie
---------------  ------  -------------  -----------  --------------  ---------     -------------
  Titulaire 1       1      -100.00      Pharma Toto    2009-11-26    Santé         Pharmacie non remboursée
  Titulaire 1       3      -250.00      Maurice        2009-11-15    Communication   ---
  Titulaire 2       2       -70.00      Auchan         2009-11-03    Alimentation    ---
  Titulaire 2       2       -50.00      Auchan         2009-11-03    Santé         Pharmacie non remboursée
  Titulaire 2       2       -30.00      Auchan         2009-11-03    Loisir        Jardinage
  Titulaire 2       2       -20.00      Auchan         2009-11-03    Loisir        Livres
  Titulaire 2       5     -1000.00      Mezigue        2009-11-10    Loisir        Jardinage
  Titulaire 3       4       -30.00      Albert         2009-11-10    Loisir          ---

Ou encore, au niveau catégorie :

Code SQL :

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

1
2
3
4
5
SELECT a.CompteTitulaire, b.OperId, SUM(b.MontantGlobal) AS MontantGlobal, b.OperLibelle, b.OperDateValeur, b.Categorie  
FROM     Compte AS a
           INNER JOIN OperQuelconque_V AS b ON a.CompteId = b.CompteId
GROUP BY a.CompteTitulaire, b.OperId, b.OperLibelle, b.OperDateValeur, b.Categorie
ORDER BY a.CompteTitulaire, b.OperId ;

Code :

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

1
2
3
4
5
6
7
8
9
CompteTitulaire  OperId  MontantGlobal  OperLibelle  OperDateValeur  Categorie
---------------  ------  -------------  -----------  --------------  ----------
  Titulaire 1       1      -100.00      Pharma Toto    2009-11-26    Santé
  Titulaire 1       3      -250.00      Maurice        2009-11-15    Communication
  Titulaire 2       2       -70.00      Auchan         2009-11-03    Alimentation
  Titulaire 2       2       -50.00      Auchan         2009-11-03    Loisir
  Titulaire 2       2       -50.00      Auchan         2009-11-03    Santé
  Titulaire 2       5     -1000.00      Mezigue        2009-11-10    Loisir
  Titulaire 3       4       -30.00      Albert         2009-11-10    Loisir

Etc., etc.

Pensez-vous qu'il faille une semaine pour développer ces quelques vues ?

[tavarlindar]

Bonjour fsmrel,

Avant d'aller plus en avant dans la compréhension de cette solution, aurais-tu par chance (chance pour moi) conserver la définition mysql de ces tables ? Ceci me permettra de comprendre plus rapidement ces notions qui sont totalement nouvelles.

Par avance un grand merci.

[fsmrel]

Bonsoir tavarlindar,

J’avais conservé le MCD et le MLD. J’ai donc procédé à la génération d’un script (toujours Power AMC), qui donne ceci :

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
102
103
104
105
106
107
108
109
110
111
112
113
 
/*==============================================================*/
/* Table : CategorieNoeud                                       */
/*==============================================================*/
create table CategorieNoeud
(
   CategNoeudId                   int                            not null,
   CategNom                       varchar(48)                    not null,
   primary key (CategNoeudId)
);
/*==============================================================*/
/* Table : Compte                                               */
/*==============================================================*/
create table Compte
(
   CompteId                       int                            not null,
   CompteTitulaire                varchar(48)                    not null,
   primary key (CompteId)
);
/*==============================================================*/
/* Table : Operation                                            */
/*==============================================================*/
create table Operation
(
   OperId                         int                            not null,
   OperLibelle                    varchar(48)                    not null,
   OperDateValeur                 datetime                       not null,
   CompteId                       int                            not null,
   primary key (OperId)
);
/*==============================================================*/
/* Index : Oper_Compte_FK                                       */
/*==============================================================*/
create index Oper_Compte_FK on Operation
(
   CompteId
);
/*==============================================================*/
/* Table : DetailOper                                           */
/*==============================================================*/
create table DetailOper
(
   OperId                         int                            not null,
   DetailId                       int                            not null,
   DetailMontant                  int                            not null,
   DetailNote                     varchar(32)                    not null,
   CategNoeudId                   int                            not null,
   primary key (OperId, DetailId)
);
/*==============================================================*/
/* Index : DetailOper_CatNoeud_FK                               */
/*==============================================================*/
create index DetailOper_CatNoeud_FK on DetailOper
(
   CategNoeudId
);
/*==============================================================*/
/* Table : OperSansVentil                                       */
/*==============================================================*/
create table OperSansVentil
(
   OperId                         int                            not null,
   OperMontant                    decimal(10,2)                  not null,
   CategNoeudId                   int                            not null,
   primary key (OperId)
);
/*==============================================================*/
/* Index : OperSansVentil_CatNoeud_FK                           */
/*==============================================================*/
create index OperSansVentil_CatNoeud_FK on OperSansVentil
(
   CategNoeudId
);
/*==============================================================*/
/* Table : SousCategorie                                        */
/*==============================================================*/
create table SousCategorie
(
   SousCategId                    int                            not null,
   CategParenteId                 int                            not null,
   primary key (SousCategId)
);
/*==============================================================*/
/* Index : CategorieParente_FK                                  */
/*==============================================================*/
create index SousCat_Cat_Parente_FK on SousCategorie
(
   CategParenteId
);
/*==============================================================*/
/* Cléq étrangères                                              */
/*==============================================================*/
 
alter table DetailOper add constraint FK_DetailOper_CatNoeud foreign key (CategNoeudId)
      references CategorieNoeud (CategNoeudId) on delete restrict on update restrict;
 
alter table DetailOper add constraint FK_DetailOper_Oper foreign key (OperId)
      references Operation (OperId) on delete cascade on update restrict;
 
alter table OperSansVentil add constraint FK_OperSansVentil_CatNoeud foreign key (CategNoeudId)
      references CategorieNoeud (CategNoeudId) on delete restrict on update restrict;
 
alter table OperSansVentil add constraint FK_OperSansVentil_Oper foreign key (OperId)
      references Operation (OperId) on delete cascade on update restrict;
 
alter table Operation add constraint FK_Oper_Compte foreign key (CompteId)
      references Compte (CompteId) on delete restrict on update restrict;
 
alter table SousCategorie add constraint FK_SousCat_Cat_Etre foreign key (SousCategId)
      references CategorieNoeud (CategNoeudId) on delete cascade on update restrict;
 
alter table SousCategorie add constraint FK_SousCat_Cat_Parente foreign key (CategParenteId)
      references CategorieNoeud (CategNoeudId) on delete restrict on update restrict;

[tavarlindar]

Bonjour,

A fin de reprendre facilement le sujet plusieurs mois après, j'ai repris les exemples de fsmrel ainsi que les noms des tables et champs.

Rappel :
on va saisir 5 opérations différentes :
1) le 26 nov 2009 : achat sur le compte 1 de 100 à la pharmacie Toto, dépense affectée en pharmacie non remboursée (sous catégorie appartenant à la catégorie Santé)
Concernant cette première opération, on démarre avec un cas possible mais pas logique. On ventile l'opération alors que l'opération est constituée que d'une seule ligne (un seul détail d'opération). La logique aurait plutôt voulu qu'on l'affecte directement à une opération non ventilée.
Ce cas peut néanmoins arriver.
2) le 3 nov 2009 : achat sur le compte 2 total de 170 a Auchan, dépense se ventilant en :
- 50 catégorie Sante et sous catégorie Pharmacie non remboursée
- 20 catégorie Loisirs et sous catégorie Livres
- 30 catégorie Loisirs et sous catégorie Jardinage
- 70 catégorie Alimentation et on ne précise pas de sous catégorie
3) le 15 nov 2009 : achat de 250 a Maurice en Communication (pas de sous catégorie) sur le compte 1 - pas de ventilation
4) Le 10 nov 2009 : achat en Loisirs a Albert pour 30 (compte 3) - pas de ventilation
5) le 10 nov 2009 : achat a Mezigue pour 100 en Jardinage (catégorie Loisirs)(compte 2) - pas de ventilation

J'ai reconstruit les tables avec les données, ce qui donne :

Table Compte

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE `Compte` (
  `CompteId` int(11) NOT NULL auto_increment,
  `CompteTitulaire` varchar(48) NOT NULL,
  PRIMARY KEY  (`CompteId`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

-- 
-- Contenu de la table `Compte`
-- 

INSERT INTO `Compte` (`CompteId`, `CompteTitulaire`) VALUES (1, 'Compte 1 BNP'),
(2, 'Compte 2  LCL'),
(3, '');

Table Operation

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
CREATE TABLE `Operation` (
  `OperId` int(11) NOT NULL auto_increment,
  `OperLibelle` varchar(48) NOT NULL,
  `OperDateValeur` date NOT NULL,
  `CompteId` int(11) NOT NULL,
  PRIMARY KEY  (`OperId`),
  KEY `CompteId` (`CompteId`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

-- 
-- Contenu de la table `Operation`
-- 

INSERT INTO `Operation` (`OperId`, `OperLibelle`, `OperDateValeur`, `CompteId`) VALUES (1, 'Pharmacie Toto', '2009-11-16', 1),
(2, 'Auchan', '2009-11-03', 2),
(3, 'Maurice', '2009-11-15', 1),
(4, 'Albert', '2009-11-10', 3),
(5, 'Mezigue', '2010-11-10', 2);

-- 
-- Contraintes pour la table `Operation`
-- 
ALTER TABLE `Operation`
  ADD CONSTRAINT `Operation_ibfk_1` FOREIGN KEY (`CompteId`) REFERENCES `Compte` (`CompteId`) ON UPDATE CASCADE;

Table OperSansVentil

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
CREATE TABLE `OperSansVentil` (
  `OperId` int(11) NOT NULL,
  `OperMontant` decimal(10,2) NOT NULL,
  `CategNoeudId` int(11) NOT NULL,
  PRIMARY KEY  (`OperId`),
  KEY `CategNoeudId` (`CategNoeudId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 
-- Contenu de la table `OperSansVentil`
-- 

INSERT INTO `OperSansVentil` (`OperId`, `OperMontant`, `CategNoeudId`) VALUES (3, '-250.00', 7),
(4, '-30.00', 2),
(5, '-1000.00', 5);

-- 
-- Contraintes pour la table `OperSansVentil`
-- 
ALTER TABLE `OperSansVentil`
  ADD CONSTRAINT `OperSansVentil_ibfk_2` FOREIGN KEY (`CategNoeudId`) REFERENCES `CategorieNoeud` (`CategNoeudId`) ON UPDATE CASCADE,
  ADD CONSTRAINT `OperSansVentil_ibfk_1` FOREIGN KEY (`OperId`) REFERENCES `Operation` (`OperId`) ON DELETE CASCADE ON UPDATE CASCADE;

Table DetailOper
Lorsque j'ai créé cette table, j'avais défini comme clé primaire le champ DetailId. Ensuite je m'étais contenté de definir OperID comme etant une cle etrangere se referant aun champ OperId de la table Operation.
En regardant le script de fsmrel, je m'aperçois que néni. La cle primaire de la table DetailOper est définie sur OperdID et DetailId.
J'ai fais la modification, mais je ne comprends pas trop pourquoi.
Quelle aurait été la conséquence si je n'avais gardé comme clé primaire que DetailId ?
Au passage, j'ai définie comme clé unique le DetailId avec l'auto incrémentation. Ceci explique que les résultats obtenus à partir du code ci-dessous différent des résultats de fsmrel (au niveau du DetailId).

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
CREATE TABLE `DetailOper` (
  `OperId` int(11) NOT NULL,
  `DetailId` int(11) NOT NULL auto_increment,
  `DetailMontant` int(11) NOT NULL,
  `DetailNote` varchar(32) NOT NULL,
  `CategNoeudId` int(11) NOT NULL,
  PRIMARY KEY  (`OperId`,`DetailId`),
  UNIQUE KEY `DetailId` (`DetailId`),
  KEY `DetailOper_CatNoeud_FK` (`CategNoeudId`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

-- 
-- Contenu de la table `DetailOper`
-- 

INSERT INTO `DetailOper` (`OperId`, `DetailId`, `DetailMontant`, `DetailNote`, `CategNoeudId`) VALUES (1, 1, -100, '', 4),
(2, 2, -50, 'plantes', 4),
(2, 3, -20, 'Comprendre La spécialisation', 6),
(2, 4, -30, 'Pelle', 5),
(2, 5, -70, '', 1);

-- 
-- Contraintes pour la table `DetailOper`
-- 
ALTER TABLE `DetailOper`
  ADD CONSTRAINT `DetailOper_ibfk_1` FOREIGN KEY (`OperId`) REFERENCES `Operation` (`OperId`) ON DELETE CASCADE,
  ADD CONSTRAINT `DetailOper_ibfk_2` FOREIGN KEY (`CategNoeudId`) REFERENCES `CategorieNoeud` (`CategNoeudId`) ON DELETE CASCADE,
  ADD CONSTRAINT `FK_DetailOper_Oper` FOREIGN KEY (`OperId`) REFERENCES `Operation` (`OperId`) ON DELETE CASCADE;

Les définitions des tables CategorieNoeud et SousCategorie :
Le mcd et mld m'ont longtemps dérouté. Après mise en place des tables, la structure me parait maintenant évidente.

Ainsi si j'ai bien compris si je prends la définition suivantes pour les 2 tables :

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
CREATE TABLE `CategorieNoeud` (
  `CategNoeudId` int(11) NOT NULL auto_increment,
  `CategNom` varchar(48) NOT NULL,
  PRIMARY KEY  (`CategNoeudId`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

-- 
-- Contenu de la table `CategorieNoeud`
-- 

INSERT INTO `CategorieNoeud` (`CategNoeudId`, `CategNom`) VALUES (1, 'Alimentation'),
(2, 'Loisirs'),
(3, 'Sante'),
(4, 'Pharmacie non remboursee'),
(5, 'Jardinage'),
(6, 'Livres'),
(7, 'Communication');

-- 
-- Contenu de la table `SousCategorie`
-- 

INSERT INTO `SousCategorie` (`SousCategId`, `CategParenteId`) VALUES (5, 2),
(6, 2),
(4, 3);
-- 
-- Contraintes pour la table `SousCategorie`
-- 
ALTER TABLE `SousCategorie`
  ADD CONSTRAINT `SousCategorie_ibfk_2` FOREIGN KEY (`CategParenteId`) REFERENCES `CategorieNoeud` (`CategNoeudId`),
  ADD CONSTRAINT `SousCategorie_ibfk_1` FOREIGN KEY (`SousCategId`) REFERENCES `CategorieNoeud` (`CategNoeudId`) ON DELETE CASCADE;

A partir de la si je souhaite avoir la liste complète des couples categorie--sous-categorie, on peut faire

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT c.CategNom CATEGORIE, '---'
FROM CategorieNoeud AS c
WHERE NOT
EXISTS (
	SELECT *
	FROM SousCategorie AS e
	WHERE c.CategNoeudId = e.SousCategId
	)
	UNION
SELECT c1.CategNom CATEGORIE, c2.CategNom 'Sous CATEGORIE'
FROM SousCategorie AS  sc INNER JOIN CategorieNoeud AS c1 ON sc.CategParenteId = c1.CategNoeudId
INNER JOIN  CategorieNoeud AS c2 ON sc.SousCategId = c2.CategNoeudId

ce qui donne :


Peut-on faire mieux que cette requête ?

Globalement, après avoir tester la solution de fsmrel, je suis confiant dans la robustesse du MCD. La spécialisation me parait moins floue qu'auparavant et je suis convaincue de son utilité. L'argument qui m'a fait fait changer d'avis :

D’une manière générale, la spécialisation permet d’appeler un chat un chat et de ne pas laisse les choses dans l’ombre.

Naturellement toutes les interventions de JPhi33 et f-leb m'ont fait prendre conscience que je faisais fausse route avec mon MCD initial.
Je ne connaissais pas les vues. C'est effectivement très pratique. En me documentant sur le sujet, j'ai découvert en même temps la notion de trigger.
Vos interventions m'ont forcées a me replonger dans les notions de table InnoDB de Mysql.
Je m'aperçois que mon MCD initial était un peu dicté par mes maigres connaissances en matière SQL. Évidemment, c'était la mauvaise approche. Un MCD doit faire abstraction de sa mise en œuvre, mais en pratique il est difficile d'imaginer ce que l'on ne sait pas faire.

Je tiens à ce stade à vous adresser à tous les trois mes plus vifs remerciements. Vos interventions ont été remarquables. Le fait que des personnes expérimentées et compétentes comme vous daignent aider des novices est vraiment formidable.
Je ne clôture pas encore cette longue discussion (j'attends d'avoir mis concrètement le tout en place), mais espère le faire le plus rapidement possible.

Encore merci a vous,
Tavar