Discussion :

[fsmrel]

l'entité-type MAIN-ADRESSE a pour clé primaire {actor_id, adresse_id} avec une contrainte d'unicité sur actor_id

D’accord. Je suppose que le grand « U » en rouge est synonyme d’unicité (je croyais au départ que c’était de la pub ^^). Cela dit, si MAIN-ADRESSE comporte nécessairement la clé étrangère {actor_id, adresse_id}, alors au stade SQL (l’outil de modélisation n’ayant plus son mot à dire), et au nom du principe d’essentialité, il suffit de définir {actor_id} comme clé primaire et seule clé candidate (cf. le message #76). En passant, c’est aussi valable pour les tables RESPONSABLE, MAIN-PHONE, MAIN-EMAIL.

Pourriez-vous fournir le code SQL du message #64, mais mis à jour ?

[fsmrel]

ces principes d'historisation me font tiquer quand à la manière de récupérer la bonne info en production.
Comment faites vous? Avez vous un exemple de requête SQL simple?
Par exemple la jointure entre un item_cde et son taux de tva en tenant compte de la date de la commande. En effet, on a le cas ou le taux est déjà présent dans l'historique, et le cas ou il ne l'est pas.

Je pense avoir montré (cf. message #80) que récupérer « la bonne information en production » ne présente aucune difficulté avec la structure que j’ai proposée. Je vous laisse le soin de montrer qu’il en va de même avec votre propre structure (cf. message #73). J’attire quand même votre attention sur le fait que dans votre cas, retrouver un intervalle (date de début d’application, date de fin d’application) pour un taux donné n’est pas trivial (utilisation des auto-jointures, fonctions MIN, MAX, donc GROUP BY et autres joyeusetés ou cauchemars de l’agrégation des données) est beaucoup moins performant (même si la rapidité d’exécution des requêtes n’est pas l’objet, encore que...)

Votre avis ?

[frm013]

Bonsoir François,

Navré pour le délai, j'étais à l'extérieur toute la journée.

Je pense avoir montré (cf. message #80) que récupérer « la bonne information en production » ne présente aucune difficulté avec la structure que j’ai proposée

Je suis entièrement d'accord, et nul n'était besoin de me le prouver, je suis là pour me perfectionner et je ne pensais même pas à discuter. D'ailleurs je vous remercie pour cet exemple très détaillé, cela m'a redonné les idées claires, j'étais assez perturbé par votre exemple avec les adresses, mon esprit s'est bloqué.

Comme je vous le disait, je me posait justement des questions sur l'implémentation des Selects avec ma structure.

même si la rapidité d’exécution des requêtes n’est pas l’objet, encore que...

D'accord avec vous, vous l'aurez sans doute remarqué, j'essaie de prendre en compte la performance le plus tôt possible dans l'analyse. Pour ce qui est de votre implémentation SQL je doute que cela impact négativement les performances sur une petite table comme la tva. En tous cas avec la mienne c'était partit pour la galère.

Donc, si j'ai bien compris, il faudrait faire de même qu'avec la tva avec les prix et les adresses. Ces tables-ci (piece et adresse ) comportent plusieurs centaines de milliers de lignes.

Pourriez-vous fournir le code SQL du message #64, mais mis à jour ?

Ok, le temps de le re-générer et de le vérifier et je le poste, d'ici demain matin sans doute.

Encore merci,
Bonne soirée,
François

[frm013]

Bonjour Francois,

Voici le diagramme partiel des coordonnées corrigé selon vos conseils:



Et le code SQL généré :

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
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
CREATE TABLE org_type (
  org_type_id  SERIAL NOT NULL, 
  lib         varchar(30) NOT NULL, 
  PRIMARY KEY (org_type_id));
CREATE TABLE demolition (
  magasin_id int4 NOT NULL, 
  agrement   varchar(20) NOT NULL, 
  qualicert  varchar(20) DEFAULT '' NOT NULL, 
  PRIMARY KEY (magasin_id));
CREATE TABLE directeur (
  entreprise_id int4 NOT NULL, 
  employe_id    int4 NOT NULL, 
  magasin_id    int4 NOT NULL, 
  PRIMARY KEY (entreprise_id, 
  employe_id), 
  CONSTRAINT directeur_ak1 
    UNIQUE (entreprise_id, magasin_id));
CREATE TABLE employe_mag (
  entreprise_id int4 NOT NULL, 
  employe_id    int4 NOT NULL, 
  magasin_id    int4 NOT NULL, 
  PRIMARY KEY (entreprise_id, 
  employe_id), 
  CONSTRAINT emp_mag_ak_1 
    UNIQUE (entreprise_id, employe_id, magasin_id));
CREATE TABLE responsable (
  employe_id int4 NOT NULL, 
  PRIMARY KEY (employe_id));
CREATE TABLE employe (
  entreprise_id int4 NOT NULL, 
  employe_id    int4 NOT NULL UNIQUE, 
  PRIMARY KEY (entreprise_id, 
  employe_id));
CREATE TABLE metier (
  metier_id  SERIAL NOT NULL, 
  lib       varchar(30) NOT NULL, 
  PRIMARY KEY (metier_id));
CREATE TABLE mra (
  mra_id    int4 NOT NULL, 
  metier_id int4 NOT NULL, 
  PRIMARY KEY (mra_id));
CREATE TABLE vendeur (
  vendeur_id int4 NOT NULL, 
  PRIMARY KEY (vendeur_id));
CREATE TABLE magasin (
  vendeur_id int4 NOT NULL, 
  magasin_id int4 NOT NULL UNIQUE, 
  nom        varchar(100) NOT NULL, 
  siret      varchar(14) NOT NULL, 
  logo       varchar(255) NOT NULL, 
  PRIMARY KEY (vendeur_id, 
  magasin_id));
CREATE TABLE prefixe (
  prefixe_id  SERIAL NOT NULL, 
  lib        varchar(30) NOT NULL, 
  lib_court  varchar(10) NOT NULL, 
  CONSTRAINT pk_prefixes 
    PRIMARY KEY (prefixe_id));
CREATE TABLE statut (
  statut_id  SERIAL NOT NULL, 
  lib       varchar(20) NOT NULL, 
  PRIMARY KEY (statut_id));
CREATE TABLE societe (
  societe_id int4 NOT NULL, 
  statut_id  int4 NOT NULL, 
  capital    varchar(20) NOT NULL, 
  PRIMARY KEY (societe_id));
CREATE TABLE ent_immat (
  ent_immat_id int4 NOT NULL, 
  tribunal_rcs varchar(50) NOT NULL UNIQUE, 
  siren        varchar(9) NOT NULL UNIQUE, 
  tva_intra    varchar(20) NOT NULL UNIQUE, 
  PRIMARY KEY (ent_immat_id));
CREATE TABLE entreprise (
  entreprise_id int4 NOT NULL, 
  rs            varchar(50) NOT NULL, 
  PRIMARY KEY (entreprise_id));
CREATE TABLE organisme (
  organisme_id int4 NOT NULL, 
  org_type_id  int4 NOT NULL, 
  name         varchar(50) NOT NULL, 
  PRIMARY KEY (organisme_id));
CREATE TABLE membre (
  membre_id int4 NOT NULL, 
  pseudo    varchar(30) NOT NULL, 
  logo      varchar(255) NOT NULL, 
  PRIMARY KEY (membre_id));
CREATE TABLE main_phone (
  actor_id int4 NOT NULL, 
  phone_id int2 NOT NULL, 
  PRIMARY KEY (actor_id));
CREATE TABLE main_adresse (
  actor_id   int4 NOT NULL, 
  adresse_id int2 NOT NULL, 
  PRIMARY KEY (actor_id));
CREATE TABLE main_email (
  actor_id int4 NOT NULL, 
  email_id int2 NOT NULL, 
  PRIMARY KEY (actor_id));
CREATE TABLE personne (
  personnne_id int4 NOT NULL, 
  prefixe      int4 NOT NULL, 
  name         varchar(50) NOT NULL, 
  firstname    varchar(50) NOT NULL, 
  CONSTRAINT persons_pk 
    PRIMARY KEY (personnne_id));
CREATE TABLE phone (
  actor_id int4 NOT NULL, 
  phone_id int2 NOT NULL, 
  lib      varchar(50) NOT NULL, 
  number   varchar(20) NOT NULL, 
  CONSTRAINT phones_pk 
    PRIMARY KEY (actor_id, 
  phone_id));
CREATE TABLE email (
  actor_id int4 NOT NULL, 
  email_id int2 NOT NULL, 
  lib      varchar(50) NOT NULL, 
  address  varchar(320) NOT NULL, 
  CONSTRAINT emails_pk 
    PRIMARY KEY (actor_id, 
  email_id));
CREATE TABLE adresse (
  actor_id    int4 NOT NULL, 
  adresse_id  int2 NOT NULL, 
  lib         varchar(50) NOT NULL, 
  chez        varchar(38) DEFAULT '' NOT NULL, 
  line_1      varchar(38) NOT NULL, 
  line_2      varchar(38) DEFAULT '' NOT NULL, 
  line_3      varchar(38) DEFAULT '' NOT NULL, 
  code_postal char(5) NOT NULL, 
  ville       varchar(32) NOT NULL, 
  CONSTRAINT adresses_pk 
    PRIMARY KEY (actor_id, 
  adresse_id));
CREATE TABLE actor (
  actor_id  SERIAL NOT NULL, 
  CONSTRAINT actors_pk 
    PRIMARY KEY (actor_id));
CREATE TABLE "public".member_login (
  member_id int4 NOT NULL UNIQUE, 
  id_user   int4 NOT NULL UNIQUE, 
  CONSTRAINT actors_logins_pk 
    PRIMARY KEY (member_id, 
  id_user));
ALTER TABLE organisme ADD CONSTRAINT organisme_org_type_org_type_id_fk FOREIGN KEY (org_type_id) REFERENCES org_type (org_type_id);
ALTER TABLE demolition ADD CONSTRAINT demolition_magasin_magasin_id_fk FOREIGN KEY (magasin_id) REFERENCES magasin (magasin_id) ON DELETE Cascade;
ALTER TABLE phone ADD CONSTRAINT phone_actor_actor_id_fk FOREIGN KEY (actor_id) REFERENCES actor (actor_id) ON DELETE Cascade;
ALTER TABLE email ADD CONSTRAINT email_actor_actor_id_fk FOREIGN KEY (actor_id) REFERENCES actor (actor_id) ON DELETE Cascade;
ALTER TABLE adresse ADD CONSTRAINT adresse_actor_actor_id_fk FOREIGN KEY (actor_id) REFERENCES actor (actor_id) ON DELETE Cascade;
ALTER TABLE main_email ADD CONSTRAINT main_email_email_actor_id_email_id_fk FOREIGN KEY (actor_id, email_id) REFERENCES email (actor_id, email_id) ON DELETE Cascade;
ALTER TABLE main_phone ADD CONSTRAINT main_phone_phone_actor_id_phone_id_fk FOREIGN KEY (actor_id, phone_id) REFERENCES phone (actor_id, phone_id) ON DELETE Cascade;
ALTER TABLE main_adresse ADD CONSTRAINT main_adresse_adresse_actor_id_fk FOREIGN KEY (actor_id, adresse_id) REFERENCES adresse (actor_id, adresse_id) ON DELETE Cascade;
ALTER TABLE membre ADD CONSTRAINT membre_actor_membre_id_fk FOREIGN KEY (membre_id) REFERENCES actor (actor_id) ON DELETE Cascade;
ALTER TABLE organisme ADD CONSTRAINT organisme_membre_organisme_id_fk FOREIGN KEY (organisme_id) REFERENCES membre (membre_id) ON DELETE Cascade;
ALTER TABLE personne ADD CONSTRAINT personne_membre_personnne_id_fk FOREIGN KEY (personnne_id) REFERENCES membre (membre_id) ON DELETE Cascade;
ALTER TABLE entreprise ADD CONSTRAINT entreprise_membre_entreprise_id_fk FOREIGN KEY (entreprise_id) REFERENCES membre (membre_id);
ALTER TABLE ent_immat ADD CONSTRAINT ent_immat_entreprise_ent_immat_id_fk FOREIGN KEY (ent_immat_id) REFERENCES entreprise (entreprise_id) ON DELETE Cascade;
ALTER TABLE societe ADD CONSTRAINT societe_ent_immat_societe_id_fk FOREIGN KEY (societe_id) REFERENCES ent_immat (ent_immat_id) ON DELETE Cascade;
ALTER TABLE societe ADD CONSTRAINT societe_statut_statut_id_fk FOREIGN KEY (statut_id) REFERENCES statut (statut_id);
ALTER TABLE personne ADD CONSTRAINT prefixes_persons_fk FOREIGN KEY (prefixe) REFERENCES prefixe (prefixe_id) ON UPDATE No action ON DELETE No action;
ALTER TABLE vendeur ADD CONSTRAINT vendeur_ent_immat_vendeur_id_fk FOREIGN KEY (vendeur_id) REFERENCES ent_immat (ent_immat_id) ON DELETE Cascade;
ALTER TABLE mra ADD CONSTRAINT mra_entreprise_mra_id_fk FOREIGN KEY (mra_id) REFERENCES entreprise (entreprise_id) ON DELETE Cascade;
ALTER TABLE mra ADD CONSTRAINT mra_metier_metier_id_fk FOREIGN KEY (metier_id) REFERENCES metier (metier_id);
ALTER TABLE magasin ADD CONSTRAINT magasin_vendeur_vendeur_id_fk FOREIGN KEY (vendeur_id) REFERENCES vendeur (vendeur_id) ON DELETE Cascade;
ALTER TABLE magasin ADD CONSTRAINT magasin_actor_magasin_id_fk FOREIGN KEY (magasin_id) REFERENCES actor (actor_id);
ALTER TABLE employe ADD CONSTRAINT employe_personne_employe_id_fk FOREIGN KEY (employe_id) REFERENCES personne (personnne_id) ON DELETE Cascade;
ALTER TABLE employe ADD CONSTRAINT employe_entreprise_entreprise_id_fk FOREIGN KEY (entreprise_id) REFERENCES entreprise (entreprise_id);
ALTER TABLE responsable ADD CONSTRAINT responsable_employe_employe_id_fk FOREIGN KEY (employe_id) REFERENCES employe (employe_id) ON DELETE Cascade;
ALTER TABLE employe_mag ADD CONSTRAINT employe_mag_employe_entreprise_id_employe_id_fk FOREIGN KEY (entreprise_id, employe_id) REFERENCES employe (entreprise_id, employe_id) ON DELETE Cascade;
ALTER TABLE employe_mag ADD CONSTRAINT employe_mag_magasin_entreprise_id_magasin_id_fk FOREIGN KEY (entreprise_id, magasin_id) REFERENCES magasin (vendeur_id, magasin_id) ON DELETE Cascade;
ALTER TABLE directeur ADD CONSTRAINT directeur_employe_mag_entreprise_id_employe_id_magasin_id_fk FOREIGN KEY (entreprise_id, employe_id, magasin_id) REFERENCES employe_mag (entreprise_id, employe_id, magasin_id) ON DELETE Cascade;
CREATE INDEX personne_idx 
  ON personne (name, firstname);
CREATE INDEX adresse_idx 
  ON adresse (code_postal);

Dans l'attente de vos remarques,
François

[fsmrel]

Bonsoir François,


Merci pour le code généré, je le mets soigneusement de côté, car il est la seule représentation exacte, dépourvue d’ambiguïté de ce que sera la base de données (je me méfie des diagrammes..)

Pour ce qui est de votre implémentation SQL, je doute que cela impact négativement les performances sur une petite table comme la tva. En tous cas avec la mienne c'était partit pour la galère. Donc, si j'ai bien compris, il faudrait faire de même qu'avec la tva avec les prix et les adresses. Ces tables-ci (piece et adresse) comportent plusieurs centaines de milliers de lignes.

Si vous avez des soucis quant au codage des requêtes SQL, même sans être un champion de ce langage, je pourrai toujours essayer de vous aider (sauf dans la situation de galère que vous avez évoquée !) Si la requête que je vous ai proposée vous paraît compliquée, on peut la réécrire ainsi :

Dans un 1er bloc, on se focalise sur la table TVA :

Code SQL :

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

1
2
3
4
5
6
7
8
9
SELECT CommandeNo, CommandeDate, PieceDesignation, TvaTaux, Quantite
FROM   ITEM_CDE AS x, COMMANDE AS y,  
                      PIECE AS z,
                      TVA AS t
  WHERE x.ActorId = 1 
    AND CommandeDate >= t.TvaApplDebut 
    AND x.ActorId = y.ActorId AND x.CommandeId = y.CommandeId
    AND x.PieceId = z.PieceId
    AND z.TvaId = t.TvaId

Dans un 2e bloc, on se focalise sur la table TVA_HISTO :

Code SQL :

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

1
2
3
4
5
6
7
8
9
SELECT CommandeNo, CommandeDate, PieceDesignation, TvaTaux, Quantite
FROM   ITEM_CDE AS x, COMMANDE AS y,  
                      PIECE AS z,
                      TVA_HISTO AS t
  WHERE x.ActorId = 1 
    AND CommandeDate BETWEEN t.TvaApplDebut AND  t.TvaApplFin 
    AND x.ActorId = y.ActorId AND x.CommandeId = y.CommandeId
    AND x.PieceId = z.PieceId
    AND z.TvaId = t.TvaId

Et on réunit les deux blocs en une requête unique, par UNION :

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
SELECT CommandeNo, CommandeDate, PieceDesignation, TvaTaux, Quantite
FROM   ITEM_CDE AS x, COMMANDE AS y,  
                      PIECE AS z,
                      TVA AS t
  WHERE x.ActorId = 1 
    AND CommandeDate >= t.TvaApplDebut 
    AND x.ActorId = y.ActorId AND x.CommandeId = y.CommandeId
    AND x.PieceId = z.PieceId
    AND z.TvaId = t.TvaId 
 
UNION ALL
 
SELECT CommandeNo, CommandeDate, PieceDesignation, TvaTaux, Quantite
FROM   ITEM_CDE AS x, COMMANDE AS y,  
                      PIECE AS z,
                      TVA_HISTO AS t
  WHERE x.ActorId = 1 
    AND CommandeDate BETWEEN t.TvaApplDebut AND  t.TvaApplFin 
    AND x.ActorId = y.ActorId AND x.CommandeId = y.CommandeId
    AND x.PieceId = z.PieceId
    AND z.TvaId = t.TvaId

Vous avez des craintes au sujet de la performance, aussi n’hésitez pas à vous exercer à l’instruction EXPLAIN (au fait quel est votre SGBD ?) Prenons le cas de la requête ci-dessus, mais gonflons les volumétries en jeu :

Table ACTOR : 100 000 lignes
Table COMMANDE : 1 000 000 lignes
Table ITEM_CDE : 5 000 000 lignes (on ne mollit pas !)
Table PIECE : 10 000 lignes
Table TVA : 100 000 lignes (c’est juste pour le fun...)
Table TVA_HISTO : 1 000 000 lignes

Il ne faut pas s’alarmer. En effet, que dit le DB2 que j’utilisais il y 10 ans ? (Et qui n’a jamais éprouvé la moindre émotion quant aux performances...) Voici sa stratégie pour le bloc 2 (participation de la table TVA_HISTO)

1) Utilisation de l’index branché sur la clé primaire de la table ITEM_CDE pour récupérer les items pour lesquels ActorId = 1. Supposons qu’il y ait 500 items récupérés. Pour un DBA, c’est tout bon.

2) A partir de ces 500 items, utilisation de l’index branché sur la clé primaire de la table COMMANDE pour récupérer les commandes référencées par ces items. Supposons qu’il y ait 100 commandes récupérées (sur la base 5 items par commande). C’est encore tout bon.

3) Accès à la table TVA pour traiter la condition « CommandeDate >= t.TvaApplDebut » et récupérer le taux (colonne TvaTaux). Supposons qu’il y ait 50 commandes concernées (disons la moitié de ce qui a été récupéré au cours de l’étape précédente). DB2 annonce un table scan (balayage de table), bien qu’il y ait un index de clé {CommandeDate, TvaTaux} pour éviter cela et booster la performance : l’outil que j’ai utilisé a une faiblesse étonnante ès matière, mais je ne vais pas creuser, ça n’est pas grave.

