| |
Developing And Utilizing SQL Naming Standards
By Craig Borysowich
Expert Author
Article Date: 2009-02-10
The format for stored procedure names will be:
Pwwddddd
where: p the prefix indicating the type of SQL operation as shown below, must be in upper case.
ww first two characters of the window name (the processing grouping), must be in lower case.
ddddd 5 characters as determined by the developer, a meaningful description of the operation, must be in lower case.
Prefix
| Description
| S
| Select the return results.
| U
| Update the database.
| I
| Insert new data into the database.
| D
| Delete data from the database.
| X
| Other, i.e., special function
|
Table 9-1 - Stored Procedures
SQL Script
SQL scripts may be stored in multiple locations in the System Development Environment (SDE) hierarchy. The location of the SQL script in the hierarchy is indicative of its function. For example, SQL scripts associated with the methods author would be stored in the SQL subdirectory of the methods author directory.
The format for SQL script filenames will be:
uu...u.sql
where: uu...u is a user defined object identifier, limited to 8 characters, may consist of multiple words separated by underscores.
sql is the standard suffix.
Database
The format for SQL database names will be:
uu...u_e
where: uu...u is a user defined database identifier, limited to 16 characters, may consist of multiple words separated by underscores.
e a suffix indicating the environment.
Suffix
| Environment
| D
| Development
| I
| Integration Test
| S
| System Test
| A
| Acceptance Test
| T
| Training
| P
| Production
|
Table 9-2 - Database
Tables, Views, and Indices
The format for Tables, Views and Indices will be:
uu..u_xn
where: uu..u a user defined name, limited to 15 characters, may consist of multiple words separated by underscores.
x a suffix indicating the type of object.
n may vary from 1 - 9, a - z. Increments with each table or view created for the same base.
Suffix
| Type
| (no suffix)
| Table
| In
| Index
| Vn
| View
|
Table 9-3 - Tables, Views, and Indices
SQL Column
The format for SQL columns names will be:
uu...u_xx
where: uu...u a user defined column identifier, limited to 15 characters, total length may not exceed 18 characters (following the ANSI standard), depends on length of suffix, upper case, may consist of multiple words separated by underscores.
xx optional suffix, upper case, recommended suffixes listed in the Appendix of this guide.
Cursors
The format for SQL cursor names will be:
uu..u_cr
where: uu...u user-defined cursor identifier, lower case, may be multiple words separated by underscores, limited to 40 characters.
Physical Database Objects
The format for the names of physical database object such as devices, segments, etc., will be:
uu..u_sss
where: uu...u user-defined name, limited to 14 characters, upper case, may consist of multiple words separated by underscores.
sss suffix indicating database object type.
Suffix
| Type
| data
| Data Device
| log
| Log Device
| seg
| Segment
| spc
| Tablespace
| mir
| Mirror Device
|
Table 9-4 - Physical Database Objects
Comments
About the Author:
Craig Borysowich has over 18 years of Technology Consulting experience with both public and private sector clients, including ten years in Project Leadership roles. His extensive background in working with large scale, high-profile systems integration and development projects that span throughout a customer’s organization allows him to help consulting organizations world-wide to deliver better quality projects more consistently.
|
|
