Discussion :

[marchand_de_sable]

Bonjour.

Je travail actuellement sur une base issue d’un progiciel (plus précisément, export d’une partie de la base), et je m’interroge sur la nécessité de la remodeler, et surtout COMMENT !!
Le modèle est en grande partie construit comme sur le schéma_1 (en pièce jointe).

Non, non, ce n’est pas un modèle en étoile, et mis à part la forme, on en est loin…
En réalité, ce ne sont pas 5 mais plutôt une 20aine de tables qui « gravitent » autour de PERSONNE.
Pour être plus précis, les données qu’elles contiennent sont de plusieurs types :
- données répétitives fixes (finissant pas RF_x). A une date donnée, une personne peut cumuler plusieurs occurrences dans ces tables. Ce sera par exemple le cas des enfants d’une personne si on se borne à ne s’intéresser qu’à leur existence.
- données uniques historiques (finissant par UH_x).A une personne donnée et à une date entre date_debut et date_fin, ne correspondra qu’1 ou zéro occurrence dans ces tables. Cette information évolue dans le temps et on en garde l’historique. C’est le cas par exemple de la résidence principal d’un individu
- données répétitives historiques (finissant pas RH_x). Hé ben c’est le mix des deux : à un instant T, à une personne peut correspondre 0 ou N occurrences dans ces tables. C’est par exemple les comptes bancaires d’une personne.

Comme vous pouvez le constater, niveau contraintes d’intégrité, ce modèle est plutôt simpliste et un select qui ne relie ces tables que par comparaison de clés croisera toutes les occurrences de toutes les autres tables pour une personne donnée (une sorte de mini produit cartésien).
Je peux donc me retrouver avec une personne vivant dans un appartement avec un enfant qui n’est pas encore né… C’est donc principalement le caractère historique des ces données qui me posent problème.
Il est bien évident que les date_début et date_fin de toutes ces tables ne sont pas forcément égales. Les périodes de validité peuvent se chevaucher ou non.

J’ai attaqué ce problème sous plusieurs angles, notamment dans ce post et celui-là. J’ai abouti à un nouveau schéma (cf. schéma_2 en pièce jointe)
Mais ma table SITUATION comporte 3 millions de lignes et les performances lors des requêtes ne sont vraiment pas au rendez-vous, alors qu’au final, chaque requête n’attaque que 2 ou 3 de ces tables...
Cette solution ne me satisfait pas, et à force de retourner le problème dans tous les sens, mes neurones commencent à disjoncter…

Si quelqu’un à une expérience sur ce type de schéma, merci de me mettre sur la voie. Pour les autres, un regard neuf sur mon problème me ferait le plus grand bien.



[EDIT]Désolé, j'avais oublié d'inséré les liens vers les posts cités [/EDIT]

[JPhi33]

Je ne suis pas un spécialiste du sujet, mais quelque chose me choque dans les tables "satellites". Elles ont toutes un identifiant a-significatif. Il me semble que si elles étaient identifiées par Id_personne + Date_début (UH) ou Id_personne + Date_début + Numéro (RF et RH), les jointures seraient plus performantes, non ? (et exit les clés étrangères !)

Evidemment, dans cette hypothèse, il y aurait un coût supplémentaire pour l'exportation des données du progiciel dans la base. Il faudrait développer des "injecteurs" qui calculent le Numéro pour les RF et RH, et qui contrôlent la cohérence au sein de chaque table. Mais si l'exportation doit s'effectuer à fréquence régulière, ça peut être rentable ; sinon, ce serait à fonds perdus, "one shot" comme disent certains.

JPhi33

[marchand_de_sable]

Bonjour.

Merci pour ta réponse.
Qu'entends-tu par "les jointures seraient plus performantes" ? Tu commentes le schéma_1 ou le schéma_2 ?

En réalité, toutes les tables "satellites" ont des clés de type Id_personne+N°_ligne. Donc une référence directe à la personne + le numéro d'occurence de l'enregistrement pour cette personne (le n° de ligne est évidemment en lien direct avec la date). En réalité, elles ne sont donc pas "a-significative".

J'ai ajouté les Id_table_XX_x dans le schéma joint parce que je ne trouve pas "propre" ce système de clé composée. Je suis encore fraîchement sorti de la fac, et il me semble qu'on conseil en général d'utiliser des clés uniques pour une entité. Mais je peux concevoir que dans la pratique ce ne soit pas si avantageux (si d'autres avis veulent se faire connaître, n'hésitez pas).
Cela dit, j'avoue que d'un point de vu conceptuel, je ne vois pas ce que ça change...
Je vais quand même tenter de modifier mon schéma_2 en remplaçant les clés etrangères id_table_XX_x par les N°_ligne, on ne sait jamais

En fait, je pense que le problème est qu'on ne peux pas raisonner en terme de dates, mais de périodes (entre date_deb et date_fin). Or les périodes d'une table à l'autre et d'un incividu à l'autre ne correspondent pas. Ainsi, pour que l'intégrité soit respectée entre 2 tables satellites, il faudrait que :

Code :

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

1
2
3
t1.Id_personne=t2.Id_personne ET
((t1.date_deb<=t2.date_deb ET t1.date_fin>t2.date_deb)
OU (t1.date_deb>=t2.date_deb ET t1.date_deb<t2.date_fin))

de manière à ce que si la prériode de validité d'une occurence de t1 (respectivement t2) chevauche les périodes de validité de 2 occurences de t2 (resp. t1), alors l'occurrence de t1 soit retournée pour les 2 occurence de t2.
Et j'avoue que pour modéliser ça, j'ai du mal
Je n'ai trouvé que la solution du schéma_2...

[JPhi33]

Qu'entends-tu par "les jointures seraient plus performantes" ? Tu commentes le schéma_1 ou le schéma_2 ?

Oui, pardon, j'ai oublié de préciser que c'était le schéma 1 que je commentais.

En réalité, toutes les tables "satellites" ont des clés de type Id_personne+N°_ligne. Donc une référence directe à la personne + le numéro d'occurence de l'enregistrement pour cette personne (le n° de ligne est évidemment en lien direct avec la date). En réalité, elles ne sont donc pas "a-significative".

