Remodéliser votre base de données,

étude de cas en SQL Server par StringBuilder

Il arrive souvent que vous soyez face à une base de données mal pensée à l'origine, et qui a mal évolué.
Vous vous retrouvez avec des tables "mamouth", au nombre incalculable de colonnes nulles.

Le problème, c'est que de nombreux outils accèdent souvent à cette base de données, et qu'il vous semble insurmontable de devoir tout réécrire afin de réagencer quelques tables dans votre base.

Pourtant, même si la tâche reste ardue, il n'est aucunement nécessaire de réécrire vos applications pour utiliser des tables dont vous aurez modifié la structure.
En effet, la plupart des SGBD modernes permettent :

• de faire des vues
  
• de faire du CRUD dessus
  
• d'étendre les possibilités du CRUD sur les vues à l'aide de triggers positionnés directement sur les vues
  

Les exemple de cet article sont basés sur SQL Server. La syntaxe peut varier d'un SGBD à l'autre. Cet article n'a pas pour vocation de fournir des exemple s'adaptant à tous les SGBD, mais avant tout à proposer une méthodologie.

Soit une base de données de contacts, très mal modélisée :

• Une seule table
  
• Des colonnes nullables
  
• Une sémantique redondante entre certaines colonnes
  

create table contact
(
id int primary key not null identity,
nom varchar(50) not null,
prenom varchar(50) not null,
telephone_maison varchar(10) null check (telephone_maison like replicate('[0-9]', 10)),
telephone_mobile varchar(10) null check (telephone_mobile like replicate('[0-9]', 10)),
telephone_boulot varchar(10) null check (telephone_boulot like replicate('[0-9]', 10)),
unique (nom, prenom)

);


On remarque tout de suite que la présente de 3 colonnes "numéro de téléphone", nullables, est une aberration : en effet, si on souhaite rechercher une personne par un numéro de téléphone, il est impossible d'utiliser le moindre index. Et la présence de NULL dans une table, c'est la preuve d'une modélisation hasardeuse.

On souhaite donc effectuer des recherches simples en saisissant un numéro de téléphone.
La recherche doit alors retourner toutes les lignes de contact où l’un des trois numéros de téléphone est égal au numéro recherché.
La table étant très mal modélisée, on a au départ tenté :


select *
from contact
where telephone_maison = '0123456789' or telephone_mobile = '0123456789' or telephone_boulot = '0123456789';


Mais malgré la création d’index, cette requête est très lente (le OR est non sargable) et franchement pas très jolie. Surtout qu’on a pour objectif, d'ici quelques temps, d’ajouter un “telephone_conjoint”, donc il faudra modifier la requête, recréer des index.

