Discussion :

[regisyves]

bonjour , J'ai deux questions :
1 ) la prmière
j'ai deux tables :
facture(numerofact , montantHT facture , montant TVA , montantTTC )
et l'entité règlement(numero_reglement,date_reglement ) .
Chacune de ses entités a pour cardinalité (1,1 ) .
Je vois sur les forums que dans ce cas , on fusionne les deux tables pour former une seule . Mais , on m'a aussi appris qu'on peut garder ces deux entités et la table la plus ancienne porte maintenant comme cardinalité(0,1 ) .
Puisque je n 'ai pas vu cette remarque sur les forums , alors ça m'inquiète . ils disent tous qu'il faut fusionner les deux entités .
1) Est ce faux ou vrai ?
2) est ce que je suis obligé de mettre les propriétés montantHT facture , montant TVA , montantTTC puisqu'elles sont calculées

[_Xavier_]

1) Est ce faux ou vrai ?

Je vais peut être te dire une bétise mais à ta place je fusionnerais les deux tables (je préfère avoir deux colones NULL dans une table que de recourir tout le temps aux jointures pour consulter mes factures). C'est une implémentation du concept d'héritage (entre facture et facture_réglée ) que le modèle relationnel gère à peine.

2) est ce que je suis obligé de mettre les propriétés montantHT facture , montant TVA , montantTTC puisqu'elles sont calculées

Effectivement la norme interdit la présence de données calculées dans les tables. Ta TVA tu la gères comment, une table à part ?

[CinePhil]

Tu as donc le schéma suivant :
Facture -1,1----Payer----1,1- Règlement

En français, ça donne :
Une facture est payée par un seul règlement et un règlement paye une seule facture.

Autrement dit, dès que tu émets une facture, le paiement est aussitôt enregistré !
Dépose un brevet sur le système qui entraîne le règlement aussi rapide des factures émises, tu vas faire fortune !

Le schéma devrait donc plutôt être :
Facture -0,1----Payer----1,1- Règlement

Allons plus loin dans la réflexion...
N'y a t-il pas des factures payées en plusieurs règlements ?
N'y a t-il pas des règlements qui payent plusieurs factures ?

Ne dois-tu pas enregistrer le type de règlement (chèque, virement, crédit, traite, espèces...) ?

Maintenant comme l'a dit Xavier, et surtout compte-tenu du fait que ta table des règlement ne contient que la date du règlement comme donnée significative, une colonne dans la table des factures suffirait. Elle serait par défaut à NULL.

Venons-en à la seconde question :

2) est ce que je suis obligé de mettre les propriétés montantHT facture , montant TVA , montantTTC puisqu'elles sont calculées

Comme l'a dit Xavier, en principe on ne met pas de colonnes calculées dans les tables. Cependant, le cas de ces colonnes là est un peu particulier.
Comment sont-elles calculées ?
montantHT = SUM(PrixUnitaire x Quantité) ?
PrixUnitaire et Quantité viennent d'une autre table des lignes de commande ?
Ou bien PrixUnitaire vient directement de la table des articles ?

Soit un article à 50 euros. J'en vends 100 à un client et j'enregistre une commande de 5000 euros que je facture ensuite 5000 euros.
Quelques temps plus tard, je change le prix de l'article. Conséquence si toutes mes données calculées relatives aux prix n'ont pas été enregistrées au moment de la vente et de la facturation : Toutes les commandes et mes factures contenant cet article sont modifiées !
Ajoutons que lorsque je vends quelque chose, je peux faire une remise par rapport au prix de base ou sur le montant global. Que s'il y a eu un problème lors de la production, je peux accorder aussi en compensation une remise au moment de la facturation.
Bref ! Le montant hors taxe et le montant de la TVA, bien qu'ils soient effectivement calculés par le logiciel, doivent être enregistrés en BDD.
Par contre, montantTTC = montantHT + montantTVA. Le montant TTC n'a pas besoin d'être enregistré.

[fsmrel]

Je vais peut être te dire une bétise mais à ta place je fusionnerais les deux tables (je préfère avoir deux colones NULL dans une table que de recourir tout le temps aux jointures pour consulter mes factures). C'est une implémentation du concept d'héritage (entre facture et facture_réglée ) que le modèle relationnel gère à peine.

