Discussion :

[pfortin]

Bonjour,

Je travaille sur un sujet plutôt simple à exposer mais générateur de discorde (type ORM impedence mismatch...).

Il s'agit de modéliser la gestion d'une valeur qui change au fil du temps.
C'est un historique qu'on souhaite consulter pour connaitre la valeur à une date donnée (appelée date de consultation).

Convention :
- La valeur est attachée à un parent que nous appellerons Entité
- Ce parent est identifié par un Id_Entite

Règles :
- Une valeur est valide pour un parent (ici Entité) sur une période (début, fin)
- Une entité peut avoir de 1 à N valeurs mais 1 et 1 seule par période
- Une période dure au moins une journée
- Les périodes se suivent exactement (pas de période sans valeur)
- Les périodes ne se recouvrent pas (ni inclusion ni chevauchement)
- Les périodes vont de "l'infini avant" jusqu'à "l'infini après"

Bon, ceci n'est que ma retranscription de plusieurs pages de spécifications en français mais je pense que tout y est.
Néanmoins, l'interprétation reste possible sur différents points.

Partant de là, je soumets à vos aimables critiques les MCD, MLD joints.

Les questions qui se posent :
- la relation vers la période suivante est-elle désirable ?
- faut-il stocker date de début ET date de fin (debut_suiv) ?
- sachant qu'une date peut changer (correction), faut-il introduire une surrogate key ?
- peut-on accepter le null dans les dates ?

* question subsidiaire : Y a-t-il des préconisations issues de la 6NF (que je ne connais que de nom) ?

* question bonus : comment convaincre un analyste-concepteur UML que la base doit aussi implémenter des contraintes métiers ? (bon OK je dévie là)

Voilà ! A vos plumes !
Et merci d'avance pour vos commentaires.

Cordialement,

Patrick

[pfortin]

