Remodéliser votre base de données,

étude de cas en SQL Server par StringBuilder

Le 22 septembre 2015, par StringBuilder

0PARTAGES

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

Code sql :

Sélectionner tout

1
2
3
4
5
6
7
8
9
10
11
12
  
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é :

Code sql :

Sélectionner tout

1
2
3
4
  
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) :

Code sql :

Sélectionner tout

1
2
3
4
5
6
7
8
9
10
11
  
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”.

Code sql :

Sélectionner tout

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
  
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 :

Code sql :

Sélectionner tout

1
2
3
4
5
6
7
8
9
10
11
  
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 :

Code sql :

Sélectionner tout

1
2
3
4
5
6
7
8
9
10
11
12
  
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 :

Code sql :

Sélectionner tout

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
  
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.

Code sql :

Sélectionner tout

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
  
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 !

Une erreur dans cette actualité ? Signalez-nous-la !

Votre nom : Votre e-mail :

Décrivez l'erreur que vous souhaitez porter à notre connaissance :

Identifiez-vous

Créer un compte

Remodéliser votre base de données,

étude de cas en SQL Server par StringBuilder

Identifiant
Mot de passe

Mot de passe oublié ?

Identifiez-vous

Créer un compte

Remodéliser votre base de données, étude de cas en SQL Server par StringBuilder

Remodéliser votre base de données,

étude de cas en SQL Server par StringBuilder