D'accord, mais ça ne correspond pas au schéma 1. Dans ce schéma les clés des tables sont "Id_Table_XX_x" et non pas "Id_Personne + N° ligne". Elles sont donc a-significatives par rapport à la table centrale Personne identifiée par "Id_Personne".

J'ai ajouté les Id_table_XX_x dans le schéma joint parce que je ne trouve pas "propre" ce système de clé composée.

C'est ton droit. Mais il faut savoir que passer d'un système d'identification à un autre n'est pas sans conséquences sur les performances des requêtes.

Je suis encore fraîchement sorti de la fac, et il me semble qu'on conseil en général d'utiliser des clés uniques pour une entité.

Oui, tout à fait, il faut une clé unique. Mais "Id_Personne + Date_début + Numéro (pour une même date début)" n'est pas moins unique que "Id_Table_XX_x" pour les tables de type RF et RH.

D'après mes maigres connaissances en bases de données, je crois que les requêtes sont plus performantes avec une identification du type "Id_Personne + Date_début + Numéro" (identification relative) car la jointure élimine d'office les lignes qui ne correspondant pas à l'Id_Personne recherché car il fait partie de l'index, les comparaisons de dates s'effectuent sur les lignes restantes. Dans le cas de "Id_Table_XX_x", toutes les lignes de la table sont examinées (scan table) et les comparaisons sont faites non seulement sur les dates mais aussi sur l'Id_Personne. Je pense que c'est infiniment plus long (à valider par un DBA confirmé).

Cela dit, j'avoue que d'un point de vu conceptuel, je ne vois pas ce que ça change...

Dans le cas d'une modélisation conceptuelle avec un modèle Entité-Association (MDC MERISE), les "Id_Table_XX_x" n'auraient même pas été envisagés.

En fait, je pense que le problème est qu'on ne peux pas raisonner en terme de dates, mais de périodes (entre date_deb et date_fin). Or les périodes d'une table à l'autre et d'un incividu à l'autre ne correspondent pas.

C'est vrai. Mais ça tu n'y peux rien, c'est l'existant et tu dois faire avec. Les requêtes basée sur une date unique "je veux la situation de la personne N°123 le 01/10/2001" seront plus performantes que celles basées sur une période "je veux les enregistrements de la personne N°123 du 01/10/2001 au 15/10/2001". Le principe est similaire dans les deux cas : il faut sélectionner les lignes pour lesquelles la date ou la période recherchée est située dans la période de la ligne de la table.

JPhi33

[marchand_de_sable]

Au sujet des clés : je me suis mal exprimé, j'ai parlé de clé unique alors que j'aurais du dire clé simple, par opposition aux clés composées.

Dans le cas d'une modélisation conceptuelle avec un modèle Entité-Association (MDC MERISE), les "Id_Table_XX_x" n'auraient même pas été envisagés.

J'avoue que ça me surprend énormément. Il me semblait qu'il était toujours conseillé d'utilisé une clé simple et abstraite (sans signification, genre un nombre auto-incrémenté) pour chaque entité. Et ceci, justement dans l'approche MERISE .
Dans un cas typique d'une relation entre COMMANDE et CLIENT, la clé de COMMANDE sera un numéro "quelconque", et on aura la clé etrangère du client dans la commande. Mais pas une clé composée de N°_client+num_ligne... Enfin, d'après ce que j'ai pu lire sur le sujet (notamment dans ce tuto de Cyril Gruau)...

Après, dans la pratique c'est autre chose. Une clé unique aura, j'imagine, l'avantage de simplifier la requête (jointure et clause where moins fournies), et aura une incidence sur les performances. Dans mon cas, je suis plutôt d'accord avec ton idée du fait qu'avec toutes mes tables indexées sur Id_personne, je gagnerais en performance.

