Discussion :

[Nerva]

Bonsoir.

J'ai créé une requête à partir du QBE de LO Base (puis modifiée ensuite à la main). Elle est fonctionnelle mais il me semble qu'elle pourrait être optimisée par des jointures.

Son utilité est la suivante...

Un client a une adresse "normale" (dite de facturation) et éventuellement une adresse de livraison. Ces adresses sont enregistrées dans une table T_ADRESSES et leur type dans une table TR_ADRESSES_TY (ID 1 = facturation, ID 2 = livraison). Dans la table T_CLIENTS j'ai une colonne CLI_LIV (SMALLINT, 0 ou 1 autorisés) qui définit si le client a une adresse de livraison. Je sais que ça fait sûrement double emploi mais je n'ai pas encore trouvé comment faire autrement. Je pars du principe que le client se fait toujours livré à la même adresse et c'est pourquoi je n'ai pas choisi de placer cette colonne dans la facture.

Le but de la requête est de sélectionner la ligne d'adresse de livraison quand celle-ci existe...

Structure de la base, très simplifiée :

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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
CREATE TABLE T_CLIENTS (
	CLI_ID INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,
	CLI_NOM VARCHAR (32) NOT NULL,
	CLI_PRENOM VARCHAR (32) NOT NULL,
	CLI_LIV SMALLINT NOT NULL,
	CONSTRAINT PK_CLI_ID PRIMARY KEY (CLI_ID),
	CONSTRAINT CC_CLI_LIV CHECK (CLI_LIV BETWEEN 0 AND 1)
);
 
CREATE TABLE TR_LOCALITES (
	LOC_ID INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,
	LOC_CP CHAR (5) NOT NULL,
	LOC_LIB VARCHAR (32) NOT NULL,
	CONSTRAINT PK_LOC_ID PRIMARY KEY (LOC_ID),
	CONSTRAINT UC_LOC_CP_LIB UNIQUE (LOC_CP, LOC_LIB),
	CONSTRAINT CC_LOC_CP CHECK (CAST (LOC_CP AS INTEGER) BETWEEN 01000 AND 97680)
);
 
CREATE TABLE TR_ADRESSES_TY (
	ADT_ID INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,
	ADT_LIB CHAR (24) NOT NULL,
	CONSTRAINT PK_ADT_ID PRIMARY KEY (ADT_ID)
);
 
INSERT INTO TR_ADRESSES_TY (ADT_ID, ADT_LIB) VALUES (1, 'Adresse facturation');
INSERT INTO TR_ADRESSES_TY (ADT_ID, ADT_LIB) VALUES (2, 'Adresse livraison');
 
CREATE TABLE T_ADRESSES (
	ADR_ID INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,
	ADT_ID INTEGER NOT NULL,
	CLI_ID INTEGER NOT NULL,
	ADR_LIB VARCHAR (48) NOT NULL,
	LOC_ID INTEGER NOT NULL,
	CONSTRAINT PK_ADR_ID PRIMARY KEY (ADR_ID),
	CONSTRAINT FK_ADR_ADT FOREIGN KEY (ADT_ID) REFERENCES TR_ADRESSES_TY (ADT_ID),
	CONSTRAINT FK_ADR_PER FOREIGN KEY (CLI_ID) REFERENCES T_CLIENTS (CLI_ID),
	CONSTRAINT FK_ADR_LOC FOREIGN KEY (LOC_ID) REFERENCES TR_LOCALITES (LOC_ID)
);
 
CREATE TABLE T_FACTURES (
	FAC_ID INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
	CLI_ID INTEGER NOT NULL,
	FAC_DATE DATE DEFAULT CURRENT_DATE NOT NULL,
	FAC_MNT DECIMAL (6,2) NOT NULL,
	CONSTRAINT PK_FAC_ID PRIMARY KEY (FAC_ID),
	CONSTRAINT FK_FAC_CLI FOREIGN KEY (CLI_ID) REFERENCES T_CLIENTS (CLI_ID)
);