On a donc créé une vue permettant d’effectuer cette recherche plus facilement, et de façon plus performante (le union permet d'utilisation des index) :


create view contact_telephone (id, nom, prenom, type_telephone, numero_telephone)
as
select id, nom, prenom, 'MAISON', telephone_maison
from contact
union
select id, nom, prenom, 'MOBILE', telephone_mobile
from contact
union
select id, nom, prenom, 'BOULOT', telephone_boulot
from contact;


Après mure réflexion, cette modélisation est parfaitement pourrie, et on souhaite l’améliorer.
Les NULL polluent l'utilisation des index, rendent la maintenance des données difficile (dédoublonnage par exemple) : comment mettre en place une clause d'unicité portant sur trois colonnes et toutes les lignes d'une table ?

Seulement, on a un programme qui permet de faire du CRUD sur la table “contact”, afin de remplir notre base de contacts.
Et une autre application web, qui fait des recherches à l’aide de la vue “contact_telephone”, permettant aux utilisateur de trouver rapidement le contact à partir de son numéro.

Ce programme étant écrit en COBOL, et son auteur étant parti à la retraite à l’époque où vous ne saviez même pas encore lire, vous préférez éviter d’avoir à le toucher, même si, à terme, vous serez certainement amenés à le remplacer.
Quant au site web, c'est un stagiaire qui vous a fait ça en PHP entre deux parties de jeu vidéo, et vous n'osez pas vous approcher du code, tellement il est incompréhensible.

Pourtant, vous n'allez pas abandonner. Vous pouvez rendre votre base de données propre et performante, en la remodélisant.

Voici donc comment procéder.

On va, dans un premier temps, créer les tables “propres”.


create table personne
(
id int primary key not null identity,
nom varchar(50) not null,
prenom varchar(50) not null,
unique (nom, prenom)
);

create table telephone
(
id int primary key not null identity,
personne_id int not null references personne(id),
type_telephone varchar(6) not null check (type_telephone in ('MAISON', 'MOBILE', 'BOULOT')),
numero_telephone varchar(10) not null check (numero_telephone like replicate('[0-9]', 10))
);


Ce nouveau modèle des données est loin d'être parfait. Il faudrait notamment avoir une table "type_telephone" plutôt qu'une contrainte sur une colonne littérale. Il s'agit ici simplement d'un exemple.

Puis recopier notre table poubelle dedans :


set identity_insert personne on;
insert into personne (id, nom, prenom)
select id, nom, prenom
from contact;
set identity_insert personne off;

insert into telephone (personne_id, type_telephone, numero_telephone)
select id, type_telephone, numero_telephone
from contact_telephone
where numero_telephone is not null;


Et on va maintenant transformer la table poubelle en vue toute propre :


drop table contact;
go

create view contact (id, nom, prenom, telephone_maison, telephone_mobile, telephone_boulot)
as
select p.id, p.nom, p.prenom, t1.numero_telephone, t2.numero_telephone, t3.numero_telephone
from personne p
left outer join telephone t1 on t1.personne_id = p.id and t1.type_telephone = 'MAISON'
left outer join telephone t2 on t2.personne_id = p.id and t2.type_telephone = 'MOBILE'
left outer join telephone t3 on t3.personne_id = p.id and t3.type_telephone = 'BUREAU';
go


Seul hic, si on tente de créer/modifier/supprimer des lignes, ça ne fait pas trop ce qu’on veut :

Lorsqu'on tente d'enregistrer un nouveau contact :

Msg 4405, Level 16, State 1, Line 75
View or function 'contact' is not updatable because the modification affects multiple base tables.

Lorsqu’on tente de mettre à jour un numéro de téléphone existant :

(0 row(s) affected)

Lorsqu'on tente de supprimer un contact :

Msg 4405, Level 16, State 1, Line 79
View or function 'contact' is not updatable because the modification affects multiple base tables.

On va donc devoir créer quelques triggers pour gérer correctement les différents cas :


create trigger trg_contact_ins
on contact
instead of insert
as
begin
insert into personne (nom, prenom)
select nom, prenom
from inserted;

insert into telephone (personne_id, type_telephone, numero_telephone)
select personne.id, 'MAISON', inserted.telephone_maison
from inserted
inner join personne on personne.nom = inserted.nom and personne.prenom = inserted.prenom
where telephone_maison is not null
union
select personne.id, 'MOBILE', inserted.telephone_mobile
from inserted
inner join personne on personne.nom = inserted.nom and personne.prenom = inserted.prenom
where telephone_mobile is not null
union
select personne.id, 'BOULOT', inserted.telephone_boulot
from inserted
inner join personne on personne.nom = inserted.nom and personne.prenom = inserted.prenom
where telephone_boulot is not null;
end;
go

create trigger trg_contact_del
on contact
instead of delete
as
begin
delete telephone where personne_id in (select id from deleted);
delete personne where id in (select id from deleted);
end;
go

create trigger trg_contact_upd
on contact
instead of update
as
begin
update personne
set nom = i.nom, prenom = i.prenom
from inserted i
where personne.id = i.id and (personne.nom <> i.nom or personne.prenom <> i.prenom);

insert into telephone (personne_id, type_telephone, numero_telephone)
select i.id, 'MAISON', i.telephone_maison from inserted i left outer join deleted d on d.id = i.id where d.telephone_maison is null and i.telephone_maison is not null
union
select i.id, 'MOBILE', i.telephone_mobile from inserted i left outer join deleted d on d.id = i.id where d.telephone_mobile is null and i.telephone_mobile is not null
union
select i.id, 'BOULOT', i.telephone_boulot from inserted i left outer join deleted d on d.id = i.id where d.telephone_boulot is null and i.telephone_boulot is not null;

update telephone
set numero_telephone = tmp.numero_telephone
from (
select i.id personne_id, 'MAISON' type_telephone, i.telephone_maison numero_telephone from inserted i inner join deleted d on d.id = i.id where d.telephone_maison <> i.telephone_maison
union
select i.id, 'MOBILE', i.telephone_mobile from inserted i inner join deleted d on d.id = i.id where d.telephone_mobile <> i.telephone_mobile
union
select i.id, 'BOULOT', i.telephone_boulot from inserted i inner join deleted d on d.id = i.id where d.telephone_boulot <> i.telephone_boulot
) tmp
where telephone.personne_id = tmp.personne_id and telephone.type_telephone = tmp.type_telephone;

delete telephone
from inserted
inner join deleted on deleted.id = inserted.id
where (deleted.telephone_maison is not null and inserted.telephone_maison is null and telephone.type_telephone = 'MAISON')
or (deleted.telephone_mobile is not null and inserted.telephone_mobile is null and telephone.type_telephone = 'MOBILE')
or (deleted.telephone_boulot is not null and inserted.telephone_boulot is null and telephone.type_telephone = 'BOULOT')
end;
go


Et enfin, la vue “contact_telephone” est réécrite, de façon très simple.


drop view contact_telephone;
go

create view contact_telephone (id, nom, prenom, type_telephone, numero_telephone)
as
with type_telephone (libelle)
as
(
select 'MAISON'
union all
select 'MOBILE'
union all
select 'BOULOT'
)
select p.id, p.nom, p.prenom, type_telephone.libelle, t.numero_telephone
from personne p
cross join type_telephone
left outer join telephone t on t.personne_id = p.id and t.type_telephone = type_telephone.libelle;


Et voilà ! Maintenant, vos programmes existants travaillent dans une jolie base bien modélisée sans même s'en rendre compte !
Et les performances, malgré la taille imposante des triggers, seront au rendez-vous, je vous le garanti !