Formes Normales et valeurs NULL [Résolu]

[vh]

Bonjour,

À la fin de mes études d'informatiques, l'entrée dans la vie professionnelle m'a vite faite comprendre que la théorie des formes normalles étaient souvent sabotées par les obligations de gain de temps de développement. J'ai donc fini par oublier le vocabulaire des formes normalles et créer mes tables plus par expérience qu'avec l'aide de la théorie.

Maintenant, j'aimerai bien m'y remettre donc j'ai déjà lu Dénormalisation de table. Quand ? et entre la theorie et la pratique! mais j'ai encore des zones obscures dans la conception des tables.
J'ai par exemple cette table Client ("id_client" est une clé primaire et "type_contrat" une clé étrangère)

Code :

1
2
3
4
5
6
7
id_client  |  nom_client  |  type_contrat  |  offre_du_moment  
-----------+--------------+----------------+-------------------------
1          |  Bouchard    |  A             |  NULL         
2          |  Jean        |  A             |  NULL         
3          |  Rodriguez   |  A             |  NULL         
4          |  La Fleur    |  B             |  Un bouquet acheté pour deux gratuits
5          |  Nestor      |  A             |  NULL

Les clients qui ont un payé un contrat "B" peuvent ajouter un texte personalisé. La proportion est d'environ 1 contrat "B" pour 10 contrats "A" donc je me retrouve avec des tonnes de valeurs "NULL".
À partir de là, je me demande si en suivant la théorie des Formes Normales, j'aurais pu découper ma table dès le début ?

Ou alors il s'agit uniquement d'optimisation et donc ça se rapporte à la question "Quand dénormaliser ?" qui a pour réponse "Après avoir analysé le système en production" ?

[fsmrel]

Bonjour VH,

La théorie de la normalisation fait partie du Modèle Relationnel de Données alors que les valeurs nulles en sont bannies. Pour être en conformité (et en prime procéder à un dégraissage sérieux), il suffit de casser la table Client en deux (comme lorsqu’on normalise) :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
CREATE TABLE Client
(   Id_Client     Int        NOT NULL,
    Nom_Client    Char (xx)  NOT NULL,
    Type_Contrat  Char (xx)  NOT NULL,
  PRIMARY KEY (Id_Client),
  FOREIGN KEY (Type_Contrat)...
) ;
CREATE TABLE Client_Offre_Du_Moment
(   Id_Client     Int           NOT NULL,
    Offre_Du_Moment  Char (xx)  NOT NULL,
  PRIMARY KEY (Id_Client),
  FOREIGN KEY (Id_Client) REFERENCES Client ON DELETE Cascade 
)

Toutes les colonnes sont maintenant NOT NULL.

Concernant la contrainte qui veut que seuls les clients ayant un contrat de type B puissent figurer dans la table Offre_Du_Moment, du point de vue du standard (ou norme) SQL, vous pouvez coder quelque chose de ce genre :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
 
CREATE Assertion Contrainte_xx 
    CHECK (NOT EXISTS 
            (SELECT * 
             FROM Client A 
             WHERE Type_Contrat <> 'B'
                AND EXISTS 
                   (SELECT * 
                    FROM Offre_Du_Moment B
                    WHERE A.Id_Client = B.Id_Client)
                   )
            )
          ) ;

Dans la mesure où votre SGBD ne propose pas l’instruction Create Assertion, vous pouvez utiliser un trigger.

Pour vous rafraîchir la mémoire quant à la normalisation des tables, vous pouvez lire la discussion :

http://www.developpez.net/forums/sho...d.php?t=281221

[vh]

Envoyé par fsmrel

La théorie de la normalisation fait partie du Modèle Relationnel de Données alors que les valeurs nulles en sont bannies.

d'accord merci pour ta réponse
donc j'aurais pu le prévoir dès le début

Envoyé par fsmrel

Dans la mesure où votre SGBD ne propose pas l’instruction Create Assertion, vous pouvez utiliser un trigger.

