Discussion :

[tavarlindar]

Bonjour à tous,

Qui dit épisode III, dit pour rappel Episode I et Episode II.

Il y a 2 sujets pour lesquels j'ai le plaisir de vous solliciter.

1) La gestion du paiement de commissions
2) La gestion des emails associés au paiement des échéances

Après avoir présenter une partie du MCD, je présenterai le contexte et ensuite (malheureusement) pourquoi je n'arrive pas à finaliser le MCD.

MCD actuel:


ce qui donne en gros le MLD suivant:


Rappel succinct du contexte général : domaine de la location de bateaux
Un client fait une demande sur Internet pour louer un bateau. Une fois que sa demande se concrétise commercialement, on fait une réservation. Cette réservation entraine l'établissement d'échéanciers qui viennent directement alimenter des comptes bancaires. (pour une meilleure compréhension voir début de l'
Episode I)

Point 1 - Gestion du paiement de commissions

Contexte et cahier des charges:

Marge Brute d’une réservation = (Somme des paiements du client – Somme des paiements fournisseurs) associées à la dite réservation.
Marge Brute d’un dossier commercial = Marge Brute d’une demande = somme des marges des réservations associées. Dans 90% des cas, une demande d'info (table demande) qui se concrétise commercialement fait d'objet d'une seule réservation (dossier de réservation). Il peut néanmoins arriver que soit rattachée à une demande plusieurs dossiers de réservations.

Marge Brute Finale:
La marge brute finale est la marge calculée lors de la clôture du dossier. C’est le service gestion qui définit la clôture: date et montant. En principe, la marge brute finale ne peut pas être calculée tant que le client n’a pas terminé sa location et/ou que tous les fournisseurs liés à cette réservation n’ont pas été payés.

Règle 1: La marge brute finale se calcule uniquement à partir de données comptables : encaissements et décaissements constatés. Par données comptables, il faut traduire données enregistrées dans les comptes bancaires. (= données issues de la table opération)

Règle 2: La marge brute associée à une réservation s’exprime toujours en euro, même si les encaissements et les décaissements se font en dollar ou autres monnaies.

Pratique 3: Dans le cadre de réservation faisant intervenir d’autres monnaies que l’euro, il est d'usage qu'un seul taux de change (par monnaie) soit pris en compte, indépendamment de toutes considérations (date d’encaissement réel, de décaissement, etc) . Ce taux est choisi par la personne en charge de la gestion.

Règles 4: Principe de précaution et afin de garantir la pérennité de la solution, l'application doit pouvoir s’affranchir de la pratique n°3! En d’autres termes, le système doit être capable de calculer la marge d’un dossier en considérant des taux de change différents.

Règle 5: Un taux de change se détermine en fonction de sa date de référence. Autrement dit, on ne saisit pas un taux de change, on saisit une date. En fonctionne de cette date, le système doit connaître le taux de change à appliquer.

Le commercial perçoit une commission. Cette commission se calcule à partir de la Marge brute selon la formule suivante :
commission = (marge brute) x (taux de commission).

Ce taux est jusqu'à présent toujours de 50%, mais rien ne dit qu'il en sera toujours à l'avenir pour tous les dossiers.

En début de mois (généralement), la gestion procède au paiement des commerciaux.Chaque commercial perçoit alors un montant global qui est la somme de ses commissions. Ce paiement est enregistré dans les comptes bancaires. Cela fait donc l'objet d'une opération bancaire.

La marge s'exprimant en euro, la commission s'exprime de facto en euro. Si le commercial est américain et doit être payé en dollar, la commission doit être valorisée en dollar. C'est la personne en charge de la gestion qui choisi la date du taux de change.

Un petit exemple pour bien comprendre :

Un client américain, mr OBAMA loue un bateau en Espagne. Le client paye sa réservation en dollar sur un compte (en dollar). Le client fait 3 paiements de 3000 $. Le ou les fournisseurs qui sont impliqués dans ce dossier seront eux payer en euro (ici un seul fournisseur pour simplifier).
Prenons le cas le plus «compliqué»:
Pour Y raisons, il est décidé de valoriser les paiements du client comme suit:

paiement du 1er janvier au taux du 2 janvier 2009. A cette date la parité euro_dollar était de 1.25, donc 1/1.25 euro (=0,8) pour 1 dollar