Requête :

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
SELECT
T_FACTURES.FAC_ID,
T_FACTURES.FAC_DATE,
T_FACTURES.CLI_ID,
T_CLIENTS.CLI_NOM,
T_CLIENTS.CLI_PRENOM,
T_ADRESSES.ADR_ID,
T_ADRESSES.ADT_ID,
TR_ADRESSES_TY.ADT_LIB,
CASE WHEN T_CLIENTS.CLI_LIV = 0 THEN TR_ADRESSES_TY.ADT_ID = 1 ELSE TR_ADRESSES_TY.ADT_ID = 2 END AS CLI_TEST,
T_ADRESSES.ADR_LIB,
TR_LOCALITES.LOC_CP,
TR_LOCALITES.LOC_LIB,
T_CLIENTS.CLI_LIV
FROM
T_ADRESSES,
T_CLIENTS,
T_FACTURES,
TR_ADRESSES_TY,
TR_LOCALITES
WHERE
T_ADRESSES.CLI_ID = T_CLIENTS.CLI_ID AND T_FACTURES.CLI_ID = T_CLIENTS.CLI_ID AND T_ADRESSES.ADT_ID = TR_ADRESSES_TY.ADT_ID AND T_ADRESSES.LOC_ID = TR_LOCALITES.LOC_ID AND CLI_TEST = 1
ORDER BY
T_FACTURES.FAC_ID ASC

Merci.

[escartefigue]

Bonjour,

Puisque l'adresse de livraison est facultative, il faut utiliser une jointure externe, sans quoi la requête ne sélectionnera que les cas où la ligne adresse de livraison est présente.

Pour coder correctement les jointures, il faut utiliser l'opérateur JOIN et pour une jointure externe, c'est plus précisément [LEFT/RIGHT/FULL] OUTER JOIN qu'il faut utiliser.

J'ai alimenté vos tables avec un jeu d'essai de 3 clients, dont deux ayant à la fois une adresse de facturation et de livraison et un qui n'a qu'une adresse de facturation (puisque celle-ci est obligatore).
Voici les requêtes de création de ce jeu d'essai :

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
insert into T_CLIENTS(CLI_NOM, CLI_PRENOM, CLI_LIV)
values ('Lagaffe', 'Gaston', 0)
     , ('Lampion', 'Séraphin', 1)  
     , ('Talon', 'Achile', 0)
;
insert into  TR_LOCALITES (LOC_CP, LOC_LIB)
values ('13000', 'Marseille')
     , ('29000', 'Quimper')
     , ('29600', 'Morlaix')
     , ('31000', 'Toulouse')
;
insert into  T_ADRESSES (ADT_ID, CLI_ID, ADR_LIB, LOC_ID)
values (1, 1, '13 rue de la facturation', 1)
     , (2, 1, '13 bis rue de la livraison', 1)  
     , (1, 2, '29 rue de la facture de Bretagne', 2)  
     , (2, 2, '29 rue de la livraison de Bretagne', 3)    
     , (1, 3, '31 rue de la facture de Haute Garonne', 4)  
; 
insert into T_FACTURES (CLI_ID, FAC_DATE, FAC_MNT)
values (1, '2022-05-06', 1300.13)
     , (2, '2022-05-17', 2900.29)
     , (3, '2022-05-18', 3100.31)
     , (1, '2022-05-29', 1313.13)
;

Et la requête qui affiche l'adresse de livraison si elle existe, celle de facturation sinon :

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
SELECT FAC.FAC_ID
     , FAC.FAC_DATE
     , FAC.CLI_ID
     , CLI.CLI_NOM
     , CLI.CLI_PRENOM
     , COALESCE(ADL.ADR_LIB, ADF.ADR_LIB) as adresse
     , LOC.LOC_CP
     , LOC.LOC_LIB
FROM T_FACTURES as FAC
inner join T_CLIENTS  as CLI
   on CLI.CLI_ID=FAC.CLI_ID
inner join T_ADRESSES as ADF -- adresse de facturation obligatoire
   on ADF.CLI_ID=CLI.CLI_ID
  and ADF.ADT_ID=1  
LEFT OUTER JOIN T_ADRESSES as ADL -- adresse de livraison facultative
   on ADL.CLI_ID=CLI.CLI_ID  
  and ADL.ADT_ID=2
INNER JOIN TR_LOCALITES as LOC   
   on LOC.LOC_ID=COALESCE(ADL.LOC_ID, ADF.LOC_ID)
ORDER BY FAC.FAC_ID ASC

Et son résultat :

FAC_ID	FAC_DATE	CLI_ID	CLI_NOM	CLI_PRENOM	adresse	LOC_CP	LOC_LIB
1	2022-05-06	1	Lagaffe	Gaston	13 bis rue de la livraison	13000	Marseille
2	2022-05-17	2	Lampion	Séraphin	29 rue de la livraison de Bretagne	29600	Morlaix
3	2022-05-18	3	Talon	Achile	31 rue de la facture de Haute Garonne	31000	Toulouse
4	2022-05-29	1	Lagaffe	Gaston	13 bis rue de la livraison	13000	Marseille