4) Pour les 250 items rescapés (à la louche, la moitié des 500), accès à la table PIECE par index pour récupérer le libellé des pièces.

Le coût estimé par DB2 pour le bloc 2 (donc extrapolable pour le bloc 1) : environ 20 secondes. Ça paraît beaucoup, mais l’outil d’estimation n’a pas tenu compte de l’index de clé {TvaApplDebut, TvaTaux} branché sur la table TVA_HISTO. Quoi qu’il en soit, si je ramène la volumétrie de cette table à 100 000 lignes, le temps de traitement est d’environ 2 secondes, et pour une volumétrie de 10 000 lignes, ce temps de traitement est d’environ 300 millisecondes, Joe Dalton est battu.


Maintenant, avant de parler de l’historisation des adresses et la performance des requêtes correspondantes, peut-être pourrait-on en revoir lla structure. Le diagramme ci-dessous vous convient-il ? (L’attribut AdrDateDebut anticipe sur l’historisation...)





Code SQL correspondant :

TABLE VILLE

Code SQL :

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

1
2
3
4
5
6
CREATE TABLE VILLE 
(
  VilleId        INT               NOT NULL,
  VilleNom       VARCHAR(45)       NOT NULL,
  CONSTRAINT VILLE_PK PRIMARY KEY (VilleId)
) ;