Mon problème vient notamment du fait que ma base sera attaquée via un logiciel de reporting par des utilisateurs peu éclairés sur toutes ces questions. A ma charge, donc, de m'assurer que les données qu'ils récupéreront soient correctes. D'où l'idée de me pencher sur le modèle qui s'avère au moins incomplet, si ce n'est incorrect (que ce soit avec clé simple ou composé... est-on d'accord ??).

Je vais donc pousser les tests dans ce sens-là pour améliorer les performances du schéma 2 (le seul à m'assurer des données correctes à l'heure actuelle).

Je reste évidemment preneur de tout conseil sur ces points de modélisation.

[JPhi33]

Au sujet des clés : je me suis mal exprimé, j'ai parlé de clé unique alors que j'aurais du dire clé simple, par opposition aux clés composées.J'avoue que ça me surprend énormément. Il me semblait qu'il était toujours conseillé d'utilisé une clé simple et abstraite (sans signification, genre un nombre auto-incrémenté) pour chaque entité. Et ceci, justement dans l'approche MERISE .
Dans un cas typique d'une relation entre COMMANDE et CLIENT, la clé de COMMANDE sera un numéro "quelconque", et on aura la clé etrangère du client dans la commande. Mais pas une clé composée de N°_client+num_ligne... Enfin, d'après ce que j'ai pu lire sur le sujet (notamment dans ce tuto de Cyril Gruau)...

Puisque tu as lu le tutoriel de Cyril GRUAU, tu as vu que la démarche de modélisation des données avec MERISE suit un ordre qui va du conceptuel au physique (MCD puis MLD puis MPD). On commence donc par le MCD dont l'élaboration a pour objectif de modéliser la réalité du domaine étudié en s'intéressant à la sémantique des informations (données). Lorsqu'on crée une entité COMMANDE, identifiée par un numéro de commande, c'est parce que dans le monde réel du domaine étudié, il existe des données sur les commandes et qu'elles ont un intérêt dans le système d'information. Le fait que le numéro de commande est un numéro "quelconque" (une clé simple et abstraite) n'est pas un choix du concepteur mais la traduction de la réalité. Parce que dans telle entreprise on a choisi de numéroter les commandes comme ça.

Mais il existe des entreprises qui ne choisissent pas ce système. Certaines PME numérotent leurs commandes par rapport au numéro de client (identification relative) :

• commande 1 du client 1,
• commande 2 du client 1,
• commande 3 du client 1,
• commande 1 du client 2,
• etc.

Pour ce cas, ce qu'il faut faire :
l'identifiant de l'entité COMMANDE est la concaténation des deux propriétés "Numéro de client" et "Numéro de commande du client". Traduit dans le MLD : la clé de la table Commande est une clé composée de ces deux attributs.

Ce qu'il ne faut surtout pas faire :
"inventer" un nouvel identifiant unique (clé simple et abstraite, sans signification, genre un nombre auto-incrémenté) pour l'entité COMMANDE, car ce numéro n'a aucune existence dans la réalité et ne véhicule aucune sémantique. Traduit dans le MLD : la clé de la table Commande est ce nouveau numéro, le numéro de commande (le vrai) est un attribut non clé de la table et il y a une clé étrangère "Numéro de client" qui référence la table Client (j'ai déjà expliqué dans mon précédent message les conséquences de ça sur les performances).

Donc, dans la majorité des cas, ce n'est pas le concepteur qui choisit, mais chaque fois qu'il a le choix, alors oui, tu as raison, il vaut mieux identifier avec une clé non composée.
Dans le cas du schéma 1, normalement, tu n'as pas le choix : toutes tes tables font référence à une Personne, elles sont donc toutes identifiée par Id_Personne (+ autre chose pour les tables secondaires).


Pour le schéma 2, je ne comprends pas bien à quoi correspond une occurrence de SITUATION. Surtout, c'est la présence de Date début et Date Fin que je ne saisi pas bien. Une occurrence de SITUATION correspond à une période de temps pour une Personne ? Si oui, comment fais-tu pour calculer cette période ?

[fsmrel]

Bonsoir,

Lorsqu'on crée une entité COMMANDE, identifiée par un numéro de commande, c'est parce que dans le monde réel du domaine étudié, il existe des données sur les commandes et qu'elles ont un intérêt dans le système d'information. Le fait que le numéro de commande est un numéro "quelconque" (une clé simple et abstraite) n'est pas un choix du concepteur mais la traduction de la réalité. Parce que dans telle entreprise on a choisi de numéroter les commandes comme ça.

Si je puis me permettre, en tant que DBA expérimenté...
L’utilisateur choisit ce qu’il veut comme structure pour le numéro de commande, appelons l'attribut correspondant : CommandeNo. Pour le concepteur ça peut être un identifiant, mais pas forcément une clé primaire pertinente du point de vue du DBA qui a la responsabilité du comportement de la base de données. En effet, l’utilisateur a le droit de modifier ses données, il en est le propriétaire. Autrement dit, si {CommandeNo} est clé primaire, sa modification légitime par l’utilisateur peut avoir des conséquences pénalisantes en production (contentions, nombre d’entrées/sorties). Il est nécessaire que l’utilisateur et l’informaticien restent chacun maîtres de leurs données respectives. Autrement dit, la clé primaire doit être la propriété de l’informaticien qui définit en ce sens un attribut — appelons-le CommandeId — qui est invariant : on affecte les valeurs lors des Inserts et ensuite on ne touche plus. Le numéro de commande CommandeNo fait l’objet d’une clé alternée et ceci est transparent pour l’utilisateur. Et surtout, la modification de CommandeNo ne se propage pas dans les autres tables.

Au niveau SQL, on écrira donc :

Code :

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

1
2
3
4
5
6
7
8
9
10
Create Table Commande
(CommandeId    Integer    Not Null,
 CommandeNo      ...      Not Null,
 ...,
Primary Key (CommandeId),
Unique (CommandeNo),
...
) ; 
Create Unique  index CommandeId on Commande (CommandeId) ;
Create Unique  index CommandeNo on Commande (CommandeNo) ;

Ainsi on garantit l’unicité des numéros de commande et la performance des accès à ces numéros par l’utilisateur, qui se moque éperdument de CommandeId dont il ne soupçonne même pas l’existence (on ne lui présente jamais cet attribut).

Mais il existe des entreprises qui ne choisissent pas ce système. Certaines PME numérotent leurs commandes par rapport au numéro de client (identification relative) :
• commande 1 du client 1,
• commande 2 du client 1,
• commande 3 du client 1,
• commande 1 du client 2,
• etc.
Pour ce cas, ce qu'il faut faire :
l'identifiant de l'entité COMMANDE est la concaténation des deux propriétés "Numéro de client" et "Numéro de commande du client". Traduit dans le MLD : la clé de la table Commande est une clé composée de ces deux attributs.

Dans le sens de ce qui précède, l’utilisateur fait les choix qu'il veut et l’informaticien les siens propres. Au niveau SQL, on peut très bien se retrouver avec les instructions suivantes (l’attribut ClientId est la propriété exclusive de l’informaticien, tandis que l’utilisateur peut disposer d’un attribut Numéro de client dans la table Client) :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Create Table Commande
(CommandeId    Integer    Not Null,
 ClientId      Integer    Not Null,
 CommandeNo    Smallint   Not Null,
 ...,
Primary Key (CommandeId),
Unique (ClientId, CommandeNo),
Foreign Key (ClientId) References Client,
...
) ; 
Create Unique  index CommandeId on Commande (CommandeId) ;
Create Unique  index CommandeNo on Commande (ClientId, CommandeNo) ;

Donc, dans la majorité des cas, ce n'est pas le concepteur qui choisit, mais chaque fois qu'il a le choix, alors oui, tu as raison, il vaut mieux identifier avec une clé non composée.

Pour reprendre ce que j’ai écrit, l’utilisateur est roi et il aura toutes les données qu’il veut dans la base de données, avec toutes les garanties d’unicité et autres contraintes qu’il désire. Maintenant, techniquement, l’informaticien fait ses propres choix. Il fait ce qu’il veut avec CommandeId : par exemple le hacher, si en temps réel trop d’utilisateurs saisissent des commandes en même temps et que les contentions fassent stresser la machine.
S’il s’avère que, pour des raisons de performance, le verdict du prototypage de performance conduit à éliminer les attentes trop nombreuses de fin d’entrées/sorties (effet IO/Bound), on aura le loisir, toujours de façon transparente pour l’utilisateur, d’identifier La commande relativement au client si c’est la solution (CommandeId devient un séquenceur relatif à ClientId) :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Create Table Commande
(CommandeId    Smallint    Not Null,
 ClientId      Integer     Not Null,
 CommandeNo    Smallint    Not Null,
 ...,
Primary Key (ClientId, CommandeId),
Unique (ClientId, CommandeNo),
Foreign Key (ClientId) References Client,
...
) ; 
Create Unique  index CommandeId on Commande (ClientId, CommandeId) ;
Create Unique  index CommandeNo on Commande (ClientId, CommandeNo) ;

Concernant le choix des identifiants, un maître en Merise, à savoir Yves Tabourier a écrit :

"Un identifiant ne décrit rien, son rôle est de distinguer deux jumeaux parfaits".

Et il précise qu’un identifiant ne doit donc pas être une propriété naturelle, qu'il y a "abus de langage" (Y. Tabourier. De l’autre côté de MERISE (Les Éditions d’organisation, 1986)).

[marchand_de_sable]

Cela dit, j'avoue que d'un point de vu conceptuel, je ne vois pas ce que ça change...

Le tuto de Cyril Gruau, je l'ai plutôt feuilleté en diagonale, je m'intéressais essentiellement aux données historiques (qu'ils n'abordent que trop rapidement à mon goût). Donc il n'est pas impossible qu'en le citant, j'ai pris quelques raccourci entre le niveau conceptuel et le niveau physique et pratique.

Cela dit, l'intervention de fsmrel me rassure sur ce que je pensais avoir intégré...
En fait, je voulais surtout dire que l'adoption d'une clé ou de l'autre, ne modifiait pas le sens de la relation qui reste :
table_satellite 1-1 -> 0-n personne

Pour le schéma 2, je ne comprends pas bien à quoi correspond une occurrence de SITUATION.

Hé bien, la sémantique de l'information contenue dans SITUATION n'est effectivement pas très claire... C'est pour ça que je n'en suis pas satisfait (mais j'ai pas d'autre solution pour le moment ).

Surtout, c'est la présence de Date début et Date Fin que je ne saisi pas bien. Une occurrence de SITUATION correspond à une période de temps pour une Personne ?

Pas exactement : plusieurs "situations" peuvent être valides pour une même période...de même qu'une situation peut être valide à différentes périodes.

Je ne peux même pas en faire une association dont la clé serait composée des différentes clés etrangères + date_debut, étant donné que les clés etrangères peuvent être nulles (une personne n'a pas forcément été marié, par exemple). Niveau conceptuel, c'est clairement boîteux.

comment fais-tu pour calculer cette période ?

Pour récupérer les date_debut :

Code :

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

1
2
3
4
5
6
7
8
SELECT DISTINCT id_personne, date_modif FROM 
(
SELECT Id_personne, date_debut AS Date_modif FROM TABLE_RH_1
UNION
SELECT Id_personne, date_fin AS Date_modif FROM TABLE_RH_1
UNION
/*la même chose pour toutes les tables concernées*/
)

Un script se charge ensuite de d'inserer la date_fin de l'occurrence N à partir de la date_debut de l'occurrence N+1 (pour une personne donnée, évidemment).
A partir de là, je charge les clés etrangères (ou n° de ligne, ce que je n'ai pas encore testé) selon l'id_personne et les périodes de validités des tables satellites.

Classe, n'est-ce pas ?

[JPhi33]

Dont acte pour le point de vue du DBA.

[fsmrel]

Dont acte pour le point de vue du DBA.

Lequel DBA est en phase avec Yves Tabourier...

[marchand_de_sable]

Dont acte pour le point de vue du DBA.

Lequel DBA est en phase avec Yves Tabourier...

Heuu, personnellement, tous vos avis m'intéressent.
Donc si vous avez des pistes pour moi, surtout n'hésitez pas

[fsmrel]

Heuu, personnellement, tous vos avis m'intéressent.

Well!

J’ai regardé votre message dans le forum Cognos Message Cognos. Je ne connais pas Cognos, mais peu importe.

Je ne m'intéresse pas à la table SITUATION qui semble sortie d'un cauchemar.

Avec cette modélisation et l'utilisation qui en est faite, je me heurte à 3 problèmes.
1) il n'y a aucune contraintes d'intégrité entre ces tables. Autrement dit, lorsque je fais une sélection dans plusieurs de ces tables en même temps, ça donne lieu à un produit cartésien : pour chaque personne, toutes les unions seront croisées avec toutes les adresses, etc., même si les périodes ne correspondent pas. Les résultats sont donc en partis inconsistents.

Par « contrainte d’intégrité », je suppose que vous voulez dire simplement « contrainte ». Quand vous parlez de produit cartésien, cela veut dire, en principe, que vos jointures ne portent que sur l’attribut IdPersonne : par projection du résultat sur IdPersonne et les attributs propres à chaque table participant à la jointure, vous retrouvez la situation initiale, ce qui veut dire que le résultat enfreint la 4e forme normale, mais nous ne sommes pas plus avancés...

Questions :

1 Vos requêtes font-elles mention d’une personne, d’un type de personnes, ou bien raisonne-t-on toutes personnes confondues ? (« Quelque soit la personne, je pose la question Q à la base de données »).

2) L’utilisateur est-il contraint à se limiter à une date de début donnée ? (« Je veux tout à partir de la date D »). En particulier : « Je veux tout ce qui est actif ».

3) L’utilisateur est-il contraint à se limiter à un intervalle de dates ? (« Je veux tout entre D1 et D2 »).

4) Pouvez-vous limiter les ambitions de l’utilisateur (pour ne pas lui ramener des milliers de lignes dont il n’aura que faire : avec un produit cartésien, ça monte vite...)