[Séb.]

Je pars du principe que le client se fait toujours livré à la même adresse

C'est une grosse erreur à mon sens.
Avec ton modèle, au premier changement d'adresse tu ne sais plus quelles ont été les adresses de facturation/livraison des anciennes commandes, ce qui est impensable.
Pour chaque commande le client doit spécifier l'adresse de facturation et de livraison. Sinon trop de malentendus possibles et pas d'historique.
Quitte à alléger la tâche, tu peux paramétrer des adresses par défaut.

Je ferais quelque chose comme :

clients
---------------
id
name
default_shipping_address_id (FK => addresses.id) (adresse de livraison par défaut du client)
default_billing_address_id (FK => addresses.id) (adresse de facturation par défaut du client)
...

addresses (le carnet d'adresses des clients, il en faut au moins 1 par client)
---------------
id
city_id (FK => ...)
...
client_id (FK => clients.id)

invoices (les factures)
---------------
id
created_at
...
client_id (FK => clients.id)
shipping_address_id (FK => addresses.id) (adresse de livraison choisie par le client pour cette commande, obligatoire)
billing_address_id (FK => addresses.id) (adresse de facturation choisie par le client pour cette commande, obligatoire)

Ensuite pour récupérer adresses de facturation et livraison, le problème ne se pose plus :

Code :

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

1
2
3
4
5
SELECT ALL ...
FROM invoices AS i
INNER JOIN clients AS c ON i.client_id = c.id
INNER JOIN addresses AS sa ON i.shipping_address_id = sa.id
INNER JOIN addresses AS ba ON i.billing_address_id = ba.id

[Nerva]

Escartefigue
Même si elle est mal conçue, la requête est fonctionnelle ; dans ma base de test j'ai 20 clients avec une facture chacun (j'ai volontairement facturé les 3 adresses de livraison à la fin pour les séparer visuellement) :



Mais comme je le supposais, c'est avec des jointures qu'il faut l'optimiser.

Seb
Effectivement, c'est bien plus cohérent mais je cherche à ce que la commodité d'une telle requête puisse être appliquée de différentes manières, pas seulement à des adresses facturation/livraison qui peuvent varier d'une commande à l'autre.

Cas pratique : chaque mois, une personne âgée est facturée pour tel ou tel service. Elle peut s'occuper de sa paperasse elle-même ou avoir un contact (un tuteur) qui s'en occupe. Dans ce cas, il faut préciser dans la table si oui ou non elle est sous tutelle afin que l'adresse de correspondance soit celle qui soit inscrite sur la facture papier, pour que La Poste l'aiguille à la dite adresse de correspondance. Dans un tel cas, il est quasiment impossible que l'adresse varie d'une facture à l'autre.

---

Merci déjà pour ces réponses. Je vais voir tout ça de près...

[escartefigue]

Dans la table T_CLIENTS j'ai une colonne CLI_LIV (SMALLINT, 0 ou 1 autorisés) qui définit si le client a une adresse de livraison. Je sais que ça fait sûrement double emploi mais je n'ai pas encore trouvé comment faire autrement.

Effectivement c'est le genre de choses à ne pas faire, car il peut y avoir incohérence entre l'indicateur de la table T_CLIENTS et la présence ou pas d'une adresse de livraison dans la table T_ADRESSES
La solution c'est la jointure externe (OUTER JOIN) telle que je l'ai utilisée dans ma requête

Mais surtout, il ne faut pas concevoir la base de données en réfléchissant aux tables, il faut, dans l'ordre

1. collecter les règles de gestion (par exemple RG01 : un client possède une et une seule adresse de facturation ; RG02 : un client peut avoir zéro à plusieurs adresses de livraison, etc.)
2. établir le modèle conceptuel des données (MCD) à partir de ces règles de gestion
3. dériver le modèle tabulaire à partir de ce MCD en fonction du choix du SGBD, ça se fait en un clic avec la plupart des logiciels de modélisation

Commencer par les tables, c'est s'engouffrer dans des impasses qu'on paye cash plus tard et sur la durée : ce qui coute le plus cher c'est de reconsidérer une base de données mal conçue, car il faut non seulement refaire la base de données, mais aussi les traitements.

Note : l'usage pour nommer les tables est plutôt le singulier. On se doute bien que dans la table T_CLIENT il y a plusieurs clients