paiement du 1er août au taux du jour. A cette date, la parité euro_dollar était de 1.3456 soit 1 dollar pour 0,7432 euro

paiement du 29 octobre au taux du 1er décembre 2009. A cette date, la parité euro_dollar était de 1.1123 soit 1 dollar pour 0,899 euro



Sachant que pour cette réservation, le taux de commission est de 50%, la commission est de 1213,20 €. Le commercial étant américain, on valorise sa commission en dollar. Il est décidé de prendre la date du 1er décembre 2009 pour le taux de change.
Commission = 1213,20 x 1.1123 = 1349,44 $

Dans la pratique, si on reprend l'exemple ci-dessus, on valorise tous les paiements au même taux de change. Idem pour le taux de change retenu pour le calcul de la commission. C'est le taux de change en date du paiement des commissions qui est pris en compte. Ici la date du 1 décembre 2009.



Commission = (3131,33 x 0,5) x 1.1123 = 1774,85 $


Mon analyse pour le MCD.
Quelles sont les entités et relations associées à définir et qui ne sont pas encore représentées dans le MCD actuel ?

La notion de marge brute me pose problème. C'est un champ calculé. La marge prend en considération une ou plusieurs opérations bancaires, chaque opération bancaire pouvant être associée à un taux de change (date de taux de change). Initialement, je me suis dit, c'est simple, il suffit de rajouter l'attribut date_taux_change dans la table opération. Seulement voilà, cet attribut ne concerne que des opérations résultant d'une réservation, opérations appartenant en plus à un compte bancaire libellé dans une autre devise que l'euro. Je vais avoir beaucoup de NULL pour cet attribut.
J'ai donc pensé à la notion d'héritage/spécialisation.
Une opération bancaire servant au calcul de marge , n'est ni plus ni moins qu'une opération bancaire, elle en a tout les attributs, mais en plus elle a l'attribut date_taux_change.

Oui mais, cela sous entend qu'on utilise une date de taux de change pour chaque opération, or en pratique ce n'est pas vrai. En effet, généralement, on utilise le même taux de change (date) pour toutes les opérations.
Doit-on prendre on considération cet état de fait, sachant que dans le cas où l'on gère différents taux de change, il sera toujours possible de calculer la marge. Toutes les opérations bancaires auront la même date de taux de change.
N'y aurait-il pas là une notion d'exclusion à modéliser ? Le calcul de la marge fait intervenir une date de taux de change ou s'appuie sur plusieurs de taux de change. Nous avons donc une notion de modalité de calcul de Marge avec Taux_Change_Unique ou Taux_Change_Multiple.
Cependant, la notion de modalité de calcul n'a de sens que pour des comptes bancaires dont la devise n'est pas l'euro. Dois-t-on le modéliser ?
En faisant abstraction cette notion de marge, au mieux (à priori) , on peut dire qu'une commission se rattache à une réservation avec comme cardinalité (1,1) et (1,1). Cette entité «commission» ayant pour attribut le tx_de_commission. Il existerait aussi une relation entre Commission et Commercial.
[commission]----1,1-----(concerne)----0,n-------[commercial]. Tout ceci ne me convainc guère ...

J'ai beau «brouillonner» plusieurs nouvelles entités, relations, j'aboutis à rien .
J'ai même étudié la piste de mettre des attributs d'association comme présentée ici. Je verrai bien en effet une relation se_calcule entre une entité [Marge_brute] et l'entité [opération] avec comme attribut d'association date_taux_change. Ce serait mon premier attribut d'association! J'en ai pas encore dans mon MCD!

Bref, j'ai du mal à faire la différence entre ce qui relève purement du MCD voir MLD et des requêtes qui seront mises en œuvre.

Pour ce qui est du point 2 ( La gestion des emails associés au paiement des échéances), on verra cela après !

En espérant ne pas vous avoir perdu en cours de route,

Bien à vous
Tavar

[JPhi33]

Bonjour Tavar,

Le taux de change n'est pas une donnée "métier". On peut considérer qu'il s'agit d'un paramètre du système. Les paramètres, dont le taux de TVA est l'exemple typique, sont modélisés à part car :
- leur évolution ne dépend pas du métier de l'entreprise
- ils sont uniquement utilisés pour des calculs.

