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

Mettons-nous dans le cadre d'une œuvre 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 :

Dont le MPD est :

Ce qui signifie que trois 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 :

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 champs 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 :

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

Ce qui signifie que nous pouvons utiliser un nombre comportant autant de bits 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é :

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

/* ======================================================== */
/* 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)

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 :

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

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 ?

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

Que l'on peut écrire aussi :

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 ?

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

que l'on peut aussi écrire :

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

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 :

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 :

select * from V_ADH_ETAT

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

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 ont 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…