5) Construisez-vous vos requêtes dynamiquement, à partir de ce que demande l'utilisateur ?

6) Faites-vous dans le 80-20, autrement dit connaissez-vous le top 10 des requêtes ? (à bichonner donc, avec un turbo de compétition).

7) Les dates sont-elles indexées ?

8) Auriez-vous deux ou trois exemples concrets de requêtes ?

9) Quel est le SGBDR dont vous disposez ?

10) Faites-vous des campagnes d’explain ?

Etc.

[marchand_de_sable]

1 Vos requêtes font-elles mention d’une personne, d’un type de personnes, ou bien raisonne-t-on toutes personnes confondues ? (« Quelque soit la personne, je pose la question Q à la base de données »).

Mes requêtes servent en général à identifier une population particulière : "quelles personnes répondent à telle et/ou telle autre et/ou encore telle caractéristiques à telle date de référence"; lesquelles caractéristiques sont définies dans les tables "satellites".
Exemple : Quelles personnes ont un statut juridique particulier : divorcées ou résidents à l'étranger au 31/12/2006 ?

Rq : les "et/ou" indiquent que la plupart des jointures sont externes : si une caractéristique n'existe pas, on s'intéresse quand même aux autres.

2) L’utilisateur est-il contraint à se limiter à une date de début donnée ? (« Je veux tout à partir de la date D »). En particulier : « Je veux tout ce qui est actif ».

