Introduction

About this Document

This document is intended for professionals working with relational databases at Stanford University. It describes the naming conventions - standards and guidelines - to be used for naming database objects. For each object to be named, it is indicated if the naming convention is a guideline. If there is no specific mention it is a standard by default.

It covers naming conventions for Sybase as well as Oracle database objects. Wherever possible, the guidelines are kept consistent for both DBMSs.

In the case of an application software package, the convention should be adhered to as much as possible. However, it is not warranted to customize the application software package solely to meet these naming conventions.

Change Log

Version Date Change description

Note:
Changes introduced with the last revision are marked with sidebars.

General Guidelines for naming

Database object names should follow the general naming conventions listed below. Wherever possible, naming conventions are kept consistent between Oracle and Sybase.


• Oracle database object names are limited to a maximum of 30 characters with the exception of database names which are 8 characters.
• Sybase database object names are limited to a maximum of 30 characters.
• Object names follow all rules for the DBMS being used.
• Object names contain only the letters [a-z] [0-9] and underscore Î_Ì.
• Object names must start with a letter.
• Object names will follow the rules for standard abbreviations as documented in the "Naming standards for Data Models" document (available on the Data Administration Web page).
• Object names are all lowercase except where explicitly indicated.
• Names cannot be Oracle or Sybase reserved words.

Conventions

In this document, naming conventions are presented as templates composed of one or more of the following:


• underscores: the elements of a name are separated by underscores to improve readability.
• substitutions: represented by italicized characters, indicating that the element is composed of characters provided by the user.
• constants: represented by non-italicized characters.
• brackets: indicate that the enclosed character is included only under specific conditions as defined.




For example, the naming for Sybase triggers is presented as:
tablename_[d][i][u]_ trg

• The italicized characters "tablename" indicate that appropriate text will be substituted.
• The underscores demarcate the nameÌs three elements.
• The brackets around the characters "d", "i" and "u " indicate that these characters will be included or omitted based upon some criteria.
• The suffix "trg" is a constant; all trigger names will end with this string.

Sybase Naming Conventions

SQL Servers

SQL Servers should be named: name_vvv where

Note:

In the past, Stanford adhered to a single server per node convention. With more new applications in the database environment, there may be multiple servers per node and it is not a good practice to include the node (host) name in the server name.

Databases

A database is a collection of tables holding related information.


Databases should be named: aaa[_v]_ttt where

Keep in mind: Database names are lowercase.


Note: This naming convention is consistent with naming convention used for Oracle database.

Tables

Since it is difficult to predict the many different types of databases and determine a suitable naming standard for tables at Stanford University, the following should be used as a guideline for naming tables. Additional conventions can be adopted by each application team as long as they do not conflict with any of the defined naming standards.

Table names (also know as internal names) are the same as the entity names in the logical data model upon which they are based, with the following exceptions:


• Use underscores for spaces
  
• Use appropriate standard terms




For more detail on standard terms and internal entity naming conventions, please refer to the "Naming Standards for Data Models" document on the Data Administration Web page.


Here are some additional considerations:


• A work table is a table where content is discarded upon successful completion of a program, script or job and left intact after failure. It should be suffixed with _w and should contain the name of the associated table.
  
• A report table is the same as a work table except the content is left intact regardless of success or failure of the program, script or job. It should be suffixed with _r and should also contain the name of the associated table.
  
• A history table is a a table is used to archive records from active tables. It should be suffixed with _h and should also contain the name of the associated table.
  
• If a table is in tempdb but has to persist beyond the session, Work Tables, Report Tables and History Tables should contain the process id if multiple instances are present at any given time.
  
• Do not use reserved words as the table name (Example for a reserved word is user).
  
• For Sybase, work tables should be created in tempdb where they will be deleted when the server is shutdown.

Views

Since it is difficult to anticipate the many different types of databases and determine a suitable naming standard for views at Stanford University, the following should be used as a guideline for naming views. Additional conventions can be adopted by each application team as long as they do not conflict with any of the defined naming standards.


View names (also know as external names) are the same as the entity names in the logical data model upon which they are based, with the following exceptions:

• Use underscores for spaces
• Use appropriate standard terms




For more detail on standard terms and internal entity naming conventions, please refer to the "Naming Standards for Data Models" document on the Data Administration Web page.


Here are some additional considerations:

