Utilisation d'une colonne pour stocker différentes valeurs logique

Pour des raisons de performances, il est logique de penser qu'il vaut mieux utiliser un champ au sein d'une table qu'une jointure avec une autre table lorsque des informations doivent être stockées.
Cette manière de faire peut conduire à 'dénormaliser' le modèle relationnel inhérent à une base de données digne de ce nom.
Ce cas peut cependant se présenter notamment lorsque l'information stockée est plus liée à la cuisine interne du fonctionnel qu'à des nécessités de stockage de l'information.
Un exemple concret nous aidera mieux à comprendre de quoi il s'agit....

Article lu   fois.

L'auteur

Site personnelSite personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

LE PROBLÈME

Mettons nous dans le cadre d'une oeuvre sociale qui fait bénéficier ses adhérents de différents services. L'inscription de ces services se fait bien évidemment par un site web !

Chaque inscription nécessite plusieurs pages HTML de renseignements divers, si bien qu'en cas d'abandon par l'internaute ou d'interruption du système la cohérence des données se trouve dans un état pendant.

Nous voulons savoir :

  • quel service s'est inscrit l'adhérent
  • si son inscription est partielle ou complète

Par exemple, nous avons les services :

  • garde d'enfant
  • femme de ménage
  • aide soignante

Un premier jet peut nous conduire à modéliser de la sorte :

Image non disponible

Dont le MPD est :

Image non disponible

Ce qui signifie que 3 tables sont nécessaires à la manipulation des données, dont un coût de requête non négligeable pour le moteur relationnel.

Y a t-il moyen de simplifier cela en intégrant toutes ces données au sein même de la table Adhérent ?

Attribuons un code pour chacun des états :

  Inscription partielle Inscription complète
garde d'enfant 1 2
femme de ménage 3 4
aide soignante 5 6

Mais comment faire pour "rentrer" ces différentes valeurs et les exploiter de façon optimale ?
Il serait très simple de prévoir autant de champ de type booléen que d'états à statuer.
Mais en cas d'ajout d'un service il faudrait modifier le modèle relationnel et cela serait très pénalisant.

En fait une solution simple consiste à reporter ces différents "niveaux" d'état sur une zone binaire ne comportant que des 0 et des 1 :

 
Sélectionnez

  1      2      3      4      5      6
bit1   bit2   bit3   bit4   bit5   bit6

Ce qui signifie que nous pouvons utiliser un nombre comportant autant de bit que nous avons besoin d'états à gérer. Dans notre cas un nombre à 6 bits suffirait, mais comme l'on peut penser que la situation risque d'évoluer, autant prendre un entier de la plus grande dimension, le coût de stockage n'en sera pas affecté de manière sensible et les traitements seront très rapides.

Le MCD et le MPD qui en découlent relèvent alors d'une grande simplicité :

Image non disponible

Reste à savoir comment écrire les primitives des différentes requêtes SQL pour manipuler ces informations...

 
Sélectionnez

/* ======================================================== */
/* création de la table : ADHERENT                          */
/* ======================================================== */

create table ADHERENT
(
    ADH_ID        numeric               identity,
    ADH_NOM       char(36)              null    ,
    ADH_PRENOM    char(36)              null    ,
    ADH_ETAT_INS  int                   null    ,
    constraint PK_ADHERENT primary key (ADH_ID)
)

/* ======================================================== */
/* population de la table ADHERENT                          */
/* ======================================================== */

insert into ADHERENT (ADH_NOM, ADH_PRENOM, ADH_ETAT_INS) values ('DUPONT', 'Jacques', 5)
insert into ADHERENT (ADH_NOM, ADH_PRENOM, ADH_ETAT_INS) values ('DURAND', 'Paul', 10)
insert into ADHERENT (ADH_NOM, ADH_PRENOM, ADH_ETAT_INS) values ('DUBOIS', 'Pierre', 18)
insert into ADHERENT (ADH_NOM, ADH_PRENOM, ADH_ETAT_INS) values ('DUVAL', 'André', 9)
insert into ADHERENT (ADH_NOM, ADH_PRENOM, ADH_ETAT_INS) values ('DUHAMEL', 'Philippe', 42)

LES REQUÊTES SQL

Afin de nous simplifier la vie, décidons d'attribuer comme code numérique, directement les puissances de 2 à nous différents services et leurs états associés :

  Inscription partielle Inscription complète
garde d'enfant 1 2
femme de ménage 4 8
aide soignante 16 32

Quels sont les adhérents dont l'état n°4 (inscription partielle pour femme de ménage) est à vrai ?

 
Sélectionnez

select ADH_ID from ADHERENT
where ADH_ETAT_INS & 4 = 4

Quelle est la valeur de l'état 2 (inscription complète pour garde d'enfant) de l'adhérent 5 ?

 
Sélectionnez

select (ADH_ETAT_INS & 2) / 2 as ETAT2
from ADHERENT
where ADH_ID = 5

Que l'on peut écrire aussi :

 
Sélectionnez

