Les schémas SQL sont des unités de code à la manière des bibliothèque de code ou des espaces de noms (qui ne sont qu’une version moderne de la notion de library…). Cela confère à la structure d’une base de données, une certaine modularité.
Seule différence avec le monde itératif, SQL ne permet qu’un seul niveau de « schéma » et un objet de la base ne saurait se passer de lui. En fait toutes tables, vue, procédures… est toujours créée dans un schéma et non directement attaché à la base.
Vous pouyez créer autant de SCHEMA SQL que vous voulez.
Il est de bon ton de créer des schémas SQL verticaux et transversaux.
La verticalité s’entend par grandes fonctions de la base. Ainsi dans un ERP on ne mélangera pas les tables de production, des tables du commercial ou encore de celles de la comptabilité.
Par transversalité, j’entends des données qui peuvent être utilisées par de nombreux autres modules comme des tables de nomenclature, de référence, de paramétrage (table des codes postaux, des civilités…).
Outre la modularisation des objets de la base, il existe deux autres intérêts majeurs à la création des SCHEMA SQL :
1) la possibilité d’attribuer des privilèges à des utilisateurs au niveau du SCHEMA. Cela évite d’avoir à en créer pour chaque objet et chaque utilisateur et de revenir régulièrement sur le sujet à chaque nouvel ajout.
2) lorsque l’on créé des objets au sein du schéma, le modélisateur de la base n’a pas à se soucier de l’ordre logique de création des objets. Il peut ainsi commencer par créer une vue alors que même la table référencée par la vue n’existe pas encore….
Démonstration… (SQL Server 2000)
EXEC sp_addlogin 'CNX_SQL', 'Bienvenue chez les chtis'
GO
EXEC sp_addlogin 'CNX_SCH', 'Bienvenue chez les chtis'
GO
CREATE DATABASE DB_TEST_SCHEMA
GO
USE DB_TEST_SCHEMA
GO
EXEC sp_grantdbaccess 'CNX_SQL', 'USER_SQL'
GO
EXEC sp_grantdbaccess 'CNX_SCH', 'SCH_SQL'
GO
-- création d'un schema SCH_SQL
CREATE SCHEMA SCH_SQL
GRANT SELECT
ON SCH_SQL.V_CLIENT_CLI
TO USER_SQL
CREATE VIEW SCH_SQL.V_CLIENT_CLI
AS
SELECT *
FROM SCH_SQL.T_CLIENT_CLI
CREATE TABLE SCH_SQL.T_COMMANDE_CMD
(CMD_ID INT NOT NULL PRIMARY KEY,
CLI_ID INT NOT NULL
FOREIGN KEY
REFERENCES SCH_SQL.T_CLIENT_CLI (CLI_ID),
CMD_DATE DATETIME NOT NULL)
CREATE TABLE SCH_SQL.T_CLIENT_CLI
(CLI_ID INT NOT NULL PRIMARY KEY,
CLI_NOM CHAR(32) NOT NULL)
-- fin de création du schéma
Démonstration SQL Server 2005 :
CREATE LOGIN CNX_SQL
WITH PASSWORD = 'Bienvenu chez les chtis';
GO
CREATE DATABASE DB_TEST_SCHEMA
GO
USE DB_TEST_SCHEMA
GO
CREATE USER USER_SQL FOR LOGIN CNX_SQL
GO
-- création d'un schema SCH_SQL
CREATE SCHEMA SCH_SQL
GRANT SELECT
ON OBJECT::SCH_SQL.V_CLIENT_CLI
TO USER_SQL
CREATE VIEW SCH_SQL.V_CLIENT_CLI
AS
SELECT *
FROM SCH_SQL.T_CLIENT_CLI
CREATE TABLE SCH_SQL.T_COMMANDE_CMD
(CMD_ID INT NOT NULL PRIMARY KEY,
CLI_ID INT NOT NULL
FOREIGN KEY
REFERENCES SCH_SQL.T_CLIENT_CLI (CLI_ID),
CMD_DATE DATETIME NOT NULL)
CREATE TABLE SCH_SQL.T_CLIENT_CLI
(CLI_ID INT NOT NULL PRIMARY KEY,
CLI_NOM CHAR(32) NOT NULL)
-- fin de création du schéma
--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