C’est effectivement une bêtise.

1) L’héritage n’a rien à voir avec le problème posé.

2) Le Modèle Relationnel de Données (ou Théorie relationnelle) gère parfaitement l’héritage. Je vous renvoie aux ouvrages du spécialiste mondial, Chris Date, voyez par exemple The Inheritance Model.

3) NULL met en péril la validité des bases de données, inhibe l’optimiseur des SGBDR etc. Je vous renvoie aux mêmes ouvrages, par exemple « An Introduction to Database Systems » (8e édition).

4) Vous oubliez qu’il y a des tables de base (en l’occurrence Facture et Reglement) et des tables virtuelles qu’on appelle des vues. Il suffit de créer une vue FactRglt correspondant à la jointure naturelle des deux tables de base et vous n’aurez plus besoin de « recourir tout le temps aux jointures ».

[_Xavier_]

1) L’héritage n’a rien à voir avec le problème posé.

je vois bien une relation d'héritage entre facture (numerofact , montantHT facture , montant TVA , montantTTC ) et reglement (numero_reglement,date_reglement). la première entité est une facture tout court et la seconde une facture réglée.

2) Le Modèle Relationnel de Données (ou Théorie relationnelle) gère parfaitement l’héritage.

Je fais allusion à l'implémentation "pratique" de l'héritage qui n'a à mon sens aucune différence avec une jointure normale.

3) NULL met en péril la validité des bases de données, inhibe l’optimiseur des SGBDR etc.

Ca revient à dire que le modèle relationnel n'est pas adapté à la pratique, où l'on a bien le droit d'avoir dans différentes lignes d'une même table plusieurs colonnes à NULL, sans même que ce soit pour contourner un problème d'implémentation.

4) Vous oubliez qu’il y a des tables de base (en l’occurrence Facture et Reglement) et des tables virtuelles qu’on appelle des vues. Il suffit de créer une vue FactRglt correspondant à la jointure naturelle des deux tables de base et vous n’aurez plus besoin de « recourir tout le temps aux jointures ».

A-t-on les mêmes performances quand on accède à une seule table que quand on accède à une table virtuelle qui regroupe plusieurs tables ?

Je te signale que dans la pratique je n'utilise que des vues pour éviter entre autre d'intervenir au niveau applicatif en cas de modification d'une table.

[fsmrel]

Bonsoir,

je vois bien une relation d'héritage entre facture (numerofact , montantHT facture , montant TVA , montantTTC ) et reglement (numero_reglement,date_reglement). la première entité est une facture tout court et la seconde une facture réglée.

Au niveau conceptuel, c'est d’accord, l’héritage peut être utilisé pour spécialiser l’entité-type Facture en Facture_Reglee.

En ce qui concerne le Modèle Relationnel de Données, je suis évidemment toujours d’accord, mais la donne est différente, car par héritage, on entend héritage de type. Exemple

Code :

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

1
2
3
4
5
6
7
8
9
TYPE Facture
         POSSREP {NumeroFact   INTEGER, 
                  MontantHT    INTEGER, 
                  MontantTVA   INTEGER) ;

TYPE Facture_Reglee
      IS Facture
         POSSREP {NumeroReglement  INTEGER, 
                  DateReglement    DATE} ;

Ces types seront utilisés pour définir les opérateurs qui leurs sont associés, ainsi que les variables relationnelles (tables au niveau SQL pour faire court).

La norme SQL permet elle aussi que nous définissions nos propres types (User-Defined Types ou UDT).

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE TYPE Facture
       AS {NumeroFact   INTEGER, 
           MontantHT    INTEGER, 
           MontantTVA   INTEGER)
           NOT FINAL ;

CREATE TYPE Facture_Reglee UNDER Facture
       AS {NumeroReglement  INTEGER,
           DateReglement    DATE}
           NOT FINAL ;

La norme SQL permet aussi de définir des surtables et des sous-tables :

Code :

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