La plupart des requêtes portent sur une situation à date donnée (sur ce qui est "actif" ou plutôt "en vigueur" à la date D) , ou, plus rarement, sur une période de référence.
Pour reprendre l'exemple précédent : "Qui a été divorcé et /ou résidé à l'etranger à un moment ou à un autre en 2006 ?"

3) L’utilisateur est-il contraint à se limiter à un intervalle de dates ? (« Je veux tout entre D1 et D2 »).

cf. 2). Il n'y est pas "contraint", c'est plutôt lui qui fixe les dates ou périodes de références (lorsqu'il exprime ses besoins).

4) Pouvez-vous limiter les ambitions de l’utilisateur (pour ne pas lui ramener des milliers de lignes dont il n’aura que faire : avec un produit cartésien, ça monte vite...)

Je ne comprends pas vraiment la question...
a) Justement, j'aimerais me débarasser de ce produit cartésien.
b) Si c'est la question, alors qu'il existe plus de 20 tables "satellites", la plupart des requêtes ne portent que sur 3 ou 4 d'entre elles en même temps (certaines sont plus souvent utilisées que d'autres).
Petite précision : la table SITUATION a pour but de mettre en relation les enregistrements des tables "satellites" et de la table PERSONNE relativement aux périodes de validités desdits enregistrements. Elle comporte 4 à 5 millions de lignes. Effectivement, en n'associant (dans une requête ou une table) que 3 ou 4 tables (en plus de PERSONNE), je ferais de grandes économies sur les lignes ramenées...

5) Construisez-vous vos requêtes dynamiquement, à partir de ce que demande l'utilisateur ?

Oui.

6) Faites-vous dans le 80-20, autrement dit connaissez-vous le top 10 des requêtes ? (à bichonner donc, avec un turbo de compétition).

Oui, mais ça serait plutôt un top 20 ou top 30...

7) Les dates sont-elles indexées

Oui. Mais certaines tables font l'objet de 8 à 10 index différents, portant sur 15 champs en tout. L'optimiseur de requête s'y retrouve-t-il ???

8) Auriez-vous deux ou trois exemples concrets de requêtes ?

L'exemple utilisé en 1) donne une bonne idée, mais les demandes sont bien évidemment plus précise, et sans se plonger dans le fonctionnel (très particulier), il est difficile de donner un exemple parlant.

9) Quel est le SGBDR dont vous disposez ?

Oracle 9.2.

10) Faites-vous des campagnes d’explain ?

Oui, mais je ne sais pas vraiment interpréter les résultats...

Par « contrainte d’intégrité », je suppose que vous voulez dire simplement « contrainte ». Quand vous parlez de produit cartésien, cela veut dire, en principe, que vos jointures ne portent que sur l’attribut IdPersonne : par projection du résultat sur IdPersonne et les attributs propres à chaque table participant à la jointure, vous retrouvez la situation initiale, ce qui veut dire que le résultat enfreint la 4e forme normale, mais nous ne sommes pas plus avancés...

Je n'ai pas vraiment tout saisi...
Les termes "contraintes d'intégrité" sont surement mal choisis. Il n'y a pas à proprement parlé de dépendance fonctionnelle entre TABLE_UH_1 et TABLE_UH_2. Mais il devrait bien y avoir quelque chose, au niveau de la modélisation, qui permet de ne les associer que si leur période de validité se chevauchent (pour une personne donnée). Non ?

Actuellement, j'ai :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
             Id_personne | Ligne|  Date_début |  Date fin
TABLE_UH_1 :        1    |   1  |   01/01/00  |  30/06/00
                    1    |   2  |   01/07/00  |  31/12/00

TABLE_UH_2 :        1    |   1  |   01/08/00  |  31/12/00
                    1    |   2  |   01/01/01  |  31/12/01

SELECT * FROM TABLE_UH_1 t1, TABLE_UH_2 t2 WHERE t1.Id_personne=t2.Id_personne;

Id_Personne | Ligne_UH_1 (validité) | Ligne_UH_2 (validité)  |
    1       | 1 (01/01/00->30/06/00)| 1 (01/08/00->31/12/00) | KO
    1       | 1 (01/01/00->30/06/00)| 2 (01/01/01->31/12/01) | KO
    1       | 2 (01/07/00->31/12/00)| 1 (01/08/00->31/12/00) | OK
    1       | 2 (01/07/00->31/12/00)| 2 (01/01/01->31/12/01) | KO

Je récupère donc 4 ligne là où une seule association ne devrait être possible...

La table SITUATION est effectivement effrayante, mais créer des associations entre chacune des tables prises 2 à 2 serait aussi cauchemardesque (voir plus !), sans résoudre toute la question...

[fsmrel]

Bonsoir,

Citation:
Envoyé par fsmrel
9) Quel est le SGBDR dont vous disposez ?
Oracle 9.2.