TABLE CODE_POSTAL

Code SQL :

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

1
2
3
4
5
6
7
CREATE TABLE CODE_POSTAL 
(
  CPid           INT               NOT NULL,
  CodePostal     CHAR(5)           NOT NULL
  , CONSTRAINT CODE_POSTAL_PK PRIMARY KEY (CPid)
  , CONSTRAINT CODE_POSTAL_AK UNIQUE (CodePostal)
) ;

TABLE VILLE_CP

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TABLE VILLE_CP 
(
  VilleId        INT               NOT NULL,
  CPid           INT               NOT NULL
  , CONSTRAINT VILLE_CP_PK PRIMARY KEY (VilleId, CPid)
  , CONSTRAINT VILLE_CP_VILLE_FK FOREIGN KEY (VilleId) REFERENCES VILLE
  , CONSTRAINT VILLE_CP_CODE_POSTAL_FK FOREIGN KEY (CPid) REFERENCES CODE_POSTAL
) ;

TABLE ACTOR

Code SQL :

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

1
2
3
4
5
CREATE TABLE ACTOR 
(
  ActorId        INT               NOT NULL,
  CONSTRAINT ACTOR_PK PRIMARY KEY (ActorId)
) ;

TABLE ADRESSE

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
CREATE TABLE ADRESSE 
(
  ActorId            INT             NOT NULL,
  AdresseId          INT             NOT NULL,
  AdrDateDebut       DATETIME        NOT NULL,
  AdrLibelle         VARCHAR(45)     NOT NULL DEFAULT '',
  AdrChez            VARCHAR(45)     NOT NULL DEFAULT '',
  AdrLigne1          VARCHAR(45)     NOT NULL,
  AdrLigne2          VARCHAR(45)     NOT NULL DEFAULT '',
  AdrLigne3          VARCHAR(45)     NOT NULL DEFAULT '',
  AdrVilleId         INT             NOT NULL,
  AdrCPid            INT             NOT NULL
  , CONSTRAINT ADRESSE_PK PRIMARY KEY (ActorId, AdresseId)
  , CONSTRAINT ADRESSE_ACTOR_FK FOREIGN KEY (ActorId) REFERENCES ACTOR 
  , CONSTRAINT ADRESSE_VILLE_CP_FK FOREIGN KEY (AdrVilleId, AdrCPid) REFERENCES VILLE_CP
) ;