Dans le cas très général où l'on n'a pas besoin de savoir à quelle date le paramètre change, on enregistre simplement sa valeur dans une table dédiée et chaque nouvelle valeur vient écraser l'ancienne.

Exemple :

PARAMETRE(id_param, valeur_param)

certains font même plus simple s'il n'ont qu'un paramètre (ici, le taux de TVA) :

TVA(id, taux_tva)

Ton besoin, tel que je le comprends, est de disposer du taux de change pour plusieurs devises à chaque date d'opération et surtout a posteriori. C'est-à-dire que le jour du calcul de la marge brute, à la clôture du dossier, quantités d'opérations auront été enregistrées à des dates antérieures. Ce sont ces dates qu'il faudra prendre en considération lors du calcul de la marge brute. Pour éviter de devoir stocker dans chaque opération le taux de change à la date de l'opération, avec les inconvénients que tu as décrits, le mieux est de modéliser une table paramètre pour le taux de change.

La modélisation peut être :

TAUX_CHANGE(date, code_devise, parité_euro)

La gestion de la table peut se faire de deux façons.

Le taux de change change () tous les jours. Une première manière de gérer cette table consiste à stocker chaque jour le taux de change de toutes les devises dont tu as besoin. Lors du calcul, il suffit d'accéder à la table avec la date de l'opération et la devise pour récupérer le taux à cette date. C'est ce que j'appellerais la manière bestiale !

Pour éviter de stocker des lignes inutiles, il est préférable d'enregistrer le taux de change au moment de la création d'une opération qui nécessitera ce taux de change lors du calcul de la marge brute. Quand on crée l'opération, on a la devise et la date ; il faut récupérer (je ne sais trop comment) le taux de change du jour et insérer une ligne dans la table.

Exemple de M. OBAMA

Code :

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

1
2
3
4
5
6
Date       Devise Taux
---------- ------ ------
15/01/2009 USD    1,2500
01/08/2009 USD    1,3456
29/10/2009 USD    1,1123

Evidemment, les taux du 15 janvier et du 29 octobre sont faux puisque dans ton exemple ce sont les taux du 2 janvier et du 1er décembre, respectivement, mais c'est pour l'exemple.
J'ai choisi un code devise alphanumérique mais il existe aussi un code devise numérique. Les deux sont définis par la norme ISO 4217.

[tavarlindar]

Bonjour JPhi33,
Tu as bien compris le besoin. Merci pour l’iso des devises, je ne connaissais pas. L’idée de remplir la table taux_change lorsqu’on créer une opération me plait aussi.
Par contre, je suis surpris par ta proposition dans la mesure où :
1) J’ai déjà une table taux de change.
2) Comme tu le dis

Lors du calcul, il suffit d'accéder à la table avec la date de l'opération et la devise pour récupérer le taux à cette date. C'est ce que j'appellerais la manière bestiale !

.
Si en pratique, il n’y a pas raison de faire autrement, on peut très bien imaginer de que l’utilisateur souhaite faire différemment à savoir pour une date d’opération donnée, prendre une date de change différente. Dans ce cas, comment avoir l’information ?
Nulle part, on stocke en base la date de change souhaitée pour une ligne d’opération. Nulle part on stocke le taux de commission.

Pour l’heure, si ce forum n’existait pas, je partirai sur la solution suivante :
Rajout de 3 attributs à la table réservation :

- taux_change_global , champ de type enum (spécificité je crois de mYSQL) avec 2 valeurs possibles Y ou N. Si Taux_change_global = Y, alors on sait que pour toutes lignes d’opérations associées à cette réservation, on doit prendre en considération qu’une date de taux de change, date stockée dans le champ suivant.
- date_change_globale : date de taux de change à utiliser. NULL par défaut. date_change_globale n’a de sens que si Taux_change_global = Y. Dans le cas où Taux_change_global = N, date_change_globale n’a pas de signification.
- Taux de commission (puisque à 1 réservation donnée correspond un et un seul taux de commission)

Création d’une table enfant de la table opération : operation_change. -> Héritage :
operation_change : avec comme clé primaire id_operation et en même temps clé étrangère (table operation) et comme attribut date_change.
Maintenant, est-ce la bonne voir meilleure solution ?