Citation:
Envoyé par fsmrel
10) Faites-vous des campagnes d’explain ?
Oui, mais je ne sais pas vraiment interpréter les résultats...

Il faudra venir à l'EXPLAIN. Dès que l’on a des problèmes de performance potentiels (sinon réels), on doit être rodé à leur étude et leur explication.

1) l’optimiseur d’un SGBDR s’appuie sur le contenu du catalogue relationnel pour déterminer sa stratégie : pour chaque table impliquée, le nombre (estimé) de lignes de la table, pour les index connectés, le nombre de clés distinctes, le nombre de niveaux d’index et un tas de données telles que le niveau de désorganisation des tables et index. Ces données ne sont pas calculées en temps réel (le SGBD ne ferait que cela), mais renseignées au moyen d’utilitaires à cet effet. Je ne suis pas spécialiste d’Oracle, mais je pense qu’il procède ainsi et qu’il vous fournit un utilitaire de collecte des statistiques.

2) Vous savez que vos requêtes SQL ne mettent pas en jeu vos algorithmes (le comment), vous procédez de façon déclarative (le quoi) et le SGBDR se charge à son tour du comment. Pour cela, il met à profit le contenu du catalogue relationnel. Si ce contenu est pertinent (données mises à jour par l’utilitaire de collecte des statistiques), il utilisera une stratégie pertinente. Dans le cas contraire (par exemple une table qui selon le catalogue à une cardinalité égale à 2 alors qu’en réalité elle est égale à dix millions), la stratégie risque d’être catastrophique. Vous êtes en droit d'exiger que le SGBDR expose sa stratégie, vous disposez à cet effet de l’instruction EXPLAIN PLAN.

3) Avec une stratégie correcte, les performances peuvent ne pas être excellentes, parce que c’est le foutoir dans les données (désorganisation).
Vous avez là trois points à surveiller en particulier. Pour en savoir plus, consultez le document « Database Performance Tuning Guide and Reference ». Je répète, je ne connais pas Oracle, mais surveiller de près ces trois points est fondamental avec les différents SGBDR. J’ai vu des projets qui ont failli capoter parce que ces points avaient été ignorés.
Au besoin, les DBA vont plus loin grâce à des outils spécialisés.

Citation:
Envoyé par fsmrel
7) Les dates sont-elles indexées ?

Oui. Mais certaines tables font l'objet de 8 à 10 index différents, portant sur 15 champs en tout. L'optimiseur de requête s'y retrouve-t-il ???

Un EXPLAIN vous le dira...

Citation:
Envoyé par fsmrel
Par « contrainte d’intégrité », je suppose que vous voulez dire simplement « contrainte ». Quand vous parlez de produit cartésien, cela veut dire, en principe, que vos jointures ne portent que sur l’attribut IdPersonne : par projection du résultat sur IdPersonne et les attributs propres à chaque table participant à la jointure, vous retrouvez la situation initiale, ce qui veut dire que le résultat enfreint la 4e forme normale, mais nous ne sommes pas plus avancés...
Je n'ai pas vraiment tout saisi...
Les termes "contraintes d'intégrité" sont surement mal choisis. Il n'y a pas à proprement parlé de dépendance fonctionnelle entre TABLE_UH_1 et TABLE_UH_2. Mais il devrait bien y avoir quelque chose, au niveau de la modélisation, qui permet de ne les associer que si leur période de validité se chevauchent (pour une personne donnée). Non ?

Il n’y a aucune dépendance fonctionnelle qui puisse nous intéresser dans ce dont nous parlons. Et les outils de modélisation graphiques seraient bien en peine de vous fournir les moyens d’exprimer la contrainte que vous recherchez. Du point de vue des tables, leur contenu est valide. Les lignes de TABLE_UH_1 et TABLE_UH_2 sont indépendantes. Toutefois, quand vous codez :

SELECT * FROM TABLE_UH_1 t1, TABLE_UH_2 t2 WHERE t1.Id_personne=t2.Id_personne ;

alors vous effectuez un produit cartésien limité à l'attribut Id_Personne, dont le résultat est une table, et c’est pour cela que, par définition, vous violez la 4e forme normale, ce que vous subodorez être affreux (obésité du résultat).

Actuellement, j'ai :

Code :
Id_personne | Ligne| Date_début | Date fin
TABLE_UH_1 : 1 | 1 | 01/01/00 | 30/06/00
1 | 2 | 01/07/00 | 31/12/00

TABLE_UH_2 : 1 | 1 | 01/08/00 | 31/12/00
1 | 2 | 01/01/01 | 31/12/01

SELECT * FROM TABLE_UH_1 t1, TABLE_UH_2 t2 WHERE t1.Id_personne=t2.Id_personne;

Id_Personne | Ligne_UH_1 (validité) | Ligne_UH_2 (validité) |
1 | 1 (01/01/00->30/06/00)| 1 (01/08/00->31/12/00) | KO
1 | 1 (01/01/00->30/06/00)| 2 (01/01/01->31/12/01) | KO
1 | 2 (01/07/00->31/12/00)| 1 (01/08/00->31/12/00) | OK
1 | 2 (01/07/00->31/12/00)| 2 (01/01/01->31/12/01) | KO
Je récupère donc 4 ligne là où une seule association ne devrait être possible...

Et ces 4 lignes sont le résultat obligé du viol dénoncé ci-dessus.
Vous ne pouvez pas vous en sortir si vous ne fournissez pas la contrainte que vous appelez de vos vœux, mais que le SGBD serait bien en peine de deviner, et donc vous devez coder quelque chose comme :

SELECT *
FROM TABLE_UH_1 t1, TABLE_UH_2 t2
WHERE t1.Id_personne = t2.Id_personne
And ( t1.Date_Debut <= t2.Date_Debut And t2.Date_Debut <= t1.Date_Fin
or t2.Date_Debut <= t1.Date_Debut And t1.Date_Debut <= t2.Date_Fin ) ;

(et que vous matérialisez vraisemblablement dans la table SITUATION).

Citation:
Envoyé par fsmrel
5) Construisez-vous vos requêtes dynamiquement, à partir de ce que demande l'utilisateur ?
Oui.