• All terms (separate by underscores) start with an uppercase letter
• Add suffix _v to designate this as a view.

Columns

It is difficult to anticipate the many different types of databases and, therefore, define a suitable naming standard for columns at Stanford University. Therefore, the following should be used as a guideline for naming columns. Additional conventions can be adopted by each application team as long as they do not conflict with any of the defined naming standards.


Column names are the same as the attribute names in the logical data model upon which they are based, with the following exceptions:

• Use underscores for spaces
• Use appropriate standard terms




For more detail on standard terms and attribute naming conventions, please refer to the "Naming Standards for Data Models" document on the Data Administration Web page.


In addition, there are a few additional considerations as noted below:

• Foreign key column names maintain the same names as in the parent table, that means same as the primary key column names.
• Multiple instances of a column name in the same table get an appropriate prefix added to the column name. The prefix is based on the relationship given in the logical data model. For example, there are two 'part identifier' columns in the 'part' table. Their column names are 'part_id_parent' and 'part_id_child'.
• When abbreviating a word in a column name, always abbreviate it the same for all columns the database.
• Do not use reserved words as the column name (Example for a reserved word is user).

Indexes

An index is a physical construct that contains a list of values for a column or columns and points to the locations of rows containing the values.

Indexes should be named: tablename_[u][c] _ idx_n where

Examples: fin_roll_summary_uc_idx_1 (a unique, clustered index on the primary key of the fin_roll_summary table)

User-defined data types

A user-defined data type is a custom defined data type which is different or enhanced from the system supplied data types. The power of user-defined data types is to enforce domain restrictions by binding rules and defaults to the user-defined data types.

User-defined data types should be named as: ss_name_typ where

Examples: ch_carrier_id_typ, in_branch_number_typ, sm_rebate_typ

Physical Data Types

In some DBMSs, you can specify a particular data type more than one way. Whenever possible use the ANSI Standard definitions for the data type. However, this standard does not restrict the use of non ANSI standard data types.

The following table shows the mapping of ANSI standard data types to the Sybase implementation.

Rules

A rule specifies the domain of acceptable values for a particular column.

Rules should be named as: name_rul where

Examples: branch_number_rul, employee_number_rul, carrier_id_rul

Defaults

A default specifies a value to insert in a column (or in all columns of a user-defined datatype) if no value is explicitly supplied at insert time.

Defaults should be named as: name_def where

Examples: country_def, commission_percentage_d

Constraints

A constraint is a restriction to enforce integrity when data is manipulated in a table.

Constraints should be named as: tablename_[c]_ type where

Examples: carrier_pky, employee_c_unq, meal_chk

Triggers

A trigger is a special form of a stored procedure that goes into effect when a table is manipulated.

Triggers should be named as: tablename_[d][i][u]_ trg where

Examples: air_segment_i_trg, group_client_d_trg, employee_i_trg

Stored Procedures

A stored procedure is a collection of SQL statements that are stored in a database and can be executed by name.

Stored procedures should be named as: name_action_proc where

Examples: carrier_get_proc, group_client_add_proc, employee_transfer_proc


Note: For Sybase, system-wide stored procedures developed by Stanford University must be prefixed sp_su_. The suffix _proc is not present for system-wide stored procedures.

SQL Server logins and Sybase Users

In general, SQL Server login and Sybase User names should map to the user's respective Unix account name. The naming convention for Unix logins is f[m]llll where f is the first character of the user's firstname, m is the first character of the user's middlename and is optional, and llll is the user's lastname.


When adding a login with sp_addlogin, always specify the parameter fullname as"lastname, firstname". The default database should be a different database than the master database.


When adding a user with sp_adduser, always check if the user already has a SQL Server login (by checking for lastname, firstname), then add the user with the same user name. This process will ensure that the user has only one username across multiple SQL servers.


Database Owners will be established with a user name of name_dbo where name is the name of the database or application and dbo is a constant.

Database Devices (Logical)

A database device is any piece of disk or file in the file system that you use to store databases and database objects. The term "device" can refer to raw partition or a file in the file system.


Database devices should be named as: devicetype_n where

Examples: log_ 1, data_5


Keep in mind:

• All Sybase devices at Stanford University should be raw partitions except tempdb. Tempdb should always be setup as a file system for performance reasons.
• Physical device names are different than database device names and follow a different naming convention which is not documented here.

Segments

