THE PROBLEM
Let us in the context of a work that makes social benefit its members from different departments. The inclusion of these services is obviously a website!
Each entry requires several pages of information various HTML, so that in case of abandonment by the Internet or interruption of system data consistency is in a state for.
We want to know:
Which service has registered member
if its inclusion is partial or complete
For example, we have the services:
Child care
housekeeper
aide soignante
A first draft could lead us to model in this way:
Of the MPD is:
This means that 3 tables are necessary for handling data, including a cost of significant application for the relational engine.
Is there way to simplify this by integrating all these data within the Member table?
Attribuons a code for each state:
| | Registration partial | Full registration |
| Child care | 1 | 2 |
| housekeeper | 3 | 4 |
| aide soignante | 5 | 6 |
But how to "return" these different values and take advantage of?
It would be easy to predict as many field boolean that state to decide.
But in case of adding a service should change the relational model and it would be very penalizing.
In fact a simple solution is to see these different "levels" status on an area binary-only 0 and 1:
1 2 3 4 5 6
bit1 bit2 bit3 bit4 bit5 bit6
This means that we can use a bit with all that we need states to manage. In our case, a number sufficient to 6 bits, but as you might think that the situation may evolve, all take a whole the greatest dimension, the cost of storage will not be affected significantly and treatments are very fast.
The MCD and the MPD arising are then very simple:
The question remains, how to write primitives of different SQL to manipulate this information ...
/ * ================================================ ======== * / / * Creation of the table: ADHERE * /
/* ======================================================== */
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)
THE REQUESTS SQL
In order to simplify life, decide to assign numeric code as directly Powers 2 to our different services and their associated states:
| | Registration partial | Full registration |
| Child care | 1 | 2 |
| housekeeper | 4 | 8 |
| aide soignante | 16 | 32 |
What are the members whose condition No. 4 (partial listing for housekeeper) is true?
select ADH_ID from ADHERENT
where ADH_ETAT_INS & 4 = 4
What is the value of state 2 (full registration for child care) of member 5?
select (ADH_ETAT_INS & 2) / 2 as ETAT2
from ADHERENT
where ADH_ID = 5
That you can also write:
select case when ADH_ETAT_INS & 2 = 2 then 'TRUE' else 'FALSE' end as ETAT2
from ADHERENT
where ADH_ID = 5
Give the value of state 32 (full registration nursing aide) for all members?
select ADH_ID, (ADH_ETAT_INS & 32) / 32 as ETAT32
from ADHERENT
which can also write:
select ADH_ID, case when ADH_ETAT_INS & 32 = 32 then 'TRUE' else 'FALSE' end as ETAT32
from ADHERENT
Getting the member 4 to state 1 if this is not the case
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
Another way, even more elegant, is to use a view to tabulariser data and believe sure you use a table instead of a single field.
The creation of this view can be done in the following manner:
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
Therefore, question the data is the simplest 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 |
IN CONCLUSION
This method is very powerful because of its simplicity and its brevity. It allows to limit the size of storage. Some tests are allowed to show its extreme speed.