Donc vous êtes à même de traduire exactement la demande de l’utilisateur, ni plus ni moins, sous forme d’une requête construite dynamiquement. Quand on considère votre requête :

« Quelles personnes ont un statut juridique particulier : divorcées ou résidents à l'étranger au 31/12/2006 ? »

Vous construisez dynamiquement une requête qui récupère les personnes pour lesquelles dans la table contenant le statut juridique, celui-ci est égal à « divorcé » à la date indiquée. Mais plutôt que de procéder à une jointure avec la table des adresses dans laquelle on recherche les personnes ayant le statut « résident » à la même date, le OU (inclusif) connectant les résultats peut être effectué en utilisant l’opérateur UNION, plus performant (le résultat R qui est une table, ne comportant que des identifiants de personnes, joint à son tour éventuellement avec les données des personnes, telles que leur nom, autrement dit R peut figurer dans un FROM) :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
  
Select Nom from Personne P, 
             (Select Id_personne
              From   Juridique
              Where  Statut = 'divorcé'
              And    Date...
              UNION
              Select Id_personne
              From   Adresse
              Where  Statut = 'résident'
              And    Date...
             ) As R
Where P.Id_Personne = R.Id_Personne 
;

Une campagne d’EXPLAINS, les réglages en conséquence et un petit prototype de performances devrait vous éclairer à ce sujet.

La jointure devrait être réservée dans le cadre de l’utilisation du connecteur ET. Maintenant si l’emploi de l’opérateur UNION vous complique la vie par rapport à la jointure externe, allez-y avec celle-ci dans la mesure où la performance est acceptable.

La construction de la requête ci-dessus pose-t-elle un problème ?

La table SITUATION est effectivement effrayante, mais créer des associations entre chacune des tables prises 2 à 2 serait aussi cauchemardesque (voir plus !), sans résoudre toute la question...

Le défi dans tout cela est quand même d’essayer d’éviter de mettre en œuvre le monstre qui s’appelle SITUATION.

Courage...

[marchand_de_sable]