A segment is a named subset of database devices available to a particular database.


It is difficult to anticipate the many different types of databases and, therefore, define a suitable naming standard for segments at Stanford University. The following should be used as a guideline for naming segments.


For small databases (approximately 200 mb or less), the indices and tables are most likely stored on the default segment.


For larger databases, data and indices will be separated based on the analysis done by the DBA and the application team. Segments which hold multiple indexes should be called index_n. Segments which hold multiple tables should be called data_n.


If there is a need to isolate a table or index into its own segment, it should be called objectname_n.


Note: n is a sequential identifier beginning with 1.


Examples: index_1, data_23, person_1

Oracle Naming Conventions

SIDs and Databases

SIDs and databases should be named: AAA[n]_TTT where

Examples: OOD1_DEV, AMS2_RPT


Note:

• The SID and database names can be a maximum of 8 characters.
• SID names have to be unique across the network.
• The corresponding database name must always match the SID name.
• SID and database names are always uppercase.

Tables

See Sybase naming conventions

Views

See Sybase naming conventions

Columns

See Sybase naming conventions

Indexes

See Sybase naming conventions

Physical Data Types

In some DBMSs, you can specify a particular data type more than one way. Whenever possible use the ANSI Standard definitions for the data type. However, this standard does not restrict the use of non ANSI standard data types.


The following table shows the mapping of ANSI standard data types to the Oracle implementation.

Constraints

A constraint is a rule that restricts the values for one or more columns in a table.


Constraint names should be named: tablename_type_desc where

Example:

emp_p_empno Constraint on primary key column empno for table emp


Note:

Constraints should not be used to enforce uniqueness. Instead, a unique index should be created to accomplish the result of the unique constraint.

Triggers

A trigger is a stored block of SQL statements that is associated with a table. ORACLE automatically executes a trigger when a specified SQL statement is issued against the table.


Trigger should be named: tablename _x_y _trg where

Examples: employee_b_di_trg, department_a_diu_trg

Stored Procedures

See Sybase naming conventions

Packages

A package is an encapsulated collection of related procedures, functions, and other program objects stored together in the database.


Packages should be named: name_pkg where

Examples: employee_maint_pkg, contract_update_pkg

Roles

A role is a set of privileges that can be granted to users or to other roles.

Roles should be named: name _rol where

Examples: security_admin_rol

Profiles

Profiles are used to control the use of system resources and are associated with one or many users.


Profiles should be named: name _prf where

Examples: adhoc_rpt_prf

Tablespaces

A tablespace is an allocation of space in the database that contains database objects.


Tablespaces should be named: nametype where

Examples: gldetaild, glsummaryx


Note:

• The tablespace that stores the rollback segment should be named rbs.
• The tablespace that stores the temporary segments should be named temp.

Rollback Segments

A rollback segment is an object that is used by ORACLE to store data necessary to reverse, or undo, changes made by transactions.


Rollback segments should be named: rbsnn where

Example: rbs05

Data Files

Data files are physical files belonging to a tablespace.


Data files should be named:
(dir name)/tablespacename_SIDNAME_nn.dbf where

Example: ...gldetaild_FIN1_DEV_01.dbf

Control Files

Control files should be named:
(dir name)/SIDNAME_n.ctl where

Example: ...FIN1_DEV_1.ctl

Redo log Files

Redo log files should be named:

(dir name)/redo_groupnn_membernn.log where

Example: ...log_01_02.rdo

Parameter Files

Parameter files should be named:

(dir name)/initSIDNAME.ora where

Example: ...initOOD1_DEV.ora

Archive Files

Archive files should be named:

<dir name>/oracle_SIDNAME_n_m.arc where

Example: ...oracle_OOD1_DEV_1_2.arc

Service Names

Service names only apply to SQL*Net V2.0. A service name is the logical name that identifies a SID on a particular host to which a connection can be made. They should be named the same as the SID names.

Database Links

Database links can be created as private or public. At Stanford University, private links will not be used unless there are application-specific requirements to do so. Only public database links are used. They should be named the same as the SID names.

Snapshot Names

A snapshot is a table that contains the results of a query of one or more tables or views, often located on a remote database.


Snapshot names should be named: SIDNAME_tablename where

Examples: OOD1_DEV_donor, AMS2_RPT_student

Summary of Naming Conventions

Sybase

ORACLE