de retour de vacances ...

Tavar

[JPhi33]

Bonjour Tavar,

1) J’ai déjà une table taux de change.

Désolé, je ne l'avais vue .
Cette table convient mais si un jour tu as besoin d'une nouvelle devise, il faut modifier la structure de la table. Avec celle que je te propose, il n'y a rien à faire, si ce n'est d'ajouter les données.

2) Comme tu le dis .

Lors du calcul, il suffit d'accéder à la table avec la date de l'opération et la devise pour récupérer le taux à cette date. C'est ce que j'appellerais la manière bestiale !

La manière bestiale concerne le nombre de lignes qu'il faut enregistrer dans la table, pas la manière de les récupérer. La table étant identique dans les deux modes de gestion, la méthode d'accès l'est aussi : avec la clé {date, devise}.

on peut très bien imaginer de que l’utilisateur souhaite faire différemment à savoir pour une date d’opération donnée, prendre une date de change différente.

Ceci est une nouveauté... que je n'ai, par conséquent, pas pu prendre en compte. Quoi qu'il en soit, l'application ne peut pas être basée sur des spéculations ; il faut vérifier la validité de cette hypothèse. L'utilisateur peut-il oui ou non choisir une date de taux de change pour chaque opération ? Est-ce seulement souhaitable ?

Dans ce cas, comment avoir l’information ?
Nulle part, on stocke en base la date de change souhaitée pour une ligne d’opération.

J'ai bêtement supposé que le taux de change à prendre en compte pour l'opération est celui de la date de valeur (date_valeur_op).

Nulle part on stocke le taux de commission.

Je ne me suis pas encore préoccupé du taux de commission... mais parlons-en.

Ce taux est jusqu'à présent toujours de 50%

Ce taux a toutes les caractéristiques d'un paramètre du S.I. :
- il s'applique à tous les dossiers et tous les commerciaux (il en est donc indépendant)
- il peut varier dans le temps (sinon ce serait une constante, donc inopportune dans le MCD)

TAUX_COMMISSION (id, taux_commission)

Si on veut garder une trace des différents taux de commission :

TAUX_COMMISSION (date, taux_commission)

mais rien ne dit qu'il en sera toujours à l'avenir pour tous les dossiers

Si le taux de commission dépend du dossier, alors c'est une propriété de l'entité Réservation (à supposer que dossier = réservation).



Pour ce qui est de la solution avec les 3 propriétés supplémentaires dans l'entité Réservation, elle va truffer de NULL la table Réservation correspondante, ce qui n'est pas bon.

[tavarlindar]

Bonjour JPhi33,

Effectivement, la structure de la table taux de change que tu proposes est, sans doute, plus pertinente. Elle est plus souple que celle que j’ai mise en place. Je n’ai pas encore pris de décision (il faut que je modifie l’application existante) , mais je pense (à 99,5%) adopter ta solution.
Tu as choisi comme identifiant monnaie (devise) le Code alphabétique. Je trouve cela plus pratique que le Code numérique. Cela permet une lecture directe.

Je suis surpris par ton commentaire :

Ceci est une nouveauté... que je n'ai, par conséquent, pas pu prendre en compte. Quoi qu'il en soit, l'application ne peut pas être basée sur des spéculations ; il faut vérifier la validité de cette hypothèse. L'utilisateur peut-il oui ou non choisir une date de taux de change pour chaque opération ? Est-ce seulement souhaitable ?

Ceci n’est pas une nouveauté ! Cela fait clairement partie du cahier des charges (règle 4) suivi d’un exemple. Afin de parer à toutes éventualités, je souhaite avoir la possibilité d’affecter le taux de change que je veux quelque soit la ligne d’opération. Donc OUI l’utilisateur doit pouvoir choisir une date de taux de change pour chaque opération.
En pratique, si tel est le cas (utilisation de différents taux de change) , la logique veut que l’on choisisse le taux de change à la même date que la date de valeur de l’opération. Tu as donc intelligemment supposé que le taux de change à prendre en compte pour l'opération est celui de la date de valeur (date_valeur_op). Maintenant, je préfère prévenir que guérir, je préfère que la structure de la base de données (et donc le mcd/mld sous-jaccent) soit capable de répondre à tous les cas de figure (ou presque). Si l’application permet de définir le taux que l’on souhaite pour une opération bancaire donnée, je suis (presque) assuré de ne pas avoir à redévelopper par la suite.