[Nerva]

Je n'ai jamais vraiment compris pourquoi définir le nom des tables au singulier alors que leur contenu est toujours pluriel. Par exemple quand on parle d'un fichier clients (et surtout quand on l'écrit), on le fait bien au pluriel. Bref...

J'ai ultra simplifié les tables d'une base exemple afin d'alléger au maximum. Mais j'ai le même problème dans une base concernant effectivement des bénéficiaires et des tuteurs. Je n'affirmerai pas qu'elle a été conçue dans les règles de l'art mais je pense qu'elle tient quand même bien la route :



À la place d'adresse de facturation et de livraison, c'est adresse principale et adresse de correspondance (mais le principe est le même). La colonne BEN_COR indique si oui ou non le bénéficiaire est sous tutorat.

Dans l'idéal : se passer d'adresse de correspondance (qui dans ce cas est celle du tuteur) et rapatrier l'adresse du tuteur pour l'insérer dans la facture papier (pour tout ce qui est facturation, gestion, etc, ça n'a aucune importance que l'adresse soit indiquée, mais c'est la requête d'établissement de la facture papier qui est problématique). Mais là c'est bien trop complexe pour mon niveau...

[escartefigue]

Le modèle que vous présentez n'est pas un MCD mais un modèle tabulaire, le modèle tabulaire c'est le comment, le MCD c'est le quoi, c'est lui qui doit être validé en fonction des règles de gestion validées par la maîtrise d'ouvrage. Le modèle tabulaire, MLD ou MPD, n'est qu'une conséquence d'un MCD validé.
EDIT : ce graphisme me fait penser à un modèle access, ce ne serait pas surprenant, Access comme MySQL workbench ne permettent pas de réaliser un modèle conceptuel. C'est très dommage. Mais on peut contourner cette limitation en utilisant un logiciel Adhoc, il en existe des gratuits dont certains sont excellents.


En tout cas, ici aussi on a la même redondance et le même risque d'incohérence que celui que j'ai déjà signalé pour les clients et les adresses :
l'indicateur BEN_COR est inutile, c'est la présence d'une occurrence de tuteur en cours de validité (à vérifier dans la table associative TJ_beneficiaires_tuteurs) qui devrait déterminer le tutorat. Rien de plus.
Il est probable que les tuteurs et les bénéficiaires soient des sous-types de personnes (héritage), ils devraient donc être identifiés par l'identifiant de la personne.
Si vous avez besoin d'aide sur la modélisation de la BDD, il faut passer par le forum consacré à ce sujet et qui se trouve ICI
Attention, si la localité est une ville, alors elle peut avoir plusieurs codes postaux (cas des villes à arrondissements)

Pour le nom des tables, c'est juste une simplification : il est inutile d'ajouter un caractère "S" ou "X" au nom de la table, on sait que toute table contient potentiellement plusieurs lignes. La justification est surtout que quand on commence par modéliser au niveau conceptuel, ce qui est fortement recommandé pour éviter les erreurs, on s'intéresse aux types d'entité, aux acteurs. Ces acteurs sont "le client", "la commande", "le produit" etc. D'où le singulier, qu'on retrouve par défaut dans les tables qui en découlent. Mais rien n'interdit de modifier les noms au niveau tabulaire bien entendu.

[Nerva]

C'est l'affichage des relations de LO Base (celui d'Access est bien plus esthétique ! ).

Bénéficiaires et tuteurs sont bien des sous-types de personnes.

- 1 bénéficiaire peut avoir 0 ou 1 tuteur.
- 1 tuteur peut avoir 1 ou plusieurs bénéficiaires (sinon il n'existe évidemment pas).

C'est un modèle relativement ancien qui n'avait pas été fait à partir d'un MCD.

[escartefigue]

Pas certain que bénéficiaire et tuteurs doivent être des sous-types de personnes, ils n'ont aucun attribut spécifique.
Je pense qu'il s'agit plutôt d'une association réflexive de l'entité-type personne : une personne est facultativement liée à une autre personne, la première est le tuteur, l'autre le bénéficiaire.


Pour bien comprendre l'importance de la phase conceptuelle, voici un exemple qui porte uniquement sur ce sous-ensemble personne/bénéficiaire/tuteur.

Deux cas de figure :

• premier cas, le plus simple, il n'y a pas besoin de gérer la date de début et de fin de tutorat
• deuxième cas, plus complexe, on a besoin de pouvoir changer le tuteur d'un bénéficiaire

Dans les deux cas, on a comme règle qu'un bénéficiaire ne peut avoir à un instant "t" qu'un seul tuteur et qu'un tuteur peut tutorer plusieurs bénéficiaires.
Dans le deuxième cas, on a besoin d'une entité-type "calendrier" qui participera à l'association pour permettre d'avoir successivement plusieurs tuteurs pour un même bénéficiaire. C'est une entité-type dite fictive : elle ne deviendra pas une table, ce serait inutile (d'où les parenthèses autour de son nom)


Voici les deux MCD correspondant



Dans le 1^er, c'est la cardinalité maximale de 1 qui permet de respecter la règle selon laquelle on ne peut avoir au plus qu'un tuteur
Dans le 2^e, c'est la flèche de TU_tutorer_1 vers PE_PERSONNE_1 qui matérialise cette contrainte.


Ici, j'ai utilisé le logiciel LOOPING (gratuit), qui permet, en un clic, d'obtenir les modèles tabulaires correspondants :



Et j'obtiens automatiquement le script qui suit (ici j'ai choisi le SGBD DB2, car la syntaxe de vos identifiant est très proche de celle de DB2) :

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
CREATE TABLE PE_PERSONNE(
   PE_ident INT GENERATED BY DEFAULT AS IDENTITY,
   PE_nom VARCHAR(50) NOT NULL,
   PE_prenom VARCHAR(50) NOT NULL,
   PE_ddn DATE NOT NULL,
   PRIMARY KEY(PE_ident)
);
 
CREATE TABLE PE_PERSONNE_1(
   PE_ident INT GENERATED BY DEFAULT AS IDENTITY,
   PE_nom VARCHAR(50) NOT NULL,
   PE_prenom VARCHAR(50) NOT NULL,
   PE_ddn DATE NOT NULL,
   PRIMARY KEY(PE_ident)
);
 
CREATE TABLE TU_tutorer(
   PE_ident_benef INT,
   PE_ident_tuteur INT NOT NULL,
   PRIMARY KEY(PE_ident_benef),
   FOREIGN KEY(PE_ident_benef) REFERENCES PE_PERSONNE(PE_ident),
   FOREIGN KEY(PE_ident_tuteur) REFERENCES PE_PERSONNE(PE_ident)
);
 
CREATE TABLE TU_tutorer_1(
   PE_ident_benef INT,
   CA_date DATE,
   TU_dtfin DATE NOT NULL,
   PE_ident_tuteur INT NOT NULL,
   PRIMARY KEY(PE_ident_benef, CA_date),
   FOREIGN KEY(PE_ident_benef) REFERENCES PE_PERSONNE_1(PE_ident),
   FOREIGN KEY(PE_ident_tuteur) REFERENCES PE_PERSONNE_1(PE_ident)
);

On voit bien ici qu'avec des règles de gestion claires, le MCD s'établit facilement, et le MLD et le script de création des tables s'obtiennent en un clic

Comme on s'est largement éloignés du sujet initial et qu'il s'agit d'une nouvelle question, il serait préférable d'ouvrir une discussion dans le forum consacré si vous souhaitez vérifier plus loin la pertinence de votre deuxième modèle

[Nerva]

Je vous remercie pour le temps passé mais il ne faut pas accorder aux tuteurs plus d'importance qu'ils n'en ont. Ces tuteurs, qui finalement auraient dû être nommés contacts ou correspondants, n'ont qu'une utilité au sein de l'application : fournir une adresse pour l'envoi des factures. Quelle que soit leur implication avec le bénéficiaire, au sein de l'application, ça n'a strictement aucune importance ou utilité.

Vous abordez le fait qu'un bénéficiaire puisse changer de tuteur au fil du temps. C'est exact. Comme il n'y a aucun besoin d'historisation ou autre, un changement de tuteur impliquera juste un changement de nom et d'adresse dans la fiche en conservant donc le même PER_ID.

Il n'y a effectivement aucun besoin de connaître la date d'entrée et de sortie du tuteur. Pour le bénéficiaire, la date de sortie correspond généralement à un placement ou à un décès, utile pour définir si il est actif ou non. J'avais tout simplement choisi de situer ces dates dans la table des personnes afin d'éviter la redondance de colonnes.

Quoi qu'il en soit, je crois que je vais également utiliser pour cette base la même méthode (et requête) que vous avez donnée pour l'adresse de facturation et de livraison. Ça fera parfaitement le job.

Merci encore.