TABLE COMMANDE

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
CREATE TABLE COMMANDE 
(
  ActorId            INT             NOT NULL,
  CommandeId         INT             NOT NULL,
  CommandeDate       DATETIME        NOT NULL,
  CommandeNo         VARCHAR(10)     NOT NULL,
  AdresseFactId      INT             NOT NULL,
  AdresseLivrId      INT             NOT NULL
  , CONSTRAINT COMMANDE_PK PRIMARY KEY (ActorId, CommandeId)
  , CONSTRAINT COMMANDE_AK UNIQUE (CommandeNo)
  , CONSTRAINT COMMANDE_ACTOR_FK FOREIGN KEY (ActorId) REFERENCES ACTOR
  , CONSTRAINT COMMANDE_ADRESSE_FACT_FK FOREIGN KEY (ActorId, AdresseFactId) REFERENCES ADRESSE
  , CONSTRAINT COMMANDE_ADRESSE_LIVR_FK FOREIGN KEY (ActorId, AdresseFactId) REFERENCES ADRESSE
) ;

N.B. J’ai isolé les villes et CP histoire d’en mieux garantir la cohérence, mais ici c'est secondaire.

[frm013]

Bonjour François,

Si vous avez des soucis quant au codage des requêtes SQL, même sans être un champion de ce langage, je pourrai toujours essayer de vous aider (sauf dans la situation de galère que vous avez évoquée !) Si la requête que je vous ai proposée vous paraît compliquée...

Bah, normalement, je n'ai pas trop de problèmes même si je ne suis pas un champion non plus. Comme je vous disais, mon cerveau à buggé en cherchant une solution différente. Naïvement je pensais pouvoir réaliser la jointure avec la vue TVA_ALL dans le clause JOIN y compris avec la date ( j'avais bien pensé à une vue ).

Donc la requête ne me parait pas compliquée, voir plus bas. Ce qui vous semble des réactions de débutant est le produit des changements important que vous avez introduit dans ma façon de travailler.

Je prends note vous proposition d'aide et vous en remercie, il est fort probable que j'en ai besoin au cours de l'implémentation

Mon SGBDR est PostgreSql :

Je n'ai donc pas la chance de pouvoir disposer de tous les raffinements de DB2, notamment des index cluster. Avec PostgreSql on peut seulement déclarer une table cluster selon tel index mais il faut la réorganiser régulièrement. En effet, l'espace disponible que l'on peut paramétrer avec le fill factor n'est utilisé que pour les mises à jour, pas pour les insertions, donc l'ordre dans un telle table va se dégrader rapidement. j'espère qu'ils travailleront rapidement sur cela.

J'ai louché récemment sur les licences DB2 et je ne peux m'offrir cela
D'autre part je suis quand même assez attaché au modèle open source, notamment à la qualité du support qui permet bien souvent d'avoir accès aux développeurs directement.

Concernant la volumétrie, puisque nous mettons le nez sous le capot, je table sur 2000 demandes / jours avec peut-être 500 réponses aboutissant à 100 commandes, rien d'insurmontable je crois. Ceci étant une évaluation optimiste bien entendu.
On se retrouve avec en gros 350 000 membres acquis en cinq ans auquel ajouter les 100 000 actuels.

En dehors des performances, ce qui m'inquiète avec cette histoire d'historisation, c'est qu'on va se la trimballer tout au long de toutes les requêtes ayant un rapport avec les commandes. J'aime les codes clairs, que ce soit dans le SQL ou dans le code de l'application.
Vous me direz qu'il existe les vues pour ça...

Mais il est vrai qu'utiliser l'historisation plutôt que de copier les valeurs courantes dans les lignes de commandes ( ce que je faisait auparavant ) est non seulement plus élégant, mais notablement mieux normalisé
Pour tout dire ma méthode violait je ne sais combien de formes normales...

Au sujet des adresses :

Oui votre diagramme me convient, c'est celui que j'avais en tête à l'exception des tables VILLE et CP.

J'ai récupéré, pour voir, une de ces liste, et je me suis retrouvé 20 ans en arrière lorsque j'avais eu à travailler pour une société qui faisait du plubli-postage... Je me souvient encore du travail impliqué pour importer ces fichiers et en assurer la mise à jour! De plus, comme l'explique M. Brouard, ces données n'étant jamais à jour en temps réel, il faut prévoir de pouvoir effectuer une saisie qui ne fasse pas référence à la table CP. Eux n'avaient pas le choix, 150 000 prospects, 20 000 abonnés au revues, il fallait être sûr des adresses pour ne pas se faire taper sur les doigts par la poste!

