Contraintes circulaires / déferrables

[elsuket]

Bonjour,

J'ai un problème de modélisation, et il faut savoir que j'utilise SQL Server, qui ne permet pas (mais est-ce un mal) la déférrabilité des contraintes de clé étrangère.

J'ai donc une entité document, avec quelques attributs, et notamment un que nous avons nommé derniere_document_version_id.
Tout document a une dernière version.

D'autre part j'ai une entité document_version, donc les attributs sont, entre autres, document_id (référence l'entité document), version_major et version_minor.

Évidemment une version de document correspond obligatoirement à un document.

Cela donne les tables suivantes :

Code :

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
CREATE TABLE document
(
	document_id uniqueidentifier CONSTRAINT PKdocument PRIMARY KEY
	, derniere_document_version_id uniqueidentifier NOT NULL
)
GO
 
CREATE TABLE document_version
(
	document_version_id uniqueidentifier NOT NULL CONSTRAINT PKdocument_version PRIMARY KEY
	, document_id uniqueidentifier NOT NULL
	, version_major int NOT NULL
	, version_minor int NOT NULL
)
GO
 
ALTER TABLE document
ADD CONSTRAINT FKdocument__latest_document_version_id
FOREIGN KEY (latest_document_version_id) REFERENCES document_version
GO
 
ALTER TABLE document_version
ADD CONSTRAINT FKdocument_version__document_id
FOREIGN KEY (document_id) REFERENCES document
GO
 
ALTER TABLE document_version
ADD CONSTRAINT UQdocument_version__document_id__version_major__version_minor
UNIQUE (document_id, version_major, version_minor)
GO

Je suis contre l'utilisation du type uniqueidentifier comme clé primaire, donc SVP ne me tapez pas, cela ne dépend pas de moi

Donc je ne peux pas ajouter de lignes dans les tables correspondantes.

Je pensais donc rendre les colonnes :

- document_id de la table document_version,
- latest_document_version_id de la table document NULLable
- gérer l'insertion à l'aide d'une transaction

Les autre possibilités m'ont l'air assez crades :

- supprimer la colonne derniere_document_version_id, et ajouter une colonne de type bit à la table document_version pour marquer une version comme étant la dernière ...
- supprimer la colonne derniere_document_version_id créer une vue indexée pour effectuer le calcul.

Dans les deux cas ce sera plus coûteux que la transaction.

J'aimerais avoir votre avis là-dessus.

Merci !

@++

[fsmrel]

Bonsoir,


Côté modélisation, c’est évidemment affreux et vous en subissez les conséquences...

En théorie, les versions ne figurent que dans la table DOCUMENT_VERSION et récupérer la dernière d’entre elles est alors un jeu d’enfant pour quelqu’un de votre calibre.

Si malgré tout, on tient (lubie de l’autorité ?) à faire figurer cette dernière version dans la table DOCUMENT, on peut le faire, mais sans redondance, c'est-à-dire en l’expulsant de la table DOCUMENT_VERSION qui devient en quelque sorte un historique (donc avec gestion de dates de début et de fin et tout le toutim, au moins dans l’exemple).

Exemple (SQL Server 2005), (permettez-moi au passage de remplacer UNIQUEIDENTIFIER par CHAR(04)... ) :

Code SQL :

1
2
3
4
5
6
7
8
CREATE TABLE DOCUMENT
(
      DocId           CHAR(04)       NOT NULL
    , DocNom          VARCHAR(48)    NOT NULL
    , VersionId       CHAR(04)       NOT NULL
    , VersionDepuis   DATETIME       NOT NULL
  , CONSTRAINT DOCUMENT_PK PRIMARY KEY (DocId)
) ;

Code SQL :

1
2
3
4
5
6
7
8
9
10
CREATE TABLE DOCUMENT_VERSION
(
      DocId              CHAR(04)       NOT NULL
    , VersionId          CHAR(04)       NOT NULL
    , VersionDurantDeb   DATETIME       NOT NULL
    , VersionDurantFin   DATETIME       NOT NULL
  , CONSTRAINT DOCUMENT_VERSION_PK PRIMARY KEY (DocId, VersionId)
  , CONSTRAINT DOCUMENT_VERSION_FK FOREIGN KEY (DocId) 
         REFERENCES DOCUMENT (DocId)
) ;

Code SQL :

1
2
3
INSERT INTO DOCUMENT VALUES ('d01', 'document 01', 'v03', '2010-05-01') ;
INSERT INTO DOCUMENT VALUES ('d02', 'document 02', 'v01', '1999-01-01') ;
INSERT INTO DOCUMENT VALUES ('d03', 'document 03', 'v04', '2005-07-14') ;

Code SQL :

1
2
3
4
5
INSERT INTO DOCUMENT_VERSION VALUES ('d01', 'v01', '2002-03-01', '2007-07-31') ;
INSERT INTO DOCUMENT_VERSION VALUES ('d01', 'v02', '2007-08-01', '2010-04-30') ;
INSERT INTO DOCUMENT_VERSION VALUES ('d03', 'v01', '2000-01-01', '2000-04-02') ;
INSERT INTO DOCUMENT_VERSION VALUES ('d03', 'v02', '2000-04-03', '2003-05-15') ;
INSERT INTO DOCUMENT_VERSION VALUES ('d03', 'v03', '2003-05-15', '2005-07-13') ;

Avec une vue d’union (pardon pour les CONVERT SQL Server) :

Code SQL :

1
2
3
4
5
6
7
8
9
CREATE VIEW DOCUMENT_V (DocId, DocNom, VersionId, Debut, Fin)
  AS  SELECT DocId, DocNom, VersionId, 
             CONVERT (CHAR(10), VersionDepuis, 121), ' en cours '
      FROM   DOCUMENT
      UNION  
      SELECT x.DocId, y.DocNom, x.VersionId, 
             CONVERT (CHAR(10), x.VersionDurantDeb, 121), CONVERT (CHAR(10), x.VersionDurantFin, 121)
      FROM   DOCUMENT_VERSION AS x JOIN DOCUMENT AS y
             ON x.DocId = y.DocId

=>

Code :

1
2
3
4
5
6
7
8
9
10
11
 
DocId   DocNom        VersionId   Debut        Fin

d01     document 01   v01         2002-03-01   2007-07-31
d01     document 01   v02         2007-08-01   2010-04-30
d01     document 01   v03         2010-05-01   en cours 
d02     document 02   v01         1999-01-01   en cours 
d03     document 03   v01         2000-01-01   2000-04-02
d03     document 03   v02         2000-04-03   2003-05-15
d03     document 03   v03         2003-05-15   2005-07-13
d03     document 03   v04         2005-07-14   en cours

Je n'ai pas répondu d'un point de vue technique à votre demande, mais parler de bonne modélisation, de non redondance et de périodes de dates peut éventuellement donner des idées...

[vmolines]

Bonjour fsmrel,

La modélisation que vous proposez est-elle juste faite pour éviter le NULL dans VersionDurantFin ? Sinon quelle différence avec :

Code :

1
2
3
4
5
6
CREATE TABLE DOCUMENT
(
      DocId           CHAR(04)       NOT NULL
    , DocNom          VARCHAR(48)    NOT NULL
  , CONSTRAINT DOCUMENT_PK PRIMARY KEY (DocId)
) ;

Code :

1
2
3
4
5
6
7
8
9
10
CREATE TABLE DOCUMENT_VERSION
(
      DocId              CHAR(04)       NOT NULL
    , VersionId          CHAR(04)       NOT NULL
    , VersionDurantDeb   DATETIME       NOT NULL
    , VersionDurantFin   DATETIME       
  , CONSTRAINT DOCUMENT_VERSION_PK PRIMARY KEY (DocId, VersionId)
  , CONSTRAINT DOCUMENT_VERSION_FK FOREIGN KEY (DocId) 
         REFERENCES DOCUMENT (DocId)
) ;

Connaissant votre goût pour les valeurs nullables, il me semble que c'est bien la cause mais je voulais avoir confirmation. Loin de moi le désir de (re)lancer un débat sur le NULL dans le sujet.


Et enfin question pour Elsuket, pourquoi écarter la solution de la vue indexée pour maintenir une colonne de la dernière version par rapport à une solution basée sur une transaction ?

[Waldar]

Effectivement gérer la dernière version dans une vue me paraît le plus propre !

Sinon autre solution pas super (j'ai rajouté un datetime dans document_version) :

Code :

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
CREATE VIEW v_document_version
AS
SELECT doc.document_id,
       doc.derniere_document_version_id,
       dve.document_version_id,
       dve.version_major,
       dve.version_minor,
       dve.document_version_date
  FROM document AS doc
       INNER JOIN document_version AS dve
         ON dve.document_id = doc.document_id
 
 
CREATE TRIGGER trg_v_document_version_i
ON v_document_version
instead of INSERT 
AS
begin
   declare @doc_id      uniqueidentifier;
   declare @doc_vers_id uniqueidentifier;
   SET @doc_id      = newid();
   SET @doc_vers_id = newid();
 
ALTER TABLE document NOCHECK CONSTRAINT FKdocument__latest_document_version_id
 
INSERT INTO document (document_id, derniere_document_version_id)
VALUES (@doc_id, @doc_vers_id)
 
INSERT INTO document_version
(document_version_id, document_id, version_major, version_minor, document_version_date)
SELECT @doc_vers_id, @doc_id, version_major, version_minor, getdate()
  FROM inserted
 
ALTER TABLE document WITH CHECK CHECK CONSTRAINT FKdocument__latest_document_version_id
 
end;
 
 
INSERT INTO v_document_version (version_major, version_minor) VALUES (3, 2)
 
SELECT * FROM v_document_version
 
document_id                          derniere_document_version_id         document_version_id                  version_major version_minor document_version_date
------------------------------------ ------------------------------------ ------------------------------------ ------------- ------------- -----------------------
74B7EAD8-80D9-4FAD-A574-002EC9D89EB9 74350DE1-27AE-43C4-B13D-3C641B381D3E 74350DE1-27AE-43C4-B13D-3C641B381D3E 3             2             2011-05-04 12:03:57.390

[fsmrel]

Bonjour,


C’est un plaisir de vous revoir, vmolines du pays du soleil.

Envoyé par vmolines

La modélisation que vous proposez est-elle juste faite pour éviter le NULL dans VersionDurantFin ?

La modélisation que vous proposez à votre tour va dans le sens de ce que j’avais écrit :

Envoyé par fsmrel

En théorie, les versions ne figurent que dans la table DOCUMENT_VERSION et récupérer la dernière d’entre elles est alors un jeu d’enfant pour quelqu’un de votre calibre.

Cela dit, mon objet était seulement d’aborder le thème de l’historisation des données telle qu'elle doit être pratiquée dans les règles de l’art. Mais évidemment, dans cette histoire le bonhomme NULL est d’office éjecté, sans même que l’on ait à parler de lui. Le principe de base est, d’une part de séparer les données « mortes » des données « vives » (décomposition horizontale), et d’autre part de séparer les données mises à jour à des dates qui leurs sont propres (décomposition verticale). Tout est décrit en 400 pages dans un ouvrage dédié aux données temporelles Temporal Data and the Relational Model dû à Lorentzos, Date et Darwen. On en retrouve un concentré dans le chapitre 23, «Temporal Databases» de An Introduction to Database Systems, 8th edition. Si vous voulez un avant-goût, voyez le support de cours de Darwen.

[vmolines]

Encore une fois j'ai lu trop vite et la réponse que j'attendais était effectivement déjà là .

Merci pour les références concernant l'historisation des données car c'est une problématique récurrente sur laquelle je me sens encore très léger.

edit : ah mais je reconnais cet extrait, il est imprimé chez moi et j'avoue qu'il m'a fait mal à ma première (et dernière) lecture. Bon, il faudra que je m'y replonge .

[SQLpro]

Envoyé par fsmrel

Tout est décrit en 400 pages dans un ouvrage dédié aux données temporelles Temporal Data and the Relational Model dû à Lorentzos, Date et Darwen. On en retrouve un concentré dans le chapitre 23, «Temporal Databases» de An Introduction to Database Systems, 8th edition. Si vous voulez un avant-goût, voyez le support de cours de Darwen.

Tu aurais aussi pu donner la référence du snodgrass (Developing Time-Oriented Database Applications in SQL) qui possède l'avantage d'être téléchargeable :
http://sql-info.de/sql-notes/develop...ns-in-sql.html

A +

[iberserk]

supprimer la colonne derniere_document_version_id créer une vue indexée pour effectuer le calcul

Je partirais personnellement sur la vue indexée qui demandera d'ailleurs moins de
traitement sur les INSERT/UPDATE/DELETE de version qu'une historisation.

Bien sûr le principe de l'historisation est tout à fait valable également, je n'expose que ma sensibilité

[fsmrel]

Bonjour,

Envoyé par SQLpro

Tu aurais aussi pu donner la référence du snodgrass (Developing Time-Oriented Database Applications in SQL) qui possède l'avantage d'être téléchargeable

... Mais qui a l’inconvénient d’être entaché d’erreurs sévères quant aux fondements, faisant que l’incorporation à la norme SQL des propositions de Snodgrass, accueillie favorablement il y a une quinzaine d’années était toujours dans les limbes en 2006 (voir à ce sujet l’article de Date et Darwen «An Overview and Analysis of Proposals Based on the TSQL2 Approach»). Je ne sais pas quelle est la situation en 2011, et si vous avez des nouvelles, je suis intéressé. L’ouvrage de Snodgrass est évidemment plein de bonnes intentions, ces choses dont l’enfer est pavé.

Pour mémoire, parmi les principales erreurs relevées :

— Mise en œuvre d’attributs cachés, entraînant le viol du Principe de l’information : les objets mis en œuvre ne sont pas relationnels (voir le paragraphe « Consequences of Hidden Columns » dans l’article que j’ai cité).

— Résultats erronés dus aux modificateurs d’instruction (voir le paragraphe « Statement Modifiers »).

— Mélange des données actives et historisées en conséquence du principe de la compatibilité temporelle ascendante (voir le paragraphe « Temporal Upward Compatibility ») conduisant à la mise en œuvre des attributs cachés et des modificateurs d’instruction.

Cela dit, tant qu’à fournir des références, allons-y. En l’occurrence, je pioche dans DBPD (Database Programming & Design) revue malheureusement avalée fin 1998 par Intelligent Enterprise (et dont le dernier numéro est daté d’octobre 1998).


Voici ce qu’a écrit Jim Melton en novembre 1995 (Database Programming & Design, Volume 8 - Number 11), dans son article « A Flurry of Activity in the SQL Standards World » :

Long-time users of SQL in business applications (and not a few scientific ones, too!) are painfully aware that SQL's ability to handle time-series data and the like is ... well, let's just say that it's not exactly breathtaking.

When I'm introducing this subject to my nondatabase friends, I like to call it the “bank statement problem.” Remember last year when the IRS wanted to audit your 1990 taxes and you couldn't find your April 1990 bank statement? You had to call the bank up and say, “Ahem, I need a statement from April 1990 ...can you send one to me?” Providing this service in a standard SQL database would be awkward, if not downright difficult.

But, that situation is going to change. A group of (mostly) academics, called the “TSQL2 Committee,” spent a lot of time and energy debating many approaches to temporal data support in SQL; A majority of the participants finally accepted one of these approaches, and a spokesperson (Rick Snodgrass of the University of Arizona) made contact with ANSI Technical Committee X3H2 to see what interest might exist in accepting the TSQL2 Committee's work as part of the SQL standard.

X3H2 was quite receptive. Indeed, it viewed the TSQL2 Committee's work as an excellent opportunity to prove that standards committees and academia can work together. Plus, experts in specific domains could perform the design work, while standards specialists could do what they do best—write the specifications. Snodgrass was encouraged enough that he worked with Nelson Mattos of IBM and the editor (yup ... me) to develop an initial candidate base document for yet another new part of SQL—known now as Part 7: Temporal (SQL/Temporal). The U.S. proposed this work to ISO in July 1995 and, after significant debate on the subject, the ISO SQL group accepted it as its base document for a new Part 7 of SQL.

Unfortunately, this project is likely to progress less smoothly than some might like. The U.K. participants decided that they did not like the approach chosen by the TSQL2 committee and contacted a researcher who supported an approach rejected by the TSQL2 group. The U.K. participants then argued against accepting the proposed base document in favor of "in the future" having a base document that used a different approach. The ISO committee rejected this suggestion (wisely, in my opinion), partly on the grounds that the larger community of temporal data experts had selected the TSQL2 approach, and letting SQL experts who are not temporal experts reject its decision did not seem wise. Of course, the U.K. participants are free to bring in proposals to replace much of the accepted base document with the other approach; if their work is technically sound, their approach will have a chance to prevail.

J’aime bien les commentaires savoureux de Ed Dee, Database Languages Rapporteur Group, British Standards Institution (cf. Database Programming & Design (Volume 9 - Number 5), dans sa réponse « U.K. SQL standards group speaks up » datée de mai 1996) :

DEAR EDITOR

I am writing to you on behalf of the Database Languages Rapporteur Group of the British Standards Institution (the group responsible for coordinating U.K. input to SQL standardization). This letter was prompted by various references to the in Jim Melton’s November SQL Update column, “A Flurry of Activity in the SQL Standards World.”

We believe that some of those references exhibited a degree of economy with the truth and that they reflect unfavorably on us in the U.K.

We would like to give our versions of two of Melton's statements. Naturally, we believe our own versions to be the more accurate.
On the subject of SQL/PSM, Melton wrote: “The perception of the magnitude of these problems caused some participants, notably from the U.K. and Canada, to propose removing this capability from PSM entirely; to the astonishment of U.S. participants, their attempt received sufficient support from other nations!”

We would rephrase this statement as follows: There were some problems that were so serious that a majority of participants favored deferring the clearly immature error handling specification to SQL3, or having it follow on as an addendum. Surprisingly, U.S. delegates would apparently have been happy to publish a standard with known defects, to be followed inevitably by embarrassing corrigenda. Happily, both these undesirable outcomes were averted as a result of much effort on all sides.

On the subject of SQL/Temporal, Melton wrote:

“The U.K. participants decided that they did not like the approach chosen by the TSQL2 committee and contacted a researcher who supported an approach rejected by the TSQL2 group. The U.K. participants then argued against accepting the proposed base document in favor of “in the future” having a base document that used a different approach. The ISO committee rejected this suggestion (wisely, in my opinion), partly on the grounds that the larger community of temporal data experts had selected the TSQL2 approach and letting SQL experts who are not temporal experts reject its decision did not seem wise. Of course, the U.K. participants are free to bring in proposals to replace much of the accepted base document with the other approach; if their work is technically sound, their approach will have a chance to prevail.”

Our version would read as follows: Since the discussion of temporal database at the previous International meeting in January, the U.K. participants had studied the TSQL documents in considerable detail, had extensive e-mail discussions with their principal author and, with his help and encouragement, made contact with others in the field. This led to further e-mail discussions and to face-to-face meetings with researchers from three universities. As a result of these discussions, they formed the view that, by approaching two aspects of the subject in slightly different ways, and by basing SQL/T on SQL3 rather than on SQL2, it might be possible to arrive at an outcome that would be more satisfactory in a number of ways, not least by being applicable to dimensions other than time.

In spite of the U.S.-proposed working draft of SQL/T having been distributed after the cut-off date, the U.K. documented a number of specific defects in it, technical and otherwise. Although initially of the view that recommending adoption of the U.S. proposal would run the risk of bringing ISO into disrepute with anyone who read it as a near-complete SQL standard, the U.K. participants supported the recommendation for lack of any alternative.

It is very much hoped that the eventual result will be a way of specifying and accessing temporal data that is as easy to use as TSQL is claimed to be, while as general as we are convinced it should be.

Pour être complet, voici les commentaires de Jim Melton accompagnant la réponse d’Ed Dee dans le même numéro de DBPD :

Contributing editor Jim Melton replies:

I regret that my colleagues in the U.K., whom I respect greatly, feel that I exercised “economy with the truth”; nothing was further from my mind when I wrote the November column. When, in regards to SQL/PSM, I said that the U.K. and Canadian participants proposed removing “this facility from PSM entirely,” 1 referred to the version of PSM being progressed to Draft International Standard at the time—not the future version that 'will be progressed over the next two or three years. It’s more unfortunate that the U. K. participants believe that “U. S. delegates would apparently have been happy Ito publish a standard with known defects,” as the U.S. delegates were already working very hard to repair those defects. However, I think it’s very important that I acknowledge the contributions from U.K. participants in identifying and solving those problems, which I failed to do in my November column.

In my discussion of the SQL/Temporal work, I stated that U. K. participants seemed to oppose the work submitted for consideration by the TSQL committee and preferred the approach of minority position. Mr. Dee and his colleagues say they only want to approach the issues in “slightly different ways.” However, anyone who examines the actual technical papers in question would almost certainly conclude that the approaches are different in quite significant ways. The details might be suitable for a future column or article, but are rather too large for a short response such as this. Incidentally, I agree with the U.K. view that basing SQL/Temporal on SQL3 instead of SQL-92 will be very productive; though there were initial hopes of progressing SQL/Temporal somewhat faster than SQL3, this has proved to be impossible. (Incidentally, 1 would urge contributors to avoid thinking of SQL/Temporal as “SQL/T,” as that term is redundant with SQL/Transaction, another part of SQL3.)

=>

Comme le temps passe... Aujourd’hui, Anne, ma sœur Anne, ne vois-tu rien venir ? (Et pendant ce temps, Malbrough ne revient toujours pas...)

[ghosn]

Ma préférence va pour la solution "supprimer la colonne derniere_document_version_id, et ajouter une colonne de type bit à la table document_version pour marquer une version comme étant la dernière ...". C'est une solution simple et elle te permet de normaliser ton modèle.

[fsmrel]

Envoyé par ghosn

Ma préférence va pour la solution "supprimer la colonne derniere_document_version_id, et ajouter une colonne de type bit à la table document_version pour marquer une version comme étant la dernière ...". C'est une solution simple et elle te permet de normaliser ton modèle.

Quel sens donnez-vous au mot « normaliser » ? S’il s’agit de normaliser au sens classique des bases de données, c'est-à-dire respecter la première, la deuxième, etc. formes normales, notez que toutes les variantes proposées respectent la cinquième forme normale, ce qui n’est quand même pas mal...
En l’état, la table DOCUMENT proposée par elsuket respecte la sixième forme normale, tandis que sa table DOCUMENT_VERSION ne respecte « que » la cinquième forme normale. La solution qui a votre préférence n’apporterait donc d’amélioration au plan de la normalisation que si la table DOCUMENT_VERSION respectait dorénavant elle aussi la sixième forme normale, ce qui n’est pas le cas.

[ghosn]

Je suis d'accord avec toi mais je précise 2 choses :
- la solution que j'ai choisi est normalisée si on la compare par rapport à la situtation initial du modèle (pas par rapport aux autres solutions proposées);
- cette solution respecte la 3eme forme normale (qui est souvent le standard en entreprise) et je la trouve très simple.

Hors discussion :
Pour moi qui pensait que Merise et ses adeptes étaient morts, je suis agréablement surpris de voir des personnes visant la dernière forme normale.
C'est une chance que de vous avoir sur ce forum.

[fsmrel]

Bonjour,

Envoyé par ghosn

la solution que j'ai choisi est normalisée si on la compare par rapport à la situation initial du modèle

Je répète que du strict point de vue de la normalisation, les structures des tables initiales et les vôtres sont à égalité.

Envoyé par ghosn

cette solution respecte la 3eme forme normale.

La solution initiale ainsi que la vôtre respectent la cinquième forme normale (et la sixième pour partie), donc la troisième.

Envoyé par ghosn

qui est souvent le standard en entreprise

Il n’y a pas de standard en entreprise sur le sujet, sinon des affirmations gratuites déjà formulées il y a au moins trente ans par de prétendus experts n’ayant étudié ni la forme normale de Boyce-Codd, ni a fortiori la quatrième et la cinquième forme normales, ou, variante (notamment le 2e exemple), ayant compris le sujet de travers et ce dès la première forme normale.

Envoyé par ghosn

Pour moi qui pensait que Merise et ses adeptes étaient morts.

Je ne suis manifestement pas mort et je ne suis pas un adepte de Merise, mais seulement du Modèle Relationnel de Données. Pour que les choses soient claires, j’en rappelle la définition :

Le Modèle Relationnel de Données (qui relève de la logique et des mathématiques appliquées) est défini par les cinq composants suivants (pour plus de détails, voyez l’ouvrage de C. J. Date Database in depth: relational theory for practitioners) :

1. Une collection non limitée de types scalaires (dont notamment le type booléen (valeur de vérité)),
2. Un générateur de type Relation et l’interprétation attendue des types de relations générés par ce moyen,
3. Les mécanismes pour définir des variables relationnelles du type de relation voulu,
4. L’opération d’affectation relationnelle permettant d’affecter des valeurs de relations à ces variables,
5. Une collection non limitée d’opérateurs relationnels génériques (« l’algèbre relationnelle »), pour produire des valeurs de relations à partir d’autres valeurs de relations.

Envoyé par ghosn

je suis agréablement surpris de voir des personnes visant la dernière forme normale.

Pour les habitants du Relationland, c’est la moindre des choses.

[ghosn]

Bonjour,
Je me trompe peut être mais j'ai un doute sur le fait que la structure de départ soit en 5eme forme normale comme tu le dis pour les raisons suivantes :
- le champ "derniere_document_version_id" de la table "document" permet de déterminer le champ document_id" (grâce à la redondance de l'information dans la table "document_version");
- or selon la FNBC "tous les attributs non-clé ne sont pas source de dépendance fonctionnelle vers une partie de la clé".
La FNBC n'est donc pas respectée.
Ton éclairage sera bienvenu.

[fsmrel]

Bonjour,

Envoyé par ghosn

selon la FNBC "tous les attributs non-clé ne sont pas source de dépendance fonctionnelle vers une partie de la clé"

Je ne sais pas qui est l’auteur de cette variante, mais en tout cas elle n’est pas conforme, car elle suppose qu’une table a exactement une clé, ce qui est pour le moins réducteur ⁽¹⁾. Pour traiter de la normalisation, il est nécessaire d’utiliser une définition valide. Par exemple celle-ci que l'on doit à C. J. Date (qui en propose deux ou trois variantes équivalentes) :

Relvar R is in BCNF if and only if for every nontrivial FD A → B satisfied by R, A is a superkey for R.

Dans cette définition de référence, il n'est pas écrit « is the » mais « is a », définition que vous retrouverez dans SQL and Relational Theory, Database in depth: relational theory for practitioners, The Relational Database Dictionary, etc.

Dans le contexte SQL, vous pouvez remplacer le terme Relvar par celui moins précis de Table. Pour savoir ce qu’est une surclé (superkey) ou une dépendance fonctionnelle non triviale (nontrivial FD), je vous renvoie à Bases de données relationnelles et normalisation. Notez encore que A et B ne sont pas des attributs, mais des ensembles d'attributs (au sens de la théorie des ensembles). En passant, ce ne sont pas non plus des champs, le terme Champ étant totalement étranger au vocabulaire relationnel (tout comme au vocabulaire SQL, avec lequel on utilise plutôt le terme Colonne).

Vous pouvez aussi vous référer au Professeur Gardarin dans Bases de données, les systèmes et leurs langages :

Une relation est en BCNF si et seulement si les seules dépendances fonctionnelles élémentaires sont celles dans lesquelles une clé détermine un attribut.

Le Professeur Gardarin met en jeu le concept de dépendance fonctionnelle élémentaire (alias dépendance fonctionnelle totale ou irréductible à gauche), mais sa définition est équivalente à celle de Date.

Côté français, on a encore la définition de Delobel et Adiba (Bases de données et systèmes relationnels) :

Un schéma R = <U, F> est en troisième forme normale de Boyce-Codd-Kent (3FNBCK) si chaque fois que X → A, A ∉ X, est vérifiée dans R alors X contient une clé de R.

Veuillez noter que X n’est pas un attribut, mais un sous-ensemble (non strict) d’attributs de R (au sens de la théorie des ensembles). Notez encore que dans cette définition, il est écrit « X contient une clé » et non pas « X contient la clé », une fois de plus contrairement à ce qui est écrit dans la définition que vous donnez.


Maintenant, comme vous dites à propos de la table DOCUMENT en cause, {derniere_document_version_id} détermine {document_id} et la table DOCUMENT satisfait aux dépendances fonctionnelles suivantes :

{document_id} → {derniere_document_version_id}
{derniere_document_version_id} → {document_id}

Autrement dit, étant donné que document_id et derniere_document_version_id sont les seuls attributs de la table DOCUMENT, {document_id} et {derniere_document_version_id} sont les déterminants des seules DF non triviales et ce sont des surclés : la BCNF (la vraie) est respectée.

La 5NF l’est aussi, en vertu du théorème suivant de Date et Fagin :

Si la relvar R est en BCNF et si chaque clé est singleton (c'est-à-dire si elle ne comporte qu’un seul attribut), alors R est en 5NF.

DOCUMENT est aussi en 6NF. Pour vous éviter de vous lancer dans l’étude des dépendances de jointure, vous pouvez vous appuyer sur l’observation suivante (que vous retrouverez dans les ouvrages déjà mentionnés de C. J. Date) :

Relvar R is in 6NF if and only if it’s in 5NF, is of degree n, and has no key of degree less than n-1.

Voilà...


⁽¹⁾ Examinez le tableau périodique des éléments. On y voit que l’élément de numéro atomique 6 a pour nom Carbone, pour symbole C, pour masse atomique 12, etc. Aucun autre élément ne possède les caractéristiques de cet élément : numéro atomique, nom, symbole, masse atomique, etc. sont autant de propriétés uniques et chacune d’elles est candidate à faire l’objet d’une clé : dans la théorie relationnelle on dira que {numéro atomique}, {nom}, {symbole}, {masse atomique}, etc. sont des surclés et même, parce qu'elles sont irréductibles, des clés candidates de la table ELEMENT (ou plus simplement clés). Qui plus est, il y aurait bien un Yaka pour en remettre une couche et — au grand dam de Nicolas — ajouter d’autorité un attribut technique de type UNIQUEIDENTIFIER, décrétant que c’est cet attribut qui fera l'objet de la clé de la table... Si on se réfère à votre définition de la forme normale de Boyce-Codd, la table ELEMENT est délinquante, horresco referens !

[elsuket]

Désolé de ma réponse très tardive, et merci à tous de votre participation, surtout fmsrel.

Envoyé par fmsrel

Côté modélisation, c’est évidemment affreux et vous en subissez les conséquences...

Si ce n'était que la première fois

En théorie, les versions ne figurent que dans la table DOCUMENT_VERSION et récupérer la dernière d’entre elles est alors un jeu d’enfant pour quelqu’un de votre calibre.

Effectivement.
J'ai oublié de dire que la table document_version est assez volumineuse (50 millions de lignes environ, stockant un document XML.
On cherche donc a récupérer la dernière version d'un document le plus rapidement possible.

Dans ce cas le stockage de la version dans la table document, qui est toujours filtrée sur sa clé primaire, est la meilleure.

@++

[pacmann]

Salut Elsuket,

Si c'est pour les performances, un index sur (docid, version) sur la table des versions devrait te permettre de chopper la dernière version assez facilement non ? (soit en faisant la jointure du max sur la table des versions, soit en essayant avec de la fonction analytique si les indexes sont bien utilisés...)

[fsmrel]

Ave Nicolas, ave Paku,

Envoyé par elsuket

J'ai oublié de dire que la table document_version est assez volumineuse (50 millions de lignes environ, stockant un document XML.
On cherche donc a récupérer la dernière version d'un document le plus rapidement possible.

Considérons la structure que j’ai proposée ici. Les tables DOCUMENT et DOCUMENT_VERSION ont chacune un index « primaire » de clés respectives (DocId) et (DocId, VersionId). Ces index sont de type cluster sur DocId (au moins au sens DB2 du terme) et la différence à propos de la performance (conséquence de la jointure) ne doit pas être très sensible.

Quant aux mises à jour, n’oubliez pas que l’ajout d’une ligne dans votre table DOCUMENT déclenche aussi la mise à jour de l’index (s’il existe) plaqué sur la colonne latest_document_version_id, ce qui n’est pas neutre.

Et si vous avez besoin d’effectuer une jointure des deux tables, n’oubliez pas qu’avec votre structure, vous risquez d'avoir des problèmes d’I/O bound particulièrement gênants vu la volumétrie, à moins que le cluster ratio des index sur les colonnes document_id et document_version_id soit voisin (ou dans le cas contraire, d’avoir des caches gigantesques et autres palliatifs onéreux du même genre).

=>

Il serait bien d’obtenir un bilan des performances comparées des deux systèmes (prototypage des peformances).