Using a strong naming convention help to find problems…

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 * * * * *

Laisser un commentaire