En résumé, cela ne m'enchante guère.

Voilà, fin du message "je raconte ma vie".
Bonne journée,
François

[fsmrel]

Bonsoir François,

Au sujet des adresses : Oui votre diagramme me convient, c'est celui que j'avais en tête à l'exception des tables VILLE et CP.

D’accord, on évacue les tables VILLE et CP. Maintenant, historise-t-on les adresses en cas de changement ? Autant cela s’impose pour les taux de TVA, autant ça n’est pas en l'occurrence le cas si on part du principe que Raoul peut avoir de nouvelles adresses où il se fait livrer au gré de ses déplacements (la péniche, le Jus d’pomme, la clinique Dugoineau quand il a des problèmes de santé, etc.). Rien ne dit que Raoul n’habite plus à la l’adresse indiquée sauf vraisemblablement en cas d’un retour de marchandise le précisant (NPAI).

Sommes-nous d’accord pour en rester au diagramme suivant quant aux adresses ?



A noter qu’il y a une contrainte de chemin, mais contrôlée, puisque grâce à l’intégrité référentielle l’attribut ActorId de COMMANDE faisant référence à celui d’ADRESSE doit faire aussi référence à celui d’ACTOR.


Au-delà de la modélisation, un problème peut se poser en production, celui de la volumétrie de la table : si je comprends bien, on aurait un nombre de membres de l’ordre de 500 000, donc un nombre d’adresses un peu supérieur. Il s’agit de savoir si cela pose ou non un problème en ce qui concerne les sauvegardes, les réorganisations, les restaurations, bref toutes ces tâches incontournables pouvant concerner non pas une mais mille tables, alors que la production ne doit pas être perturbée, ce qui conduit en général au partitionnement des tables.

Avec PostgreSql on peut seulement déclarer une table cluster selon tel index mais il faut la réorganiser régulièrement.

Il est évident qu’avec DB2 on ne peut là aussi avoir qu’un seul ordre de rangement des lignes d’une table dans sa structure physique d’accueil. Mais les problèmes de la fréquence de la réorganisation et celle des autres tâches de service évoquées se posent avec beaucoup moins d’acuité (je parle de DB2 for z/OS, que j’ai chahuté jusqu’à il y a 10 ans), puisque, tant qu’il y a de la place, les ajouts ont lieu dans les bonnes pages (respect de la séquence définie pour l’index cluster), et si ces bonnes pages sont pleines, les ajouts ont lieu dans des pages qu’on a déclarées vides, chaînées avec les autres, etc.

Par ailleurs, la durée d’une réorganisation ou autre opération dépend de façon cruciale du partitionnement. Ainsi, toujours avec DB2, si je devais traiter des commandes sur 10 ans, je définirais par exemple 10 partitions (une par an, sur la base de 200 000 commandes/an) :

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 COMMANDE
(
  ActorId            INT             NOT NULL,
  CommandeId         INT             NOT NULL,
  CommandeDate       DATE            NOT NULL,
  CommandeNo         VARCHAR(10)     NOT NULL,
  AdresseFactId      INT             NOT NULL,
  AdresseLivrId      INT             NOT NULL
  , CONSTRAINT COMMANDE_PK PRIMARY KEY (ActorId, CommandeId)
  , CONSTRAINT COMMANDE_AK UNIQUE (CommandeNo)
  , CONSTRAINT COMMANDE_ACTOR_FK FOREIGN KEY (ActorId) REFERENCES ACTOR
  , CONSTRAINT COMMANDE_ADRESSE_FACT_FK FOREIGN KEY (ActorId, AdresseFactId) REFERENCES ADRESSE
  , CONSTRAINT COMMANDE_ADRESSE_LIVR_FK FOREIGN KEY (ActorId, AdresseFactId) REFERENCES ADRESSE
)      , PARTITION BY (CommandeDate)
         PARTITION  1 ENDING AT ('2005-12-31') 
         PARTITION  2 ENDING AT ('2006-12-31') 
         ...       
         PARTITION  9 ENDING AT ('2013-12-31') 
         PARTITION 10 ENDING AT ('2014-12-31')

Je suis parti sur le principe de partitionner en fonction de la date de commande, mais ça pourrait être sur autre chose : il s’agit d’un problème de répartition équilibrée, sachant que la réorganisation ou toute autre tâche de service peut être effectuée pour une partition en particulier, auquel cas on divise le temps des opérations par 10.

Vous noterez que les commandes n’ont pas besoin de faire l’objet d’une table COMMANDE_HISTO, on est toujours en dehors de la logique TVA

En outre, pour que les lignes dans chaque partition soient ordonnées (par exemple) sur {ActorId, CommandeId} on définit un index cluster ad-hoc :

Code SQL :

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

1
2
CREATE INDEX COMMANDEX1 ON COMMANDE (ActorId, CommandeId)
    PARTITIONED CLUSTER ;

L’index COMMANDEX1 sera par ailleurs déclaré UNIQUE à cause de la clé primaire (ActorId, CommandeId).

Qu’en est-il de tout cela avec PostgreSQL ?

[frm013]

Bonjour François,

Sommes-nous d’accord pour en rester au diagramme suivant quant aux adresses ?

D'accord, oui, à l'exception que c'est MEMBRE qui doit être client, un magasin ne peut pas commander en tant que magasin

Concernant la volumétrie, nous somme loin de penser atteindre les mille tables, plutôt environ 100-120 au max.
Nous serons plus probablement aux alentours de 35000 commandes par an. Pour les demandes, ce sera plus lourd, mais c'est une petite table en terme de volume de données.

Si je ne me plante pas, Adresse devrait "peser" environ 150Mo, sa ré-organisation devrait être légère. D'autre part, il me semble me rappeler qu'un utilitaire PostgreSql permet de faire ce travail en ligne, je ne l'ai pas bookmarqué, il faudra que je recherche à nouveau

Concernant le partitionnement, PostgreSql n'est pas très bien loti (difficulté pour l'implémentation ), c'est un de ses points faibles, à priori dans le calendrier proche des upgrades.

J'avais pensé "urbaniser" le stockage disque selon le même schéma que celle de mes modèles pour éviter ainsi le mélange données membres, données demandes, commandes, nomenclature. Mais j'anticipe

Maintenant, historise-t-on les adresses en cas de changement ? Autant cela s’impose pour les taux de TVA, autant ça n’est pas en l'occurrence le cas

Il y a les prix aussi...

J'avais raisonné comme vous dans un premier temps, malheureusement, s'agissant d'un site internet, on ne peut être sûr que l'utilisateur ne remplacera pas son adresse principale en cas de déménagement, et là c'est la cata! Et on ne peut pas lui interdire de corriger une erreur de frappe.

Je pense qu'il y a peu de chance que l'on ait besoin de faire un traitement de masse concernant les commandes nécessitant de récupérer les adresse des membres. Pourquoi pas, mais pas à la disposition de l'utilisateur en dehors de ses propres données.

Ceci dit, puisque la réplication est au point chez PostgreSql, je pense gérer un clone ( ou plus ), pour une sauvegarde quotidienne qui ne pèse pas sur la prod, cela peut aussi permettre ce genre de traitements lourd. J'ai mis cela en place pour mes bases MySql, c'est très bien, le serveur à la maison est quasi cloné ( les bases le sont ) et les bases sauvegardées selon le calendrier classique usuel.

