In a debate I have had with Joe Celko by emailing, I criticised his naming convention he expose in « SQL programming Style » (Morgan Kaufmann). Let me talk about this case…
First just see my naming convention at : http://www.sqlspot.com/Norme-de-developpement.html
(If you are not used to read franch, a short version is at the end of this paper).
The problem is : How to find all views having a table column using it ? In fact we can have views from views from views…. from tables…. Here is the difficulties….
And the answer is : strong namming convention + CTE =>
Example : Searching all views of any level referencing STE_TVA_CODE_PAYS column.
WITH V_VIEWS (VIEW_SCHEMA, VIEW_NAME, VIEW_DEFINITION, VIEW_LEVEL)
AS
(
-- views having a column nammed STE_TVA_CODE_PAYS
SELECT TABLE_SCHEMA AS VIEW_SCHEMA,
TABLE_NAME AS VIEW_NAME,
VIEW_DEFINITION,
0 AS VIEW_LEVEL
FROM INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION LIKE '%STE_TVA_CODE_PAYS%' --> the column to find
UNION ALL
-- views having a view based on a column nammed STE_TVA_CODE_PAYS
SELECT V1.TABLE_SCHEMA AS VIEW_SCHEMA,
V1.TABLE_NAME AS VIEW_NAME,
V1.VIEW_DEFINITION,
V2.VIEW_LEVEL + 1
FROM INFORMATION_SCHEMA.VIEWS V1
INNER JOIN V_VIEWS V2
ON V1.VIEW_DEFINITION
LIKE '%' + V2.VIEW_SCHEMA + '.' + V2.VIEW_NAME + '%'
WHERE V2.VIEW_DEFINITION LIKE '%STE_TVA_CODE_PAYS%'
AND V2.VIEW_SCHEMA + '.' + V2.VIEW_NAME <> V1.TABLE_SCHEMA + '.' + V1.TABLE_NAME
)
SELECT *
FROM V_VIEWS;
No doubt about the answer :
1) table are T_
2) views are V_
3) UDF are F_
4) cols are ???_ (??? = trigram)
I tested it in a very big database (almots 600 tables and about 900 views limited to level 2). The answer was :
VIEW_SCHEMA VIEW_NAME VIEW_LEVEL
--------------- ----------------------------------- ----------
S_GST V_SOCIETE_STE 0
S_GST V_ENTREPRENEUR_VIVRELEC 0
S_GST V_SOCIETE_PARTENAIRE 0
S_GST V_CONTRAT_SINISTRE_CSN 1
S_USR V_UTILISATEUR_USR 1
S_GST V_PERSONNE_INTERLOCUTEUR_PIC 1
* * *
As summary about my namming convention :
0) use a postif unique trigramme (3 letters) at the end of the table
WHY ?
You need an abreviation to join tables. Use the postfix trigramme.
You must distinct columns like « DATE », « TYPE »… Prefix alls the columns of the entity by the postfix trigramm.
When doing that in the relationnal model (at the conceptual level) the foreign keys will appear clearly by the fact that columns having a prefix trigramme different from the postif trigramme of the table are outer informations !
1) use only CAPITAL letters for all SQL object.
WHY ? This is usefull to distingate in a listing the code that run on client and this running on RDBMS
2) use T_ for table V_ for views.
WHY ? This can be usefull to see where there is a table used. Codd says to never use directly table and use views instead. This is very true because when you need to chage the schema the effort of refactoring is smaller with views than with direct table queries.
So viewing if there is a table or a view in a query is very important.
3) use a second prefix to specifiy wich type of table :
T_E_ for entity table
T_J_ for join tables (generated automatically by the data modeling toll
T_R_ for references tables (like zip code)
T_A_ for administratives tables
T_S_ for systems user tables
WHY ?
T_R_* tables dont need to be reindexed because the datas are quasi statics.
So in a generic SQL script to reindex all tables theses will be ignored.
Same for T_A_ or T_S_
As reverse T_J_* tables need to be reindexed more often than entities one.
This is also usefull for appliying some privilèges when schemas are missing.
last but not leat, all my REF tables are on the same design like this :
T_R_ZIPCODE_ZPC
(ZPC_ID INT NOT NULL PRIMARY KEY,
ZPC_CODE CHAR(8) NOT NULL,
ZPC_LIB VARCHAR(64) NOT NULL,
ZPC_ORDER INT NOT NULL UNIQUE,
ZPC_BASE BOOLEAN NOT NULL DEFAULT 0)
WHY ?
First le me explain ORDER and BASE :
ORDER is to realise an explicit user order in the SELECT statement.
I am horribly amused when surfing on the net I see drop down lists of countries beginning by afghanistan…. I think the need to have afghanistant in first position is ergonomic by the fact that this is the country in wich there is the greatest number of internautes…
BASE is to say this line in the table belongs to the DATABASE and cannot be removed or update. In some process the guide to decide what to do are based on values. If thoses values disapear or are updated what will arrive to that program ?
And finally the design of all REF tables exactly the same style is to make a full complete unique view of all the REF values by concatenanting all ref table in an UNION. The view can be dynamically generated and regenerated as well, and all the datas placed in a client buffer to avoid querying thoses datas on the server !
Exemplae (SQL Server 2000) :
USE master
GO
IF EXISTS(SELECT *
FROM master.INFORMATION_SCHEMA.SCHEMATA
WHERE CATALOG_NAME = 'DB_ORSYS')
DROP DATABASE DB_ORSYS
GO
CREATE DATABASE DB_ORSYS
GO
USE DB_ORSYS
GO
/* ============================================================ */
/* Nom de la base : ORSYS - exemple table référentiel */
/* Nom de SGBD : Microsoft SQL Server 7.x b */
/* Date de création : 09/03/2006 19:31 */
/* ============================================================ */
-- domaine SQL booléen
if exists(select 1 from dbo.systypes where name ='DMN_B_BOOLEEN')
execute sp_droptype DMN_B_BOOLEEN
go
execute sp_addtype DMN_B_BOOLEEN, 'bit', 'null'
go
-- domaine SQL code de référence (table de référence)
if exists(select 1 from dbo.systypes where name ='DMN_C_REF_CODE')
execute sp_droptype DMN_C_REF_CODE
go
execute sp_addtype DMN_C_REF_CODE, 'char(16)', 'null'
go
-- domaine SQL libellé de référence (table de référence)
if exists(select 1 from dbo.systypes where name ='DMN_C_REF_LIB')
execute sp_droptype DMN_C_REF_LIB
go
execute sp_addtype DMN_C_REF_LIB, 'char(32)', 'null'
go
-- identifiant
if exists(select 1 from dbo.systypes where name ='DMN_N_ID')
execute sp_droptype DMN_N_ID
go
execute sp_addtype DMN_N_ID, 'int', 'not null'
go
-- entier cardinal (supérieur à 0)
if exists(select 1 from dbo.systypes where name ='DMN_N_INT_POS_PLUS')
execute sp_droptype DMN_N_INT_POS_PLUS
go
if exists(select 1 from dbo.sysobjects where name ='R_N_INT_POS_PLUS' and type = 'R')
drop rule R_N_INT_POS_PLUS
go
execute sp_addtype DMN_N_INT_POS_PLUS, 'int', 'null'
go
create rule R_N_INT_POS_PLUS
as @N_INT_POS_PLUS >= 1
go
execute sp_bindrule R_N_INT_POS_PLUS, DMN_N_INT_POS_PLUS
go
/* ============================================================ */
/* Table : T_R_TITRE_TIT */
/* ============================================================ */
CREATE TABLE T_R_TITRE_TIT
(
TIT_ID DMN_N_ID identity,
TIT_CODE DMN_C_REF_CODE not null,
TIT_LIBELLE DMN_C_REF_LIB not null,
TIT_BASE DMN_B_BOOLEEN not null,
TIT_ORDRE DMN_N_INT_POS_PLUS not null
constraint PK_T_R_TITRE_TIT primary key (TIT_ID)
)
go
/* ============================================================ */
/* Table : T_R_SEXE_SEX */
/* ============================================================ */
CREATE TABLE T_R_SEXE_SEX
(
SEX_ID DMN_N_ID identity,
SEX_CODE DMN_C_REF_CODE not null,
SEX_LIBELLE DMN_C_REF_LIB not null,
SEX_BASE DMN_B_BOOLEEN not null,
SEX_ORDRE DMN_N_INT_POS_PLUS not null
constraint PK_T_R_SEXE_SEX primary key (SEX_ID)
)
go
/* ============================================================ */
/* Table : T_R_TYPE_TELEPHONE_TTL */
/* ============================================================ */
CREATE TABLE T_R_TYPE_TELEPHONE_TTL
(
TTL_ID DMN_N_ID identity,
TTL_CODE DMN_C_REF_CODE not null,
TTL_LIBELLE DMN_C_REF_LIB not null,
TTL_BASE DMN_B_BOOLEEN not null,
TTL_ORDRE DMN_N_INT_POS_PLUS not null
constraint PK_T_R_TYPE_TELEPHONE_TTL primary key (TTL_ID)
)
go
/* ============================================================ */
/* Table : T_R_FORME_SOCIETE_FST */
/* ============================================================ */
CREATE TABLE T_R_FORME_SOCIETE_FST
(
FST_ID DMN_N_ID identity,
FST_CODE DMN_C_REF_CODE not null,
FST_LIBELLE DMN_C_REF_LIB not null,
FST_BASE DMN_B_BOOLEEN not null,
FST_ORDRE DMN_N_INT_POS_PLUS not null
constraint PK_T_R_FORME_SOCIETE_FST primary key (FST_ID)
)
go
/**********************************************************
CRÉATION DE LA VUE DES RÉFÉRENCES (RÉFÉRENTIEL)
Cette procédure est injectée, s'exécute et se supprime
***********************************************************
F .BROUARD - ORSYS - 2005-03-03
***********************************************************/
CREATE PROCEDURE P_CREATE_VIEW_REF
AS
DECLARE @TAB_NAME sysname
DECLARE @QUERY VARCHAR(8000)
SET @QUERY = ''
DECLARE @TRIGRAMME CHAR(3)
DECLARE C_REFTABNAME CURSOR
FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'T_R#_%' ESCAPE '#'
FOR READ ONLY
OPEN C_REFTABNAME
FETCH C_REFTABNAME INTO @TAB_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TRIGRAMME = SUBSTRING(@TAB_NAME,LEN(@TAB_NAME)-2,3)
SET @QUERY = @QUERY + 'SELECT ''' + @TRIGRAMME+''' AS REF_TRG, '
+ @TRIGRAMME + '_ID AS REF_ID , '
+ @TRIGRAMME + '_CODE AS REF_COD, '
+ @TRIGRAMME + '_LIBELLE AS REF_LIB, '
+ @TRIGRAMME + '_BASE AS REF_BASE , '
+ @TRIGRAMME + '_ORDRE AS REF_ORD'
+ ' FROM ' + @TAB_NAME
+ ' UNION '
FETCH C_REFTABNAME INTO @TAB_NAME
END
CLOSE C_REFTABNAME
DEALLOCATE C_REFTABNAME
-- rajouter le CREATE VIEW et supprimer le dernier 'UNION'
SET @QUERY = 'CREATE VIEW V_REF AS '+SUBSTRING(@QUERY, 1, LEN(@QUERY) - 6)
PRINT(@QUERY) --### pour contrôle
EXEC (@QUERY)
GO
/**********************************************************
execution de la proc stock
**********************************************************/
EXEC P_CREATE_VIEW_REF
GO
-- insertion de données dans les tables du référentiel :
-- T_R_TITRE_TIT : titres des acteurs
SET IDENTITY_INSERT T_R_TITRE_TIT ON
INSERT INTO T_R_TITRE_TIT (TIT_ID, TIT_CODE, TIT_LIBELLE, TIT_BASE, TIT_ORDRE) VALUES (1, 'M.', 'Monsieur', 1, 1)
INSERT INTO T_R_TITRE_TIT (TIT_ID, TIT_CODE, TIT_LIBELLE, TIT_BASE, TIT_ORDRE) VALUES (2, 'Mme.', 'Madame', 1, 2)
INSERT INTO T_R_TITRE_TIT (TIT_ID, TIT_CODE, TIT_LIBELLE, TIT_BASE, TIT_ORDRE) VALUES (3, 'Mlle.', 'Mademoiselle', 1, 3)
SET IDENTITY_INSERT T_R_TITRE_TIT OFF
-- T_R_SEXE_SEX : sexe des acteurs
SET IDENTITY_INSERT T_R_SEXE_SEX ON
INSERT INTO T_R_SEXE_SEX (SEX_ID, SEX_CODE, SEX_LIBELLE, SEX_BASE, SEX_ORDRE) VALUES (1, 'MASCULIN', 'Homme', 1, 1)
INSERT INTO T_R_SEXE_SEX (SEX_ID, SEX_CODE, SEX_LIBELLE, SEX_BASE, SEX_ORDRE) VALUES (2, 'FEMININ', 'Femme', 1, 2)
SET IDENTITY_INSERT T_R_SEXE_SEX OFF
-- T_R_TYPE_TELEPHONE_TTL : type de téléphone
SET IDENTITY_INSERT T_R_TYPE_TELEPHONE_TTL ON
INSERT INTO T_R_TYPE_TELEPHONE_TTL (TTL_ID, TTL_CODE, TTL_LIBELLE, TTL_BASE, TTL_ORDRE) VALUES (1, 'TEL', 'Téléphone filaire', 1, 1)
INSERT INTO T_R_TYPE_TELEPHONE_TTL (TTL_ID, TTL_CODE, TTL_LIBELLE, TTL_BASE, TTL_ORDRE) VALUES (2, 'GSM', 'Téléphone portable', 1, 2)
INSERT INTO T_R_TYPE_TELEPHONE_TTL (TTL_ID, TTL_CODE, TTL_LIBELLE, TTL_BASE, TTL_ORDRE) VALUES (3, 'FAX', 'Télécopie', 1, 3)
SET IDENTITY_INSERT T_R_TYPE_TELEPHONE_TTL OFF
-- T_R_FORME_SOCIETE_FST : forme juridique des sociétés
SET IDENTITY_INSERT T_R_FORME_SOCIETE_FST ON
INSERT INTO T_R_FORME_SOCIETE_FST (FST_ID, FST_CODE, FST_LIBELLE, FST_BASE, FST_ORDRE) VALUES(1, 'SA', 'Société anonyme', 0, 1)
INSERT INTO T_R_FORME_SOCIETE_FST (FST_ID, FST_CODE, FST_LIBELLE, FST_BASE, FST_ORDRE) VALUES(2, 'SARL', 'SA à responsabilité limitée', 0, 2)
INSERT INTO T_R_FORME_SOCIETE_FST (FST_ID, FST_CODE, FST_LIBELLE, FST_BASE, FST_ORDRE) VALUES(3, 'ASSOC', 'Association Loi 1901', 0, 3)
INSERT INTO T_R_FORME_SOCIETE_FST (FST_ID, FST_CODE, FST_LIBELLE, FST_BASE, FST_ORDRE) VALUES(4, 'COLLOC', 'Collectivité locale', 0, 4)
INSERT INTO T_R_FORME_SOCIETE_FST (FST_ID, FST_CODE, FST_LIBELLE, FST_BASE, FST_ORDRE) VALUES(5, 'ADMIN', 'Administration', 0, 5)
SET IDENTITY_INSERT T_R_FORME_SOCIETE_FST OFF
-- test de la vue :
SELECT *
FROM V_REF
ORDER BY REF_TRG, REF_ORD
—
Frédéric BROUARD, Spécialiste modélisation, bases de données, optimisation, langage SQL.
Le site sur le langage SQL et les S.G.B.D. relationnels : http://sqlpro.developpez.com/
Expert SQL Server http://www.sqlspot.com : audit, optimisation, tuning, formation
* * * * * Enseignant au CNAM PACA et à l’ISEN à Toulon * * * * *