Pour ce qui est des stats sur le schéma, elles sont recalculées après chaque chargement (d'après le DBA). L'optimiseur travaille donc avec des infos à jour. Et j'ai effectivement le "Database Performance Tuning Guide and Reference" sous la main.

Du point de vue des tables, leur contenu est valide. Les lignes de TABLE_UH_1 et TABLE_UH_2 sont indépendantes.

Le contenu est certe valide, mais peut-être que les tables elles-mêmes ne devraient pas être créer ainsi..??
La modélisation ne devrait-elle pas permettre d'éviter de récupérer des données "inconsistentes" lors d'une jointure respectant strictement les dépendances fonctionnelles ? En d'autres termes, garantir que les données récupérées ont un sens ?

De plus, il est bien évident que je filtre mes requêtes sur les dates et que je ne me contente pas de la jointure sur Id_personne. Et c'est effecivement ça que j'aimerais mettre "en dur" dans le schéma (en passant actuellement par la table SITUATION). Mais apparemment, vous semblez dire que ce n'est pas forcément possible.

La jointure devrait être réservée dans le cadre de l’utilisation du connecteur ET. Maintenant si l’emploi de l’opérateur UNION vous complique la vie par rapport à la jointure externe, allez-y avec celle-ci dans la mesure où la performance est acceptable.

La construction de la requête ci-dessus pose-t-elle un problème ?

Le requêtage se fait via l'outil Cognos qui génère automatiquement le SQL. Malheureusement, UNION ne fait pas parti de son vocabulaire. Mais la plupart des tables étant petites, les performances restent correctes dans la plupart des cas.

En fait, à terme je ne devrais plus créer moi-même de requête. Je réétudie le modèle des données afin que les utilisateurs puisse les faire eux-mêmes via Cognos. Je suppose que je devrais créer des vues (ou table) selon le top 10 des requêtes de chaque groupe d'utilisateurs....

Courage...

Merci, il m'en faudra... J'avoue qu'à l'heure actuelle, je suis à court d'idée...

En tout cas, merci pour l'attention portée à mon problème.

A bientôt.

[fsmrel]

Bonsoir,

Le marchand de sable va passer. En attendant :

Citation:
Envoyé par fsmrel
Du point de vue des tables, leur contenu est valide. Les lignes de TABLE_UH_1 et TABLE_UH_2 sont indépendantes.
Le contenu est certe valide, mais peut-être que les tables elles-mêmes ne devraient pas être créer ainsi..??
La modélisation ne devrait-elle pas permettre d'éviter de récupérer des données "inconsistentes" lors d'une jointure respectant strictement les dépendances fonctionnelles ? En d'autres termes, garantir que les données récupérées ont un sens ?

Je ne peux que répéter ce que j’ai écrit dans mon premier message. Au stade de la modélisation, vous ne pouvez pas rendre dépendantes des données qui ne le sont pas :
Si une personne a différents statuts juridiques dans le temps, ainsi que des adresses évoluant elles aussi dans le temps, de façon tout à fait indépendante, il n’y a qu’une requête qui permette de constater qu’à telle date elle était divorcée et habitait à l’étranger. Vous ne pouvez rien retrancher à TABLE_UH_1 et TABLE_UH_2, sinon vous perdez de l’information. Du point de vue de la théorie relationnelle, les tables sont normalisées et tout à fait respectables.

Au sujet de l’indépendance
Supposons que, dans une banque, nous ayons une table des coordonnées bancaires des personnes et une table des adresses de ces mêmes personnes. Là aussi ces données sont a priori indépendantes et les deux tables ne sont pas en relation entre elles. Mais, on peut être amené à trouver quand même des dépendances entre ces deux tables et à en créer une troisième qui matérialise les liens permis entre coordonnées bancaires et adresses, afin d’éviter des situations scabreuses du genre : on a envoyé à l’adresse de domiciliation de M. Machin des relevés bancaires concernant un compte occulte, ouvert par M. Machin à l’usage de sa maîtresse et pouvant être lus par Mme Machin. On n’a pas perdu d’information, au contraire on a enrichi le système, parce qu’on a pu injecter dans la base de données une contrainte prévue par le maître d’ouvrage et permettant incidemment d’éviter des situations délicates. Mais tout cela est indépendant du temps.

Pourquoi vous tenez tant aux dépendances fonctionnelles ? Reprenons votre exercice :

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
             Id_personne | Ligne|  Date_début |  Date fin
TABLE_UH_1 :        1    |   1  |   01/01/00  |  30/06/00
                    1    |   2  |   01/07/00  |  31/12/00

TABLE_UH_2 :        1    |   1  |   01/08/00  |  31/12/00
                    1    |   2  |   01/01/01  |  31/12/01

SELECT * FROM TABLE_UH_1 t1, TABLE_UH_2 t2 WHERE t1.Id_personne=t2.Id_personne;

Id_Personne | Ligne_UH_1 (validité) | Ligne_UH_2 (validité)  |
    1       | 1 (01/01/00->30/06/00)| 1 (01/08/00->31/12/00) | KO
    1       | 1 (01/01/00->30/06/00)| 2 (01/01/01->31/12/01) | KO
    1       | 2 (01/07/00->31/12/00)| 1 (01/08/00->31/12/00) | OK
    1       | 2 (01/07/00->31/12/00)| 2 (01/01/01->31/12/01) | KO

Je suppose que vous souhaitez que le résultat R ne comporte qu’une ligne au lieu de quatre et que le système ait la connaissance des DF dans R :

{Id_Personne} -> {Ligne_UH_1}
{Id_Personne} -> {Ligne_UH_2}

Mais, si le SGBD doit faire respecter ces contraintes, parmi les 4 lignes du résultat, il ne trouve que des doublons et doit donc tout rejeter en bloc (ou ne conserver que la 1re ligne insérée ou la dernière, où faire ça à pile ou face...) alors que de votre côté vous avez en tête la règle selon laquelle il faudrait conserver les lignes pour lesquelles les dates se chevauchent, c’est-à-dire la 3e dans votre exemple. Si vous matérialisez le résultat R au sein d’une table UH_1_2, vous pouvez développer un trigger permettant de rejeter les lignes disqualifiées, mais si vous faites un insert de masse, ce trigger devra comporter un curseur pour examen des lignes une par une.
Le problème peut donc être traité ponctuellement, dans la mesure où l’on définit la table UH_1_2. Mais dans votre cas, vous aurez une brouettée de ce genre de tables, ce qui fait que c’est sans doute inexploitable.

Pour la petite histoire, votre résultat comporte deux magnifiques dépendances multivaluées :

{Id_Personne} ->-> {Ligne_UH_1}
{Id_Personne} ->-> {Ligne_UH_2}

ce que l’on écrit encore
{Id_Personne} ->-> {Ligne_UH_1} | {Ligne_UH_2}

En effet, la présence des triplets
<1, 1 (01/01/00->30/06/00), 1 (01/08/00->31/12/00)>
<1, 2 (01/07/00->31/12/00), 2 (01/01/01->31/12/01)>

implique la présence des triplets
<1, 1 (01/01/00->30/06/00), 2 (01/01/01->31/12/01)>
<1, 2 (01/07/00->31/12/00), 1 (01/08/00->31/12/00)>

Et comme le déterminant {Id_Personne} de ces DM n’est pas clé candidate, c’est pour cela que vous violez la 4NF...

Le requêtage se fait via l'outil Cognos qui génère automatiquement le SQL. Malheureusement, UNION ne fait pas parti de son vocabulaire

What ????

Je rappelle que pour être complet au plan relationnel, un langage est astreint à fournir l’opérateur UNION. Vous ne féliciterez pas Cognos de ma part. C’est quand même la base. Mais les constructeurs et les éditeurs font parfois montre d’une très grande désinvolture.
Ainsi, j’ai connu cette situation très inconfortable avec la machine base de données Teradata en 1993 : pour m’en sortir, j’exportais sous forme de fichiers plats le résultat de chaque Select (5 minutes en parallèle), le système d’exploitation MVS concaténait les fichiers pour n’en faire qu’un (durée tendant vers zéro) et j’importais le résultat (10 minutes). Mon UNION était effectuée dans un temps plus honorable, mais dans quelles conditions... Sinon, il fallait en passait par un OR qui dégénérait en produit cartésien, avec un temps de traitement estimé de l’ordre de 10^18 secondes.
J’ai aussi connu cette situation avec l’AGL COOL:GEN. J’ai pu contourner la difficulté en by-passant l’AGL et en développant un programme hébergeant les requêtes à UNION. Sinon, on repartait avec des OR, des produits cartésiens et des temps de traitement à 10^18 secondes.

En fait, à terme je ne devrais plus créer moi-même de requête. Je réétudie le modèle des données afin que les utilisateurs puisse les faire eux-mêmes via Cognos

De grâce, n’oubliez pas de mettre en place un système qui surveille les délires des utilisateurs et tue les tâches trop consommatrices. Par exemple, si l’un d’eux s’amuse à rechercher toutes les personnes dont le nom commence par la lettre "M", il va ramener la moitié de la base de données alors qu’humainement, il n’arrivera même pas à exploiter un résultat de quelques centaines de lignes.

Vous marchez sur des œufs...

[Brett]

Bonjour,

fsmrel
De grâce, n’oubliez pas de mettre en place un système qui surveille les délires des utilisateurs et tue les tâches trop consommatrices. Par exemple, si l’un d’eux s’amuse à rechercher toutes les personnes dont le nom commence par la lettre "M", il va ramener la moitié de la base de données alors qu’humainement, il n’arrivera même pas à exploiter un résultat de quelques centaines de lignes.

J'ai été confronté à ce genre de pb, que j'avais contourné de la façon suivante :
pour chaque requête créée par l'utilisateur je commençais par lui fournir un count de sa requête avec message du genre : 200 enregistrements trouvés. Voulez-vous continuer ? Oui Non.
Ca marchait très bien. Le seul problème est venu d'un utilisateur qui répondait systématiquement... NON et disait "ça marche pas ça me ramène rien"