étant développeur Web j'ai surtout de l'expérience avec MySQL qui ne permet pas de faire grand chose puisque les dernière versions (avec triggers, procédures stockées) ne sont pas encore disponibles chez tous les hébergeurs mutualisés.
de plus j'ai tendance à gérer ce genre de contrainte dans mon code et comme ça je limite l'utilisation du SGBDR au simple stockage des données. Je pense que ça m'aide aussi à construire des tables correctes puisqu'il ne faut pas qu'on puisse remplir les tables avec des données qui ne sont pas cohérentes entre elles

par contre en partant de ce principe, je mets systématiquement les champs en "NULL acceptés" puisque dans ma logique c'est à mon code de gérer l'insertion de valeurs correctes donc je vais revoir ce point

[SQLpro]

vh

... votre titre : "Formes Normales et valeurs NULL" est une hérésie au bon sens : NULL n'est pas une valeur, c'est un marqueur qui représente justement l'absence de valeur comme nil en programmtion à base de pointeurs...

fmsrel

... plus simple l'assertion :

Code :

1
2
3
4
5
6
CREATE Assertion Contrainte_xx 
CHECK NOT EXISTS (SELECT * 
                  FROM   Client C
                         INNER JOIN Offre_Du_Moment O
                               ON C.Id_Client = O.Id_Client
                  WHERE  Type_Contrat <> 'B')

A +

[vh]

Envoyé par SQLpro

... votre titre : "Formes Normales et valeurs NULL" est une hérésie au bon sens : NULL n'est pas une valeur, c'est un marqueur qui représente justement l'absence de valeur comme nil en programmtion à base de pointeurs...

ok
ce que j'ai prévu de faire c'est de concevoir mes prochaines tables avec uniquement des champs "NOT NULL"

merci à vous tous pour vos explications

[SQLpro]

ce que j'ai prévu de faire c'est de concevoir mes prochaines tables avec uniquement des champs "NOT NULL"

C'est la pire des choses à faire...

Petite anecdote... Un jour j'audite une application dans laquelle les développeurs avient tout mis en NOT NULL. Il s'agissait d'un site web de vente de lingerie fine.... Or la date de naissance des clients étaient rarement renseigné et comme c'était du NOT NULL ils mettaient par défaut 01/01/1900.

Quand on a voulu faire des statistiques de vente on s'est aperçut que la moyenne d'âge des clients qui achetaient des strings avec dentelles était de 84 ans. Je leur ait alors dit de se reconvertir dans le DAMART !!!

A +

[vh]

Envoyé par SQLpro

C'est la pire des choses à faire...

j'ôte le Résolu alors parce que là je ne comprends plus

ça veut dire que vous êtes plutot d'accord avec ce que j'ai écris plus haut là ?

Envoyé par vh

par contre en partant de ce principe, je mets systématiquement les champs en "NULL acceptés" puisque dans ma logique c'est à mon code de gérer l'insertion de valeurs correctes donc je vais revoir ce point

et en partant sur des champs qui autorisent le "NULL" on pourrait trouver où le code applicatif aurait des lacunes en cherchant les "null" dans les tables puisque normallement le code ne devrait pas le permettre.
Est ce que mon raisonnement tient la route ?

[fsmrel]

Bonsoir,

Envoyé par SQLpro

C'est la pire des choses à faire...

SQLpro, vous allez un peu vite en besogne. Dans ma réponse à vh, j’ai indiqué comment éviter les NULLS (et par la même occasion les valeurs par défaut).

Au passage (vh, vous pouvez sauter ce point de détail !), qu’on les appelle valeurs nulles ou marqueurs, ça ne change rien au fait que SQL est obligé de se frotter à la logique ternaire et de se prendre les pieds dans le tapis.

Je sais que Ted Codd préférait parler de marqueurs parce que, je cite et traduis :

Envoyé par Ted Codd