Il ne faut pas oublier que c'est une volumétrie prévisionnelle sur cinq ans, quand on voit l'évolution des performances du matériel je suis rassuré.
On est très loin de grosses volumétries genre "Le Bon coin" ou pire, de "very big data" type Twitter ou Facebook qui on dû faire face à des problème de scalability gigantesques !! cf highscalability

Je commence une partie de l'implémentation ( membres et demandes), il faut que j'avance sur ça, je suis en retard. Mais je pense que la qualité du modèle me fera gagner beaucoup de temps, j'ai déjà visualisé mentalement les simplifications de dev.

A vous lire,
François

[fsmrel]

Bonsoir François,

D'accord, oui, à l'exception que c'est MEMBRE qui doit être client, un magasin ne peut pas commander en tant que magasin

Pas de problème. A noter que la contrainte de chemin est respectée, si le membre Fernand passe commande, les adresses de livraison et de facturation ne pourront toujours pas être celles de Raoul, mais seulement celles de Fernand.

on ne peut être sûr que l'utilisateur ne remplacera pas son adresse principale en cas de déménagement, et là c'est la cata !

En quoi est-ce la cata ? Pour le moment, il y a indépendance des adresses de facturation et de livraison par rapport à l’adresse principale. Il y aurait donc une raison profonde faisant qu’un simple annule et remplace de l’adresse principale ne soit pas suffisant ?

Et on ne peut pas lui interdire de corriger une erreur de frappe.

Même chose ?

Il y a les prix aussi...

Je vais jeter un œil.

[frm013]

Je crois qu'on ne s'est pas compris...

Quand je dis remplacer, je veux dire écraser.

Ex : Fernand qui a pour adresse principale ( de facturation ) l'adresse numéro 1 à Paris, passe commande, celle-ci "pointe" donc vers l'adresse 1.

Six mois plus tard Fernand déménage à Marseille, il n'ajoute pas une nouvelle adresse dont il fera son adresse principale mais remplace celle-ci par sa nouvelle adresse, donc l'adresse n° 1 est maintenant celle de Marseille.
Ainsi, sa commande d'il y a six mois comporte donc comme adresse de facturation la nouvelle adresse de Fernand à Marseille.

[fsmrel]

Fernand qui a pour adresse principale ( de facturation )

Vous commencez à fournir des éléments de réponse à ma question concernant les adresses de livraison et facturation et leurs relations avec l’adresse principale. "Adresse principale" et "adresse de facturation" sont-ils des termes interchangeables ? Quelles sont très exactement les liens unissant ce trio de types d'adresses ?



Concernant les prix :

En historisant les prix comme les TVA, on est capable de connaître le prix HT (et le prix TTC...) des pièces commandées à une date donnée. N’est-il pas ?

[frm013]

Les adresses à mon sens :

Facturation : l'adresse de la personne à qui on facture, son lieu de résidence, donc son adresse principale.

Livraison : tout lieu auquel cette personne veut se faire livrer. Exemple : un garage ou Fernand ( commence à être célèbre c'lui-là ) fera poser le silencieux qu'il a acheté.

En pratique c'est au choix du client, par défaut on lui propose son adresse principale comme adresse de facturation et de livraison.

Concernant votre diagramme :

Effectivement, on aura des modifications de prix au cours du temps, donc je pensais arriver à ce diagramme.
Ca fait 4 données à historiser et à retrouver

[fsmrel]

Bonsoir François,

Ca fait 4 données à historiser et à retrouver

Avec de bonnes vues, ça devrait aller...

En pratique c'est au choix du client, par défaut on lui propose son adresse principale comme adresse de facturation et de livraison.

Partons de la situation suivante (pour des raisons de lisibilité, je remplace les valeurs numériques des attributs clés par les noms correspondants) :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
ADRESSE {ActorId    AdresseId    AdresseDateDebut}
         Fernand    Paris        2005-04-01 
         Fernand    Lyon         2005-04-05 


MAIN_ADDRESS {ActorId    AdresseId}
              Fernand    Paris


Commande {MembreId    CommandeId    CommandeDate    AdresseFact    AdresseLivr}
          Fernand              1    2005-04-10      Paris          Lyon

Le 1er janvier 2008, Fernand change d’adresse principale, qu’il modifie en remplaçant Paris par Marseille : ça n’a aucune incidence sur ses commandes passées. En revanche, l’application doit prendre en compte l’événement, en créant une ligne dans la table ADRESSE et en modifiant la ligne correspondante dans la table MAIN_ADDRESS. La situation devient la suivante :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
ADRESSE {ActorId    AdresseId    AdresseDateDebut}
         Fernand    Paris        2005-04-01 
         Fernand    Lyon         2005-04-05 
         Fernand    Marseille    2008-01-01 


MAIN_ADDRESS {ActorId    AdresseId}
              Fernand    Marseille

Commande {MembreId    CommandeId    CommandeDate    AdresseFact    AdresseLivr}
          Fernand              1    2005-04-10      Paris          Lyon

Et donc à partir du 1er janvier 2008 on pourra proposer à Fernand Marseille comme adresse de livraison et de facturation.


Qu’est-ce que j’ai raté ? Certes, on ne sait plus qu’avant le 1er janvier 2008 Fernand avait Paris pour adresse principale, mais quelle importance ? (S’il le fallait, on pourrait historiser les adresses principales...)

[frm013]

Bonsoir François,

Le risque n'est pas là :

J'ai gardé une référence numérique volontairement !

Adresse de Fernand :

Code :

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

1
2
3
MemberId	AddresseId	line_1 			ville
--------	----------	-------------------	--------
Fernand		1		105, bd Malesherbes	Paris

sa commande :

Code :

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

1
2
3
 CommandeId	MembreId	DateCde		AdresseFacturation
----------	--------	-------		------------------
1		Fernand		2005-04-10	1

Fernand déménage le 20 juin 2005 et il écrase les données de son adresse principale, c'est à dire l'adresse numéro 1

Comment l'application peut-elle "savoir" que c'est une nouvelle adresse et non une correction ?

Code :

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

1
2
3
MemberId	AddresseId	line_1 			ville
--------	----------	-------------------	--------
Fernand		1		250, av de la corse	Marseille

Et boum, sa commande du 10 avril est transformée avec comme adresse de facturation celle de Marseille !

Sacré Fernand

En passant, ce n'est même pas un risque, c'est sûr qu'il fera comme ça.

A demain!

[fsmrel]

Du moment que Fernand modifie l’adresse principale (je suppose qu’il a accès à une zone de saisie « Adresse principale »), on ne touche pas aux lignes existantes de la table ADRESSE, puisqu’a priori elles dont référencées par les commandes déjà passées : ce que saisit Fernand, nouvelle adresse ou correction) fait l’objet d’un ajout dans la table ADRESSE et d’une modification dans la table MAIN_ADDRESS :


Avant :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
ADRESSE

MemberId    AddresseId    line_1                 ville        AdresseDateDebut
--------    ---------     -------------------    --------     ----------------
Fernand     1             105, bd Malesherbes    Paris        2005-04-01