J'ajoute 2 modélisations alternatives histoire d'alimenter la discussion.
- MCD2/MLD2 : Sans la relation de précédence
- MCD3/MLD3 : Avec une "surrogate key" (je ne retrouve plus le terme conceptuel, s'il existe...) et la relation basée sur l'identifiant alternatif

[fsmrel]

Bonjour,


J'ai rédigé ce qui suit avant de voir votre 2e message. Je regarderai celui-ci plus tard.

Je travaille sur un sujet plutôt simple à exposer mais générateur de discorde (type ORM impedence mismatch...).

Au moins ça occupe...

peut-on accepter le null dans les dates ?

Vous connaissez le point de vue des Relationlanders : le bonhomme Null est définitivement interdit de séjour.

A ce propos, dans votre MLD, l’attribut Valeur doit être déclaré NOT NULL (table Ent_Dap).

la relation vers la période suivante est-elle désirable ?

La réponse est négative dans la mesure où la date de fin de la dernière période n’est pas connue, auquel cas le bonhomme Null ne manquerait pas de se manifester.

faut-il stocker date de début ET date de fin (debut_suiv) ?

Si la dernière période est connue (donc la date de fin qui la caractérise), c’est ce que l’on doit faire en théorie (cf. Temporal Data and The Relational Model), mais on doit disposer du générateur de type INTERVAL. A défaut, ce type est à définir soi-même, ce qu’il faut faire quand votre SGBD vous le permet, conformément à la norme SQL. Mais si, par exemple, vous utilisez SQL Server, alors you can always run...

Supposons que vous ne puissiez pas disposer d’un type PERIODE (défini disons à l’aide du générateur INTERVAL).

Si la dernière période est connue (c'est-à-dire la date de fin), votre MLD convient, mais à condition de faire la chasse au bonhomme Null (attribut Debut_Suiv à déclarer NOT NULL). En outre, il faut déclarer une clé alternative {Id_Entite, Debut_Suiv}, sinon on pourrait trouver un tuple <e1, 2011-01-01, 2011-03-01, valeur1> et un autre tuple <e1, 2011-05-01, 2011-03-01, valeur2>. Le support commis par Darwen et auquel je vous renvoie décrit les concepts qui doivent être pris en compte par les SGBD et ayant pour objet qu'ils nous déchargent de l’intégrité des données. A défaut, à vous d’assumer. Par exemple, s’assurer du non chevauchement des périodes, de leur continuité stricte (l’auto-référence de votre MLD devant disparaître) : il y a donc des triggers en perspective...

Si la date de fin n’est pas connue, la date de début (attribut Debut) et la valeur (attribut Valeur) doivent en théorie faire partie de la structure de la table ENTITE. On est alors dans la logique du « SINCE/DURING » dont fait état Darwen : depuis tel jour (SINCE), la valeur est celle-ci. Durant (DURING) les périodes qui précédèrent, les valeurs furent celles-là (aspect historique des données).

Toujours si la date de fin n’est pas connue, vous pouvez aussi faire l’économie de l’attribut Debut_Suiv. L’inconvénient est que pour déterminer la date de fin d’une période dont la date de début est d1, il faut mettre en œuvre une requête impliquant une thêta-jointure qui permette de retrouver dans la table Ent_Dap la plus petite des dates supérieures à d1. En contrepartie, pas de clé alternative et pas de triggers de contrôle du recouvrement des périodes, etc.

sachant qu'une date peut changer (correction), faut-il introduire une surrogate key ?

Stricto sensu, oui puisqu’en principe une valeur de clé primaire est de préférence invariante. Mais le respect de l’invariance vaut fondamentalement quand une clé peut être référencée par une clé étrangère.

Une fois encore, je reprends ici ce que j’ai écrit à diverses reprises :

Les concepteurs d’un projet d’une grande banque avaient retenu le numéro Siren des entreprises pour identifier celles-ci (attribut NoSiren de l’entité-type Entreprise). Au niveau SGBD, par le jeu des liens inter-tables (clé primaire - clé étrangère), le numéro Siren se propageait dans de nombreuses tables. Or, ce numéro est fourni par l’INSEE, lequel envoyait tous les mois les correctifs modifiant le Siren des nouvelles entreprises (10% d’entre elles à peu près). Les concepteurs en avaient tenu compte et me montrèrent le modèle correspondant à la mise à jour des tables impliquées : une usine à gaz ! J’avais fait observer que, vu le nombre de tables touchées et leur volumétrie (plusieurs millions de lignes chacune), cela pouvait faire exploser la production informatique (batchs de nuit), du fait d’une activité de mise à jour excessive et en plus, délicate à ordonnancer. Sans que j'ai eu à le leur demander, les concepteurs définirent une nouvelle propriété, non porteuse d’information, artificielle et invariante, destinée à être l’attribut composant la clé primaire de la table Entreprise, propagé en conséquence dans les autres tables, en lieu et place de l’attribut NoSiren. A partir de là, modifier un numéro de Siren n’impactait plus que la seule table Entreprise, les utilisateurs ayant bien évidemment toujours accès au contenu de l’attribut NoSiren, devenu clé alternative (et n’ayant donc pas perdu sa propriété d’unicité).

Si donc vos dates ne font pas (ou ne feront pas) l’objet de références par d’autres tables, inutile de définir une clé supplémentaire. Si vous craignez que cela puisse se produire, définissez cette clé.

question subsidiaire : Y a-t-il des préconisations issues de la 6NF (que je ne connais que de nom) ?

Que oui ! Mais en fait, c’est la 6NF elle-même qui est quelque part la conséquence des « Nine requirements » décrits dans le support de Darwen.

question bonus : comment convaincre un analyste-concepteur UML que la base doit aussi implémenter des contraintes métiers ?

Les contraintes doivent faire partie de la base de données, coller aux données elles-mêmes et ne pas être éparpillées dans des programmes qui en sont éloignés au sein de différentes couches. Je pense que SQLpro a pas mal développé le thème (Bases de données épaisses).

[fsmrel]

J'ajoute 2 modélisations alternatives histoire d'alimenter la discussion.
- MCD2/MLD2 : Sans la relation de précédence
- MCD3/MLD3 : Avec une "surrogate key" (je ne retrouve plus le terme conceptuel, s'il existe...) et la relation basée sur l'identifiant alternatif

En principe j’a traité de ces variantes dans ma réponse précédente.

Concernant le concept de surrogate (littéralement substitut) :

Il faut réduire le terme « surrogate key » à « surrogate » tout court. Dans le contexte de la modélisation des bases de données, le surrogate est un concept coddien, que l’on peut définir comme identifiant d’entité, généré par le système quand on crée une entité :

Code :

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

1
2
3
4
CREATE ENTITY OF TYPE PERSONNE (PersonneId := 'p1',
                                PersonneNom := 'Dee',
                                PersonneAdr := 'Relationland') ;

PersonneId représente ici l’identifiant déclaré au niveau conceptuel, donc la clé « utilisateur » au niveau logique). Le surrogate est le représentant de l’entité, caché à l’utilisateur (humain ou programme). Pour tout savoir, il faut lire ce que Codd a écrit à ce sujet, quand il décrit RM/T qui est un modèle sémantique, donc du niveau conceptuel. Quelque part, le surrogate (qui date de 1979) ressemble fort à l’OID des systèmes à objets (il est caché, non modifiable, indestructible, etc.)

Dans notre contexte, évitons de parler de surrogate et parlons plutôt des clés naturelles vs les clés artificielles (ou techniques si vous préférez).

[pfortin]

Bonjour et merci pour vos réponses, fsmrel.

peut-on accepter le null dans les dates ?

Vous connaissez le point de vue des Relationlanders : le bonhomme Null est définitivement interdit de séjour.
A ce propos, dans votre MLD, l’attribut Valeur doit être déclaré NOT NULL (table Ent_Dap).

Concernant le bonhomme Null, les techniques que vous suggériez ici fonctionnent parfaitement :
- date de début : 0001-01-01 pour "l'infini avant"
- date de fin : 9999-12-31 pour "l'infini après"
- valeur : chaîne vide si non renseignée (le NOT NULL est un laxisme dans ma reproduction du MCD, PowerAMC autorisant le NULL par défaut )
En l'occurrence, on m'impose le Null de force d'où le conflit.
La question était probablement un appel du pied inconscient...

la relation vers la période suivante est-elle désirable ?

La réponse est négative dans la mesure où la date de fin de la dernière période n’est pas connue, auquel cas le bonhomme Null ne manquerait pas de se manifester.

Intuitivement, j'ai la même position que vous.

Mais l'argument du bonhomme Null ne me semble pas tenir dans la mesure où l'on peut le neutraliser avec le joker défini plus haut.
De même, PowerAMC et moi avons internalisé cette relation mais en l'externalisant (même message), on se débarrasse aussi de notre indésirable.

faut-il stocker date de début ET date de fin (debut_suiv) ?

Si la dernière période est connue (donc la date de fin qui la caractérise), c’est ce que l’on doit faire en théorie (cf. Temporal Data and The Relational Model), mais on doit disposer du générateur de type INTERVAL. A défaut, ce type est à définir soi-même, ce qu’il faut faire quand votre SGBD vous le permet, conformément à la norme SQL. Mais si, par exemple, vous utilisez SQL Server, alors you can always run...

J'utilise le moteur SQL de Firebird. Il n'a pas de générateur de type INTERVAL.
Donc je cours, enfin je peux.

Supposons que vous ne puissiez pas disposer d’un type PERIODE (défini disons à l’aide du générateur INTERVAL).

Si la dernière période est connue (c'est-à-dire la date de fin), votre MLD convient, mais à condition de faire la chasse au bonhomme Null (attribut Debut_Suiv à déclarer NOT NULL). En outre, il faut déclarer une clé alternative {Id_Entite, Debut_Suiv}, sinon on pourrait trouver un tuple <e1, 2011-01-01, 2011-03-01, valeur1> et un autre tuple <e1, 2011-05-01, 2011-03-01, valeur2>. Le support commis par Darwen et auquel je vous renvoie décrit les concepts qui doivent être pris en compte par les SGBD et ayant pour objet qu'ils nous déchargent de l’intégrité des données. A défaut, à vous d’assumer. Par exemple, s’assurer du non chevauchement des périodes, de leur continuité stricte (l’auto-référence de votre MLD devant disparaître) : il y a donc des triggers en perspective...

La règle concernant la date de fin est la suivante : La dernière période a pour date de fin "l'infini après"...

Hypothèse 1 : (joker !)
Assimilons cela à une valeur connue, disons la date limite de consommation du système, le 9999-12-31 qui n'est pas null.
Aucune objection pour la clé alternative. Elle était prévue, j'ai juste oublié de la recréer.
Il faut aussi une contrainte vérifiant la règle de gestion Début < Début_Suiv.
Mais rien n'assure la continuité stricte.
Il faudra effectivement en passer par du code à gogo.
Seulement, triggers et "procstock" me sont interdits pour des raisons de portabilité ( qui passent donc avant l'intégrité des données )...
Donc exit la validation de la continuité dans ce cas de figure.
Mais bon, il parait que la couche métier s'en charge.

Si la date de fin n’est pas connue, la date de début (attribut Debut) et la valeur (attribut Valeur) doivent en théorie faire partie de la structure de la table ENTITE. On est alors dans la logique du « SINCE/DURING » dont fait état Darwen : depuis tel jour (SINCE), la valeur est celle-ci. Durant (DURING) les périodes qui précédèrent, les valeurs furent celles-là (aspect historique des données).

Toujours si la date de fin n’est pas connue, vous pouvez aussi faire l’économie de l’attribut Debut_Suiv. L’inconvénient est que pour déterminer la date de fin d’une période dont la date de début est d1, il faut mettre en œuvre une requête impliquant une thêta-jointure qui permette de retrouver dans la table Ent_Dap la plus petite des dates supérieures à d1. En contrepartie, pas de clé alternative et pas de triggers de contrôle du recouvrement des périodes, etc.

Hypothèse 2 : (qui vaut bien l'autre)
"l'infini après", ça n'est pas une date. Donc on y va.
Sur ce coup-là, on gère des bornes dans un historique.
Comme on fonctionne à date de consultation, on s'intéresse autant aux valeurs passées que futures (enfin si elles sont connues ).
Du coup, je prends sur moi de mettre toutes les valeurs dans l'historique.

Reste le choix entre la date de fin et la thêta-jointure, càd entre la performance et l'intégrité.
- La seconde option fut tentée il y a un moment. Les développeurs n'ont pas aimé (mais alors vraiment pas).
Et pour être honnête, j'avais du mal à me convaincre que ça puisse être performant.
Mais au moins c'était sûr.
- Au final la date de fin est privilégiée par le modèle objet qui sera responsable de l'intégrité (continuité, non-recouvrement...) au sein de la couche métier.

Stricto sensu, oui puisqu’en principe une valeur de clé primaire est de préférence invariante. Mais le respect de l’invariance vaut fondamentalement quand une clé peut être référencée par une clé étrangère.
...
Si donc vos dates ne font pas (ou ne feront pas) l’objet de références par d’autres tables, inutile de définir une clé supplémentaire. Si vous craignez que cela puisse se produire, définissez cette clé.

On m'a juré que ça n'arriverai pas et j'y crois fermement.
Par contre, on me demande un OID par table.
J'ai donc une clé artificielle en tant que clé alternative.

Que oui ! Mais en fait, c’est la 6NF elle-même qui est quelque part la conséquence des « Nine requirements » décrits dans le support de Darwen.

Je m'y plongerai dés demain.

Les contraintes doivent faire partie de la base de données, coller aux données elles-mêmes et ne pas être éparpillées dans des programmes qui en sont éloignés au sein de différentes couches. Je pense que SQLpro a pas mal développé le thème (Bases de données épaisses).

A bien y réfléchir, notre problème est plus organisationnel.
La hiérarchie a neutralisé l'ORM en imposant un mapping en 1 pour 1 entre les objets et la base.
L'espoir est de générer la base depuis le modèle objet.
Comme la conception est orientée objet... je suis tenu de m'aligner.
Mais peut-être qu'avec le temps...

Merci pour votre éclairage.