1. Les SGBD ne traitent pas les marqueurs comme des valeurs.
2. Il y a maintenant deux types de marqueurs, quand précédemment il y avait un seul type de null.
3. Certains langages hôtes traitent d’objets appelés "nulls" dont la signification diffère sensiblement des marqueurs dans les bases de données.
4. "Marked" et "unmarked" sont de meilleurs adjectifs en anglais que ne le sont "nulled", "un-nulled" et "nullified".

(Cf. E. F. Codd. The Relational Model for Database Management: Version 2 (Reading, Mass.: Addison-Wesley, 1990, pages 173-174).

— Fin du point de détail —

N’oublions pas que l’optimiseur d’un SGBD SQL a de gros problèmes avec NULL, concernant ses règles de transformation des expressions (Query Rewrite). Là où en logique binaire il y a tautologie ou contradiction, ça n’est plus vrai en logique ternaire. Par exemple "p OU NON p" est toujours vrai, sauf en logique ternaire. Qu’advient-il des lois de De Morgan ? Etc. On aura beau triturer les tables de vérité, on finira toujours par être coincé.

Je conseille à vh de méditer la discussion entamée par nat-0-0 et plutôt animée, dans laquelle nous avons pas mal débattu :

http://www.developpez.net/forums/sho...d.php?t=267568

En aparté : Dans cette discussion, vous mettiez en cause le nombre de jointures dû à la façon de procéder de Hugh Darwen : n’oubliez pas que le jour où sera utilisé "The TransRelational Model" (qui agit au niveau physique), la jointure de 20 tables ne prendra que 2 fois le temps d'une jointure de 10 tables (performance linéaire) et on pourra dire "Adieu index devenus inutiles !"

Envoyé par SQLpro

Petite anecdote... Un jour j'audite une application dans laquelle les développeurs avient tout mis en NOT NULL. Il s'agissait d'un site web de vente de lingerie fine.... Or la date de naissance des clients étaient rarement renseigné et comme c'était du NOT NULL ils mettaient par défaut 01/01/1900.

Quand on a voulu faire des statistiques de vente on s'est aperçut que la moyenne d'âge des clients qui achetaient des strings avec dentelles était de 84 ans. Je leur ait alors dit de se reconvertir dans le DAMART !!!

Anecdote sympathique. Mais :

1) Ces développeurs n’on pas dû tester leurs requêtes et devaient être bien tendres. Dans le cas précis des dates pouvant prendre ce genre de valeur par défaut, on doit avoir le réflexe de coder "... Date_xxx <> '01/01/1900'...". Il s’agit au demeurant d’un bug rapide à corriger, puisqu’on ne touche pas à la structure des tables, simplement aux requêtes. J’espère que votre audit ne vous a pas fait découvrir des anomalies du niveau de ce qui a provoqué le crash en 1999 de la sonde Mars Climate Orbiter sur la planète rouge parce qu’une équipe utilisait des unités de mesure anglo-saxonnes, tandis que l’autre utilisait le système métrique...

2) Comme je l’ai précisé dans cette discussion et dans celle avec nat-0-0 il y a quand même d’autres solutions que l’utilisation des NULLS et des valeurs par défaut pour traiter de l’absence de l’information.

[SQLpro]

N’oublions pas que l’optimiseur d’un SGBD SQL a de gros problèmes avec NULL, concernant ses règles de transformation des expressions (Query Rewrite). Là où en logique binaire il y a tautologie ou contradiction, ça n’est plus vrai en logique ternaire. Par exemple "p OU NON p" est toujours vrai, sauf en logique ternaire. Qu’advient-il des lois de De Morgan ? Etc. On aura beau triturer les tables de vérité, on finira toujours par être coincé.

en fait pas si compliqué que cela, car les moteurs considèrent NULL FIRST ou NULL LAST ce qui revient à considérer de manière interne que les NULL sont des valeurs avant ou après toutes les autres... Ce que d'ailleurs la récent norme SQL:1999 indique en ajoutant la possibilité d'ordonnancer les NULL dans les résultats des requêtes :

