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 !

Vous avez lu gratuitement 0 articles depuis plus d'un an.
Soutenez le club developpez.com en souscrivant un abonnement pour que nous puissions continuer � vous proposer des publications.

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