select case when ADH_ETAT_INS & 2 = 2 then 'TRUE' else 'FALSE' end as ETAT2
from ADHERENT
where ADH_ID = 5

Indiquer la valeur de l'état 32 (inscription complète aide soignante) pour tous les adhérents ?

 
Sélectionnez

select ADH_ID, (ADH_ETAT_INS & 32) / 32 as ETAT32
from ADHERENT

que l'on peut aussi écrire :

 
Sélectionnez

select ADH_ID, case when ADH_ETAT_INS & 32 = 32 then 'TRUE' else 'FALSE' end as ETAT32
from ADHERENT

Faire passer l'adhérent 4 à l'état 1 si cela n'est pas le cas

 
Sélectionnez

if (SELECT ADH_ETAT_INS & 1 from ADHERENT where ADH_ID = 4) = 1
begin
   UPDATE ADHERENT SET ADH_ETAT_INS = ADH_ETAT_INS + 1
   where ADH_ID = 4
end

Une autre manière, encore plus élégante, consiste à utiliser une vue pour tabulariser les données et faire croire en sorte que l'on utilise une table au lieu d'un seul champ.

La création de cette vue peut se faire de la façon suivante :

 
Sélectionnez

create view V_ADH_ETAT as 
select ADH_ID, 
case when ADH_ETAT_INS &  1 =  1 then 'TRUE' else 'FALSE' end as GE_PARTIELLE,
case when ADH_ETAT_INS &  2 =  2 then 'TRUE' else 'FALSE' end as GE_COMPLETE,
case when ADH_ETAT_INS &  4 =  4 then 'TRUE' else 'FALSE' end as FM_PARTIELLE,
case when ADH_ETAT_INS &  8 =  8 then 'TRUE' else 'FALSE' end as FM_COMPLETE,
case when ADH_ETAT_INS & 16 = 16 then 'TRUE' else 'FALSE' end as AS_PARTIELLE,
case when ADH_ETAT_INS & 32 = 32 then 'TRUE' else 'FALSE' end as AS_COMPLETE
from ADHERENT

Dès lors, interroger ces données relève de la plus simple expression :

 
Sélectionnez

select * from V_ADH_ETAT
 
Sélectionnez

ADH_ID  GE_PARTIELLE GE_COMPLETE FM_PARTIELLE FM_COMPLETE AS_PARTIELLE AS_COMPLETE 
------- ------------ ----------- ------------ ----------- ------------ ----------- 
1       TRUE         FALSE       TRUE         FALSE       FALSE        FALSE
2       FALSE        TRUE        FALSE        TRUE        FALSE        FALSE
3       FALSE        TRUE        FALSE        FALSE       TRUE         FALSE
4       TRUE         FALSE       FALSE        TRUE        FALSE        FALSE
5       FALSE        TRUE        FALSE        TRUE        FALSE        TRUE

EN CONCLUSION

Ce moyen est très performant du fait de sa simplicité et de sa concision. Il permet de limiter la taille de stockage. Quelques tests on permis de montrer son extrême rapidité.

Mais il convient d'en limiter l'usage au strict nécessaire : de la cuisine interne pour, par exemple piloter l'état d'une transaction ou encore des modes d'affichage, mais certainement pas de l'information à disposition de l'utilisateur, sauf à passer par une vue restituant correctement les informations...

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

Livres
SQL - développement
SQL - le cours de référence sur le langage SQL
Avant d'aborder le SQL
Définitions
SGBDR fichier ou client/serveur ?
La base de données exemple (gestion d'un hôtel)
Modélisation MERISE
Mots réservés du SQL
Le SQL de A à Z
Les fondements
Le simple (?) SELECT
Les jointures, ou comment interroger plusieurs tables
Groupages, ensembles et sous-ensembles
Les sous-requêtes
Insérer, modifier, supprimer
Création des bases
Gérer les privilèges ("droits")
Toutes les fonctions de SQL
Les techniques des SGBDR
Les erreur les plus fréquentes en SQL
Les petits papiers de SQLPro
Conférence Borland 2003
L'héritage des données
Données et normes
Modélisation par méta données
Optimisez votre SGBDR et vos requêtes SQL
Le temps, sa mesure, ses calculs
QBE, le langage de ZLOOF
Des images dans ma base
La jointure manquante
Clefs auto incrémentées
L'indexation textuelle
L'art des "Soundex"
Une seule colonne, plusieurs données
La division relationnelle, mythe ou réalité ?
Gestion d'arborescence en SQL
L'avenir de SQL
Méthodes et standards
Les doublons
SQL Server
Eviter les curseurs
Un aperçu de TRANSACT SQL V 2000
SQL Server 2000 et les collations
Sécurisation des accès aux bases de données SQL Server
Des UDF pour SQL Server
SQL Server et le fichier de log...
Paradox
De vieux articles publiés entre 1995 et 1999 dans la défunte revue Point DBF

  

Copyright © 2003 Frédéric Brouard. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.