Code :

1
2
SELECT ...
ORDER BY ... NULL FIRST

A +

[fsmrel]

Bonsoir,

Envoyé par SQLpro

en fait pas si compliqué que cela, car les moteurs considèrent NULL FIRST ou NULL LAST ce qui revient à considérer de manière interne que les NULL sont des valeurs avant ou après toutes les autres

Je ne vois pas en quoi une verrue NULL FISRT au niveau de la présentation des résultats viendrait résoudre un problème de fond, qui niche au cœur des optimiseurs et de la logique.

Et même au niveau de la présentation des résultats, est-ce que cela a par exemple pour conséquence que AVG(montant) est désormais toujours égal à SUM(montant)/COUNT(quantité) ?

Cela n’empêchera pas que EXISTS donne pour résultat TRUE ou FALSE, quand ce résultat attendu devrait être UNKNOWN. Comme dit Chris Date (qui m’a confirmé son excellente santé et est plus débordé que jamais), "EXISTS Is Not “Exists”!" (Cf. C. J. Date, Relational Database Writings 1985-1989 (Reading, Mass.: Addison-Wesley, 1990, pages 339-356). 17 pages consacrées à démontrer les anomalies provoquées par l’utilisation de EXISTS avec SQL.

Vous dites que les NULL sont des valeurs avant ou après toutes les autres : donc vous les considérez comme des valeurs. En conséquence, les dépendances fonctionnelles concernées perdent leur statut. Etc.

Quitte à me répéter je dis qu’il y a quand même d’autres solutions que l’utilisation des NULLS pour traiter de l’absence de l’information.

[vh]

Envoyé par fsmrel

Quitte à me répéter je dis qu’il y a quand même d’autres solutions que l’utilisation des NULLS pour traiter de l’absence de l’information.

je suis tout à fait d'accord avec ça et je me permets de reposer ma question

Envoyé par vh

et en partant sur des champs qui autorisent le "NULL" on pourrait trouver où le code applicatif aurait des lacunes en cherchant les "null" dans les tables puisque normallement le code ne devrait pas le permettre.
Est ce que mon raisonnement tient la route ?

le "problème" des NOT NULL est qu'il faut indiquer une valeur par défaut et donc en cas de problème dans le code cette valeur par défaut aurait l'apparence d'une valeur tout à fait normale

[fsmrel]

Envoyé par vh

le "problème" des NOT NULL est qu'il faut indiquer une valeur par défaut et donc en cas de problème dans le code cette valeur par défaut aurait l'apparence d'une valeur tout à fait normale.

Je ne connais pas tous les SGBDR, mais j’ai toujours codé NOT NULL en n’utilisant la clause DEFAULT (facultative) qu’avec parcimonie (par exemple avec des attributs de type CHAR).

Vous observerez que dans mon exemple (message du 20/02/2007), j’ai codé NOT NULL sans clause DEFAULT : ceci m’oblige à toujours fournir une valeur. SGBD utilisé : SQL Server.

[vh]

Envoyé par fsmrel

SGBD utilisé : SQL Server.

et voilà, nous n'avons pas les même valeurs
avec MySQL point de "NOT NULL" sans valeur pas défaut

donc je vais concerver l'idée de tout mettre en "NULL" et code de tel façon que si un NULL apparait, c'est obligatoirement un fonctionnement anormal

merci à vous fsmrel, pendant ces quelques jours sur le forum vous avez fait évoluer ma façon de développez plus rapidement que par 6 ans d'expérience donc merci à vous et merci à developpez.com qui a permis cette magie

[fsmrel]

Envoyé par vh

avec MySQL point de "NOT NULL" sans valeur pas défaut

Je veux bien vous croire, mais ceux qui font MySQL feraient bien d’étudier la théorie relationnelle...

Je suis heureux d'avoir pu vous donner quelque éclairage. Bonne continuation,

Fsmrel