MAIN_ADRESS 

MemberId    AddresseId
--------    -----------   
Fernand     1

Après :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
ADRESSE

MemberId    AddresseId    line_1                 ville         AdresseDateDebut
--------    ---------     -------------------    ---------     ----------------
Fernand     1             105, bd Malesherbes    Paris         2005-04-01
Fernand     2             250, av de la Corse    Marseille     2008-01-01

MAIN_ADRESS 

MemberId    AddresseId
--------    -----------   
Fernand     2

C’est ce que j’avais proposé dans mon message précédent...

L’écran de saisie des adresses ressemble à quoi ? Celui des commandes (en ce qui concerne les adresses) ?

[frm013]

Bonjour François,

Ok, je vois, donc si Fernand à fait deux corrections sur son adresse à Paris nous aurons ces données :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
ADRESSE

MemberId    AddresseId    line_1                 ville         AdresseDateDebut
--------    ---------     -------------------    ---------     ----------------
Fernand     1             105, bd Maleherbes     Pari          2005-04-01
Fernand     2             105, bd Malesherbes    Pari          2005-04-01
Fernand     3             105, bd Malesherbes    Paris         2005-04-01
Fernand     4             250, av de la Corse    Marseille     2008-01-01

MAIN_ADRESS 

MemberId    AddresseId
--------    -----------   
Fernand     4

Concernant les écrans commandes et adresses, rien que de très classique :

Une liste des adresses, avec les boutons ajouter, modifier, supprimer.
Un écran de saisie de l'adresse sélectionnée.

Donc avec les données précédentes on aura à l'écran deux adresses obsolètes. A moins de supprimer systématiquement l'adresse corrigée si elle n'est pas utilisée. Si elle est utilisée, elle demeurera à l'écran, à moins d'ajouter un flag "obsolète".
Ca commence à devenir compliqué niveau traitement applicatif... Un trigger "insteadof" béton!

Pour les commandes, au stade du choix des adresses, deux zones, une affichant la facturation et une pour la livraison, surplombées d'une liste déroulante pour le choix. Par défaut, dans les deux zones on propose l'adresse principale.

Ai-je compris votre pensée ?
Visualisez-vous bien les écrans ?

[fsmrel]

Ok, je vois, donc si Fernand à fait deux corrections sur son adresse à Paris nous aurons ces données :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
ADRESSE

MemberId    AddresseId    line_1                 ville         AdresseDateDebut
--------    ---------     -------------------    ---------     ----------------
Fernand     1             105, bd Maleherbes     Pari          2005-04-01
Fernand     2             105, bd Malesherbes    Pari          2005-04-01
Fernand     3             105, bd Malesherbes    Paris         2005-04-01
Fernand     4             250, av de la Corse    Marseille     2008-01-01

MAIN_ADRESS 

MemberId    AddresseId
--------    -----------   
Fernand     4

C'est tout bon !

Ca commence à devenir compliqué niveau traitement applicatif...

Si la table COMMANDE est la seule qui fasse référence à la table ADRESSE, pour éviter d’afficher les adresses réputées « obsolètes », on pourrait se contenter d’une vue pour alimenter la liste déroulante.

Structure des tables

TABLE ADRESSE

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 TABLE ADRESSE 
(
  ActorId            INT             NOT NULL,
  AdresseId          INT             NOT NULL,
  AdrDateDebut       DATETIME        NOT NULL,
  AdrLibelle         VARCHAR(45)     NOT NULL DEFAULT '',
  AdrChez            VARCHAR(45)     NOT NULL DEFAULT '',
  AdrLigne1          VARCHAR(45)     NOT NULL,
  AdrLigne2          VARCHAR(45)     NOT NULL DEFAULT '',
  AdrLigne3          VARCHAR(45)     NOT NULL DEFAULT '',
  AdrVille           VARCHAR(45)     NOT NULL,
  AdrCP              CHAR(05)        NOT NULL
  , CONSTRAINT ADRESSE_PK PRIMARY KEY (ActorId, AdresseId)
  , CONSTRAINT ADRESSE_ACTOR_FK FOREIGN KEY (ActorId) REFERENCES ACTOR 
) ;

TABLE COMMANDE

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
CREATE TABLE COMMANDE 
(
  ActorId            INT             NOT NULL,
  CommandeId         INT             NOT NULL,
  CommandeDate       DATETIME        NOT NULL,
  CommandeNo         VARCHAR(10)     NOT NULL,
  AdresseFactId      INT             NOT NULL,
  AdresseLivrId      INT             NOT NULL
  , CONSTRAINT COMMANDE_PK PRIMARY KEY (ActorId, CommandeId)
  , CONSTRAINT COMMANDE_AK UNIQUE (CommandeNo)
  , CONSTRAINT COMMANDE_ACTOR_FK FOREIGN KEY (ActorId) REFERENCES ACTOR
  , CONSTRAINT COMMANDE_ADRESSE_FACT_FK FOREIGN KEY (ActorId, AdresseFactId) REFERENCES ADRESSE
  , CONSTRAINT COMMANDE_ADRESSE_LIVR_FK FOREIGN KEY (ActorId, AdresseFactId) REFERENCES ADRESSE
) ;

Organisation de la vue permettant d’obtenir la liste des adresses utilisées par les commandes :

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE VIEW ADR_ACTIVE (ActorId, AdresseId)  AS
    SELECT x.ActorId, AdresseId 
    FROM   ADRESSE AS x INNER JOIN COMMANDE AS y 
           ON x.ActorId = y.ActorId AND x.AdresseId = y.AdresseFactId
   UNION 
    SELECT x.ActorId, AdresseId 
    FROM   ADRESSE AS x INNER JOIN COMMANDE AS y 
           ON x.ActorId = y.ActorId AND x.AdresseId = y.AdresseLivrId ;

Recherche des adresses utilisées pour les commandes de Fernand :

Code SQL :

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

1
2
3
SELECT '' AS ADR_ACTIVE, MembrePseudo, ActorId, AdresseId
FROM   ADR_ACTIVE AS x INNER JOIN MEMBRE AS y ON x.ActorId = y.MembreId
WHERE  MembrePseudo = 'Fernand' ;

Organisation de la vue permettant d’obtenir la liste des adresses non utilisées par les commandes :

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 ADR_INACTIVE (ActorId, AdresseId)  AS
    SELECT ActorId, AdresseId 
    FROM   ADRESSE AS x
    WHERE  NOT EXISTS 
              (
               SELECT ''
               FROM   COMMANDE AS y
               WHERE  x.ActorId = y.ActorId AND x.AdresseId = y.AdresseFactId 
              )   
      AND  NOT EXISTS 
              (
               SELECT ''
               FROM   COMMANDE AS y
               WHERE  x.ActorId = y.ActorId AND x.AdresseId = y.AdresseLivrId 
              )  ;

Recherche des adresses inutilisées pour les commandes de Fernand :

Code SQL :

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

1
2
3
SELECT '' AS ADR_INACTIVE, MembrePseudo, ActorId, AdresseId
FROM   ADR_INACTIVE AS x INNER JOIN MEMBRE AS y ON x.ActorId = y.MembreId
WHERE  MembrePseudo = 'Fernand' ;