Dans la vie des sociétés, il est fréquent que les règles changent. Je n’ai pas fini de lire l’ensemble du sujet consacré aux règles pour concevoir un bon MCD, mais si je devais à mon humble niveau en édicter une, je dirai : un bon MCD doit anticiper les changements.
Pour cette même raison, il faut considérer le taux de commission comme dépendant du dossier, donc comme une propriété de l'entité Réservation.

Concernant les 3 propriétés supplémentaires dans l'entité Réservation, je suis d’accord, il y aura beaucoup de NULL, mais je ne vois pas comment faire autrement.

Voilà,

Au plaisir de lire tes conseils,

Tavar

[JPhi33]

Bonjour Tavar,

Je suis surpris par ton commentaire :

Ceci est une nouveauté... que je n'ai, par conséquent, pas pu prendre en compte. Quoi qu'il en soit, l'application ne peut pas être basée sur des spéculations ; il faut vérifier la validité de cette hypothèse. L'utilisateur peut-il oui ou non choisir une date de taux de change pour chaque opération ? Est-ce seulement souhaitable ?

Ceci n’est pas une nouveauté ! Cela fait clairement partie du cahier des charges (règle 4) suivi d’un exemple.

Non, la règle 4 dit seulement que l'application doit pouvoir prendre en compte différentes dates de taux de change (i.e. une par opération) dans une même réservation, contrairement à la pratique 3 qui ne prend en compte qu'un seul taux de change par réservation. La règle 4 ne dit pas que l'utilisateur peut choisir n'importe quelle date comme date de taux de change (d'où mon étonnement).



Bref, supposons que cette règle soit valide. Tu as déjà avancé la solution dans ton premier message (sans toutefois aller jusqu'au bout).


L'entité spécialisée "operation_non_euro" contient la date de taux de change pour les opérations la nécessitant (et seulement ces opérations-là).

Quelles sont les opérations nécessitant cette date ? Je serais tenté de dire (hypothèse 1) que ce sont les opérations liées à un compte en devise non Euro pour lesquelles l'utilisateur a choisi une date différente de "date_valeur_op". Dans cette hypothèse, une ligne de cette table ne serait donc créée que si les 2 conditions sont réunies :

C1. l'opération est liée à un compte non Euro
C2. date_taux_change est différente de date_valeur_op

Ce qui réduit le contenu de cette table aux lignes réellement nécessaires.

Pour éviter de se poser des questions, on peut systématiquement récupérer la date de taux de change par jointure externe. La date n'est pas valorisée si, dans la table "operation_non_euro", il n'existe pas de ligne correspondant à l'opération sur laquelle porte la requête.

On peut aussi décider (hypothèse 2) de créer une ligne de cette table dès lors que l'opération est liée à un compte en devise non Euro. Dans cette hypothèse, on ne tient compte que de C1. Pour savoir quelle est la date du taux de change, date_valeur_op ou date_taux_change, il faut préalablement vérifier la devise du compte.

Ce sont les règles fournies par les utilisateurs qui décideront du mode de gestion de cette table.

Pour cette même raison, il faut considérer le taux de commission comme dépendant du dossier, donc comme une propriété de l'entité Réservation.

C'est cohérent. Chaque réservation aura donc son propre taux de commission.

Concernant les 3 propriétés supplémentaires dans l'entité Réservation, je suis d’accord, il y aura beaucoup de NULL, mais je ne vois pas comment faire autrement.

J'ai répondu un peu à la va-vite sur ce point car le taux de commission est valorisé pour chaque réservation comme indiqué ci-dessus (il n'y aura donc pas de NULL).

Pour ce qui est des deux autres propriétés, je ne vois pas leur intérêt avec l'apport de l'entité "operation_non_euro" sauf s'il s'agit du taux de change à appliquer pour le calcul de la commission d'un commercial non payé en Euros. Mais, à ce que j'ai compris, cette commission fait l'objet d'une opération bancaire. Si cette opération est enregistrée dans la table "operation", on aura une date de taux de change...
Il faudrait éclaircir ce point.