1
2
3
4
5
CREATE TABLE Facture OF Facture
       (REF IS Facture_OID SYSTEM GENERATED,
        PRIMARY KEY (NumeroFact) ;   

CREATE TABLE Facture_Reglee OF Facture_Reglee UNDER Facture ;

Je fais allusion à l'implémentation "pratique" de l'héritage qui n'a à mon sens aucune différence avec une jointure normale.

Vous ne faites donc pas référence au Modèle relationnel ou à la norme SQL. Il est vrai que les différents SGBDR ne sont pas nécessairement calés sur la norme et on peut être amené à l’implémentation « pratique », palliative que vous évoquez, procéder comme du temps où les concepts évoqués n’étaient pas pris en compte, c'est-à-dire en utilisant une « vue de jointure ».

On peut illustrer cela à l’intention de regisyves. Au préalable, j’ajoute l’attribut NumeroFact pour la table Facture_Reglee, en supposant que le numéro de règlement n’est pas un numéro de facture (hypothèse dont j’ai du reste tenu compte quand j’ai parlé des types).

Définition des tables de base :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Create table Facture (
    NumeroFact   INT    Not null, 
    MontantHT    INT    Not null,   
    MontantTVA   INT    Not null,
  Primary key (NumeroFact)) ;
 
Create table Facture_Reglee (
    NumeroFact          INT    Not null, 
    Numero_Reglement    INT    Not null,   
    DateReglement       DATE   Not null,
  Primary key (NumeroFact),
  Unique (Numero_Reglement),
  Foreign key (NumeroFact) References Facture ) ;

Définition de la vue (à laquelle on pourrait du reste envisager d'ajouter le montant TTC avec son calcul) :

Code SQL :

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

1
2
3
4
5
6
CREATE VIEW Facture_Reglee_V  
   (NumeroFact, MontantHT, MontantTVA, NumeroReglement, DateReglement)
  AS SELECT x.NumeroFact, x.MontantHT, x.MontantTVA, 
            y.Numero_Reglement, y.DateReglement
     FROM   Facture AS x INNER JOIN Facture_Reglee AS y
                ON x.NumeroFact = y.NumeroFact ;

En outre, comme les SGBD acceptent de façon générale les triggers il sera possible de transformer les opérations de mise à jour, quand par exemple le SGBD ne tolère pas que ces opérations portent sur une jointure.

Ca revient à dire que le modèle relationnel n'est pas adapté à la pratique, où l'on a bien le droit d'avoir dans différentes lignes d'une même table plusieurs colonnes à NULL, sans même que ce soit pour contourner un problème d'implémentation.

Au contraire, « Theory is practical », et le Modèle Relationnel de Données apporte énormément quand on commence à le connaître et si l’on veut bien prendre un peu de recul. Par exemple, toute opération de mise à jour sur une table est possible, même si cette table est virtuelle, c'est-à-dire si c’est une vue (sauf à violer des contraintes d’intégrité). Pratique, non ? En revanche, si vous pouviez m’expliquer comment la norme SQL traite du sujet, Foundations page 355 « Query expressions » : <query expression> QE1 is updatable, ... je vous serais reconnaissant, je n'y ai rien compris...

SQL (Sorry Query Language) n’est jamais qu’un avatar du Modèle relationnel, un monstre qui a un bon fond et qui s’améliore avec le temps, mais qui traîne des infirmités qui à première vue peuvent paraître parfois séduisantes, telles que NULL, qui en tout cas ne viennent pas handicaper le Modèle Relationnel. Par exemple, j’avais mentionné les optimiseurs des SGBDR, en disant que NULL les inhibaient : un optimiseur met à profit ce que les théoriciens du Modèle relationnel on pu lui apporter, mais quel que soit le SGBD, DB2 ORACLE, SQL Server etc., ils sont tous confrontés aux problèmes que leur pose NULL. Chris Date vous expliquera que mathématiquement on n’y peut rien. Certes, nous ne le voyons pas, cela se passe sous le capot mais la réalité est là. Je vous renvoie aux chapitres 18 « Optimization » et 19 « Missing Information » de An introduction to Databases Systems.

Cela dit, on peut définir des tables SQL sans avoir à utiliser NULL, mais cela demande un certain savoir-faire au niveau conceptuel, puisque c’est à ce niveau que finalement tout se joue, le MLD n'étant que la traduction du MCD.

A-t-on les mêmes performances quand on accède à une seule table et quand on accède à une table virtuelle qui regroupe plusieurs tables ?

La réponse n’est évidemment pas binaire et l’intuition est bien souvent prise en défaut à ce sujet. Pour avoir une réponse objective, un prototypage des performances s’impose. Mais la performance pour la performance n’est pas tout, il faut tenir compte des désagréments qui résultent de tout mettre dans une seule table : redondances délicates à maintenir, difficultés des opérations de mise à jour, et autres impedimenta bien connus.

Maintenant, considérez une hypothétique table des membres de Developpez.com et une table des messages dont ils sont les auteurs.

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
CREATE TABLE Membre
(    MbrId            INTEGER        NOT NULL
   , Pseudonyme       VARCHAR(16)    NOT NULL
   , DateInscription  DATE           NOT NULL
   , Localisation     VARCHAR(48)    NOT NULL
   , AdrCourriel      VARCHAR(48)    NOT NULL
 , CONSTRAINT MbrPk PRIMARY KEY (MbrId)
 , CONSTRAINT CK2 UNIQUE (Pseudonyme)
 , CONSTRAINT CK3 UNIQUE (AdrCourriel)) ;
 
CREATE TABLE Message
(    MbrId          INTEGER        NOT NULL
   , MessageId      INTEGER        NOT NULL
   , MessageDate    TIMESTAMP      NOT NULL
   , MessageTexte   VARCHAR(MAX)   NOT NULL
   , Forum          CHAR(8)        NOT NULL
 , CONSTRAINT MessPk PRIMARY KEY (MbrId, MessageId))
 , CONSTRAINT MessAk FOREIGN KEY (MbrId)
                     References Membre (MbrId) On Delete Cascade ;

De ces deux tables, n’en faisons qu’une. Comme certains membres de DVP n’ont jamais été les auteurs du moindre message, et si je vous suivais, certaines colonnes pourraient être marquées NULL, mais ça n’est pas toujours possible (colonne MessId). Utilisons-donc des valeurs par défaut.

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE Membre
(    MbrId            INTEGER        NOT NULL
   , Pseudo           VARCHAR(16)    NOT NULL
   , DateInscr        DATE           NOT NULL
   , Localisation     VARCHAR(48)    NOT NULL
   , AdrCourriel      VARCHAR(48)    NOT NULL
   , MessId           INTEGER        NOT NULL DEFAULT ...,
   , MessDate         TIMESTAMP      NOT NULL DEFAULT ...,
   , MessTexte        VARCHAR(MAX)   NOT NULL DEFAULT ...,
   , Forum            CHAR(8)        NOT NULL DEFAULT ...,
 , CONSTRAINT MbrPk PRIMARY KEY (MbrId, MessId)
 , CONSTRAINT CK2 UNIQUE (Pseudo, MessId)
 , CONSTRAINT CK3 UNIQUE (AdrCourriel, MessId)) ;

On sait que certains membres sont plus bavards que d’autres et sont à l’origine de milliers de messages. Ainsi donc, pour chacun de leurs messages, on devra valoriser les colonnes MbrId, Pseudo, DateInscr, Localisation et AdrCourriel. Apprendre dix mille fois que Marc s’est inscrit en février de je ne sais plus quelle année ne me paraît pas bien fameux (il va y en avoir des DISTINCT dans les SELECT, donc des tris...) et j’en reviendrai prudemment à deux tables, déjà par par souci d’économie en temps et en espace.

En effet, en supposant que le site comporte 5 000 inscrits, que 100 000 messages aient été postés, les deux premières tables (comprimées à 90%) consomment 24 MO (DB2 for z/OS), ce à quoi il faut ajouter 2,2 MO pour les index. Si on en fait une seule table (comprimée à 90%), l'affaire se corse, car celle-ci consomme 25,5 MO, ce à quoi il faut ajouter 11,4 MO pour les index, sachant par ailleurs que ceux-ci passent de 2 à 3 niveaux, ce qui ne va pas dans le sens de l’amélioration des performances. L’embonpoint pose problème. La durée des mises à jour va se dégrader (supposons en effet que l'homme au 10 000 messages change de localisation ou d'adresse de courriel, il va y avoir de nombreuses lectures/écritures pour le tablespace, mais aussi pour un index), les risques de contention augmenter, la durée des tâches de service (journalisation, réorganisation, sauvegarde, ...) va croître en proportion des volumes en jeu. Je n’insiste pas.