Y aura-t-il d’autres tables faisant référence à la table ADRESSE ?

Ai-je compris votre pensée ?

J’en ai bien l’impression ^^
Visualisez-vous bien les écrans ? [/Quote]
Ça devrait aller...

[frm013]

Bonjour François,

Ok pour le code. Merci

Mais je pense qu'il serait mieux pour l'utilisateur de ne pas voir les adresses passées, même si elles sont utilisées.
D'ou le fait que je pensais les flaguer "obsolète".

Donc, fonctionnement du traitement :

Si pas de AdresseId on insère et on laisse faire l'utilisateur.

Sinon on insère une nouvelle entrée et on supprime l'ancienne AdresseId si pas utilisée ou on positionne "obsolete" à true si utilisée par COMMANDE. Si l'ancien AdresseId est aussi présent dan MAIN_ADRESSE on met à jour avec le nouvel AdresseId.

Donc la définition de ADRESSE serait :

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
CREATE TABLE ADRESSE 
(
  ActorId            INT             NOT NULL,
  AdresseId          INT             NOT NULL,
  AdrDateDebut       DATETIME        NOT NULL,
  AdrLibelle         VARCHAR(45)     NOT NULL DEFAULT '',
  AdrChez            VARCHAR(45)     NOT NULL DEFAULT '',
  AdrLigne1          VARCHAR(45)     NOT NULL,
  AdrLigne2          VARCHAR(45)     NOT NULL DEFAULT '',
  AdrLigne3          VARCHAR(45)     NOT NULL DEFAULT '',
  AdrVille           VARCHAR(45)     NOT NULL,
  AdrCP              CHAR(05)        NOT NULL,
  obsolete           TINYINT         NOT NULL
  , CONSTRAINT ADRESSE_PK PRIMARY KEY (ActorId, AdresseId)
  , CONSTRAINT ADRESSE_ACTOR_FK FOREIGN KEY (ActorId) REFERENCES ACTOR 
) ;

La requête pour peupler les listes devient :

Code sql :

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

1
2
3
4
5
6
 
SELECT
  a.line_1 || ' ' || a.ville
FROM
  adresse AS a
WHERE MembrePseudo = 'Fernand' and obsolete = 0

Ainsi Fernand ne voit que les dernières mise à jour.

Je pense qu'il est inutile d'indexer la colonne "obsolete" vu le faible nombre d'adresses qu'il pourra vraisemblablement exister par membre.

Qu'en pensez vous ?

[fsmrel]

Bonsoir François,

je pense qu'il serait mieux pour l'utilisateur de ne pas voir les adresses passées, même si elles sont utilisées.

Attention, en recherchant la dernière adresse, vous commencez à faire intervenir MAX, donc GROUP BY et autres poids lourds.

On obtient une liste pertinente avec la requête que j’ai proposée dans mon précédent message, mettant en jeu la vue ADR_ACTIVE :

Recherche des adresses utilisées pour les commandes de Fernand :

Code SQL :

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

1
2
3
SELECT '' AS ADR_ACTIVE, MembrePseudo, ActorId, AdresseId
FROM   ADR_ACTIVE AS x INNER JOIN MEMBRE AS y ON x.ActorId = y.MembreId
WHERE  MembrePseudo = 'Fernand' ;

Mais à ce stade, faites comme vous le sentez.

A noter que dans votre requête :

Code SQL :

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

1
2
3
4
5
SELECT
  a.line_1 || ' ' || a.ville
FROM
  adresse AS a
WHERE MembrePseudo = 'Fernand' AND obsolete = 0 ;

Il ne faut pas oublier la jointure avec la table MEMBRE, car c’est là que figure l’attribut MembrePseudo.

Je pense qu'il est inutile d'indexer la colonne "obsolete" vu le faible nombre d'adresses qu'il pourra vraisemblablement exister par membre.

Cet index serait parfaitement inutile en consultation courante et serait pénalisant en mise à jour (revers de la médaille bien connu des DBA...)

A vous la 100e...

[frm013]

Bonjour François,

On obtient une liste pertinente avec la requête que j’ai proposée dans mon précédent message, mettant en jeu la vue ADR_ACTIVE

Avec cette vue on obtient les adresses utilisée par les commandes, ok.

Dans votre requête :

Code sql :

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

1
2
3
SELECT '' AS ADR_ACTIVE, MembrePseudo, ActorId, AdresseId
FROM   ADR_ACTIVE AS x INNER JOIN MEMBRE AS y ON x.ActorId = y.MembreId
WHERE  MembrePseudo = 'Fernand' ;

Je ne comprends pas le but de ça : " '' AS ADR_ACTIVE "

Il ne faut pas oublier la jointure avec la table MEMBRE, car c’est là que figure l’attribut MembrePseudo

Oui, j'avais commencé avec MembreId et j'ai changé

vous commencez à faire intervenir MAX, donc GROUP BY et autres poids lourds.

Je ne crois pas...

Reprenons les données de Fernand :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 ADRESSE

MemberId    AddresseId    line_1                 ville         AdresseDateDebut		obsolete
--------    ---------     -------------------    ---------     ----------------		--------
Fernand     1             105, bd Malesherbes    Paris         2005-04-01		0

MAIN_ADRESS 

MemberId    AddresseId
--------    -----------   
Fernand     1

Le scénario que je vois ( encore plus simple que mon précédent message ) :

Sur une modification :

Si pas de commande utilisant l'adresse 1
--> on laisse faire.

Si on trouve des commandes utilisant l'adresse 1
--> on insert au lieu de l'update,
--> on met à jour la ligne correspondante dans MAIN_ADRESSE ( si c'est une adresse principale ),
--> on met à jour le flag de l'adresse 1 à true.

Avec ces données Fernand passe commande ( commandeid =1 )
L'adresse 1 est donc utilisée.

Fernand tente de modifier son adresse principale le 1 janvier 2008, selon mon scénario encore, l'application intercepte, constate que l'adresse 1 est utilisée par la commande 1 et insère une nouvelle adresse au lieu d'un update, dans le même temps on passe le flag obsolete de l'adresse 1 à true et on met à jour la ligne de la table MAIN_ADRESSE correspondante.

Résultat :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
ADRESSE

MemberId    AddresseId    line_1                 ville         AdresseDateDebut		obsolete
--------    ---------     -------------------    ---------     ----------------		--------
Fernand     1             105, bd Malesherbes    Paris         2005-04-01		1
Fernand     2             250, av de la Corse    Marseille     2008-01-01		0

MAIN_ADRESS 

MemberId    AddresseId
--------    -----------   
Fernand     2

Avec cette requête on obtient que les adresses courantes:

Code sql :

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

1
2
3
4
5
6
SELECT
  a.line_1 || ' ' || a.ville
FROM
  adresse AS a JOIN membre AS m
    ON a.actorid = m.membreid
WHERE MembrePseudo = 'Fernand' AND obsolete = 0 ;

si on l'exécute avant le 01 janvier 2008 :
l'adresse de Paris
si on l'exécute à partir du 01 janvier 2008 :
l'adresse de Marseille

Un peu lourd en modifications, mais très léger en Select.

Qu'en pensez vous?

La 100è c'est pour vous