Naming Objects: General Rules

Structured IT organizations usually adopt their own database design and naming conventions.

I think that this depends largely on the objective and the strategy of the company. 

It is very important that standards and conventions are accepted by the majority of the team otherwise it wouldn’t be a standard at all. 

Standardization is critical to the successful achievement of quality in the system engineering process. The standards defined in this document are intended as a communication tool that allows developers to work together in a cohesive manner. Having established standards:

1. Provides consistency across all projects

2. Eliminates guesswork on the part of new team members as to how things are supposed to be done

3. Improves the maintainability of models and systems over time (i.e. less guesswork)

4. Reduces long-term costs (because systems are easier to maintain)

5. Promotes reusability (because objects within the Designer repository can be easily maintained)

6. Simplify the overall management and administration of the database server

 

These are some standards that I have adopted before to teams I worked  with. I talked with some organizations before who preferred codes for the names for security reasons. These codes will have no meaning to people who can have access to the document outside the organization. But this something that they define for the team.

 

All rules established in this section must be followed when naming objects, unless specifically accepted by individual object descriptions in later sections.

The individual sections contain any additional rules specific to an object and one or more examples to illustrate usage.

Acronyms/Abbreviations

Full words should always be used, unless an acronym can be substituted or an abbreviation is required for length considerations.

Use of Significant Words

Use root words wherever possible. Dropping the suffix (-age, -ence, -ance, -ing, -ant, -ity, -any, -ive, -ary, -ony, -aty, -ory, -ation, -ment, -ed, -tion) will generally leave the root word. Do this only if the remaining root word is meaningful as is.

Words such as who, what, when, or where are not allowed.

The use of articles and prepositions (such as the or of), adjoining words or conjunctions (such as and or or), qualifying words such as new or old, and numbers should be on an exception basis.

Use of Oracle Reserved Words

A name cannot be an Oracle reserved word. See Oracle Documentation for reserved words – Oracle Reserved Words.

Oracle reserved words have a special syntactic meaning to Oracle or the PL/SQL language so should not be used for schema or program object names.

Additionally, the names of SQL built-in functions should not be used for schema or program object names.

Use of Special Characters

Special characters, including brackets, quotation marks, question marks, dollar signs ($), hash signs (#) and slashes are not permitted. Dashes are allowed only in legitimately hyphenated words.

 

Use of Special Characters

Special characters, including brackets, quotation marks, question marks, dollar signs ($), hash signs (#) and slashes are not permitted. Dashes are allowed only in legitimately hyphenated words.

Use of Underscores

When an Oracle object is viewed in the Oracle data dictionary it will be in all uppercase letters regardless of the case that was typed in the CREATE statement. The problem with all uppercase names is that when more than one word exists in a name the words tend to run together.

Underscores will not be used except to separate words (or acronyms) or separate prefixes and suffixes from the base names of schema objects, object parts or PL/SQL elements.

 

1. Server Model Objects

This section describes naming conventions for objects that will normally be encountered or defined in the Design phase of development.

Tables

Table Names

• A table should be descriptive.

• Table names should have a minimum of 4 characters and a maximum of 30.

• A table name must not duplicate the name of another object in the database.

• Module name can be added as prefix to the table name.

• A table representing an entity should have a suffix of TE (example: module_employee_TE).

• A table representing a reference should have a suffix of TR (example: module_inventory_type_TR)..

• A table representing historical data should have a suffix of TS (example: module_sales_transactions_TS)

Table Comments

To ensure that appropriate meta-data is included in the Oracle data dictionary, all tables must have a comment. Comments can be entered through Designer, a DDL SQL COMMENT statement or via a third-party Oracle tool (e.g. Toad). This comment should describe the basic information stored in the table. This will allow us to generate a concise data Dictionary later.

Columns

Column Names

• A column name should be descriptive.

• A column name must be the same as the name of the attribute from which the column was mapped, with the spaces translated to underscores.

• Not more than 30 characters

• The column name must not be a plural.

• Names that are 5 characters or less in length will not be abbreviated.

• If the column is not based on an attribute, then it should be named using the naming standard set forth for attributes with the exception that an underscore is used instead of a space between segments.

• Column names will not be prefixed with the table short name.

• Columns in the same table or view cannot have the same name. However, columns in different tables or views can have the same name.

• Number of columns within a table can range from 1 to 254.

• Audit capabilities are required in all systems developed. Every entity must contain the following four audit attributes:

• CREATED BY

• DATE_CREATED

• UPDATED BY

• DATE_UPDATED

Column Comments

To ensure that appropriate meta-data is included in the Oracle data dictionary, all columns must have a comment. Comments can be entered through Designer, a DDL SQL COMMENT statement or via a third-party Oracle tool (e.g. Toad). This comment should describe the basic information stored in the column. This will allow us to generate a concise data Dictionary later.

Constraints

• Constraints can be defined for table and column to enforce certain rules.

• Constraint name length should not exceed 32 characters.

• Constraint name should be preceded by the constraint identifier and an underscore followed by the table name where the constraint is associated and n. ‘n’ represents the numeric reference of the constraint in relation to the table. The constraint identifier are the following: (example: PK_CODE0)

 

   

 

 

IDENTIFIER	CONSTRAINT TYPE
NN	Not Null
UN	Unique
PK	Primary Key
FK	Foreign Key
CK	Check

Indexes

• Create index to speed up the retrieval of rows and to enforce uniqueness on values in a column.

• Index names should reflect the table they belong to, their function and any other important data in their name.

• Index name should always be preceded by a prefix ‘aX_’ then the name of the table where the index is associated and n. ‘n’ represents the numeric reference of the index in relation to the table. ‘a’ denotes the type of index. The following are the prefix for the different type of index.

• (example: IX_CUSTOMER_ID0)

 

   

 

 

PREFIX	DESCRIPTION
FX	Foreign key index
PX	Primary key index
UX	Unique key Index
CX	Cluster key index
HX	Hash key index
BX	Bitmap key index
IX	Non unique index

Sequences

• Sequence is a database object that automatically generates a sequence number.

• The naming convention for sequence is <table_name>_SQ. Where <table_name> is the table for which the sequence will provide values to the primary key.

• Additionally, sequences may need to reflect functionality because a single sequence may be used with multiple tables. In this case, the naming convention is <common functionality>_SQ.

Example: Three tables are generated from sub-types of the entity MODEL. They each represent a different type of model but to avoid potential key conflicts, the decision is to use one sequence for all the primary keys because they will all be mapped to a common cross-reference table. So the sequence name is MODEL_ID_SQ since the sequence will generically represent a model identifier.

• Sequence names should have a minimum of 8 characters.

Views

• A virtual table — that is a table that does not physically exist in its own right.

• View name should always have a suffix of ‘_VW’ after the name that describes the information found in the view.

• The naming convention for views is <table name>_<criteria>_VW. Where <table name> is the name of the root (or ‘master’) table the view is based on. The <criteria> qualifier is optional. The qualifier, if used, should give the end users a clear idea of the purpose and contents of the view. Use the <criteria> qualifier if:

• Using the table name alone is not unique.

• The view is based on a join of 2 or more tables.

• The view contains a where clause.

• The view is unusually complex.

• The view is a summary.

Example: EMPLOYEES_VW is a view on the EMPLOYEES table

 

Materialized Views

• The naming convention for materialized views is:

<materialized view name>_<criteria>_MV

Where <materialized view name> is the name of the root (or ‘master’) table the materialized view is based on. The <criteria> qualifier is optional. The qualifier, if used, should give the end users a clear idea of the purpose and contents of the materialized view. Use the <criteria> qualifier if:

• Using the table name alone is not unique

• The materialized view is based on a join of 2 or more tables

• The materialized view contains a where clause

• The materialized view is unusually complex

• The materialized view is a summary

• Materialized view names should have a minimum of 6 characters.

Examples: CUSTOMERS_ACTIVE_MV provides information on only active CUSTOMERS.

EMPLOYEES_DEPARTMENTS_MV is a view joining the EMPLOYEES table to the DEPARTMENTS table.

 

1. Other Database Objects

Database Links

• Database link names should have a maximum of 128 characters.

• The naming convention for database links is:

@<remote database>.<host name>.<domain>

Where <remote database> is the name of the remote database, <host name> is the name of the server where the remote database resides and <domain> is the owner of the database.

• Names of database links can also contain periods (.) and ‘at’ signs (@).

Example: @CLIENT1.LNX01.COMPANY.COM

 

Databases

Export Files

• The Oracle Export utility provides a simple way of extracting the object definitions and table data from an Oracle database and storing them in an Oracle binary-format Export dump file.

• The naming convention for export dump files is:

<sid>_YYMMDD_n.DMP

Where <sid> is the SID of the database which is the target of the export.

Use n if there are multiple dumps.

• A log file using the same naming convention with the suffix LOG should always be generated to accompany any Oracle export.

• On completion of the export, the values of the export parameters used should be listed at the top of the log file for future reference.

Example: PROJECT_040101.DMP

 

Functions

• A function is a named program unit that takes parameter and returns a computed value.

• PL/SQL functions will normally be sub-programs within a PL/SQL package. Function names should not contain verbs that indicate how the function will obtain the value that it returns e.g. GET_STATUS. A function name should indicate what the return value will be and nothing more.

• The naming convention for packaged functions is:

FN_<application_name>_<noun> or FN_<application_name>_<adjective>

Where <noun> or <adjective> indicates what the function will return. <application_name> is the short name of the application to which the function is associated. Function name should always be preceded with ‘FN_’.

Example: FN_TOTAL_UNITS_CHECK is a stand-alone function that returns TRUE if the total unit count is greater than zero.

Instances

• The naming convention for instances is:

<database_name><instance type>

Where <database_name > is the name of the database to which the instance is related. The <instance type> qualifier is identifies the type of system and can take one of the following values:

DEV – Development system

QA – Quality Assurance system

PROD – Production system

• Each database has just one instance.

• Instance names should have a maximum of 9 characters.

Example: COM-DEV

Packages

• Package is a database object that groups logically related PL/SQL types, objects and subprograms.

• The naming convention for packages is:

PKG_<package name>

Where <package name> is the name of the table or other object on which the package operates. Package name should always be preceded with ‘PKG_’.

• Each package will contain procedures and functions that operate on a table or other object.

• Where a package does not operate on data in a particular table or object, a name describing the purpose of the package should be used.

• PL/SQL code should take advantage of the many benefits packages offer over stand-alone procedures and functions:

• Enforced information hiding

• Object-oriented design

• Top-down design

• Object persistence

• Performance improvement

• Package names should have a minimum of 8 characters.

Examples: PKG_VESSELS is a package containing various procedures for the VESSELS table.

PKG_STRINGS is a package that provides functions to handle string manipulations.

Procedures

• A named PL/SQL block which can take parameters and be invoked to perform a specific action.

• PL/SQL procedures will normally be sub-programs within a PL/SQL package.

• The naming convention for packaged procedures is:

SP_<apllication_name>_<noun> and/or SP_<application_name>_<verb>

Where <noun> or <verb> provide a brief explanation of what the procedure does. <application_name> is the short name of the application to which the procedure is associated. Procedure name should always be preceded with ‘SP_’.

• By default, packages used to manipulate data in a single table may be created with 5 packaged procedures that act as methods for all access to that table. The default packaged procedures are:

• select_all – Returns all records

• select_rec – Given the entire primary key and only the primary key returns complete record

• insert_rec – Inserts a record

• update_rec –Given the entire primary key and only the primary key updates a record

• delete_rec - Given the entire primary key and only the primary key deletes a record.

Example: SP_TR_ELAPSED_TIME is a stand-alone procedure that calculates and processes the elapsed time between two events.

Schemas

• A schema is a set of objects owned by a user and as such no additional naming convention is required (see Users).

• Where a set of objects is owned by an application, a ‘Three Schema Security Model’ for database security should be used as follows:

Level 1 Schema – Owns the application data and grants only the bare minimum of needed privileges to a second schema.

Level 2 Schema – Uses privileges granted by the first schema to create the objects needed to access the application data such as Views and Packages.

Level 3 Schema – Uses privileges granted and objects created by the second schema to access the data. Used for application users to connect to the database.

• Application users should never be allowed to connect to the database via a ‘single’ schema that owns all data and objects.

• Application specific schemas should be prefixed with the application short name. (See Applications)

Example: LOC_DATA is a schema that owns the data (tables) for the LOCATOR application (equivalent to Level 1 Schema).

Synonyms

• Synonyms are created as aliases for tables, views, sequences, procedures, functions, packages, materialized views, Java class schema objects, or other synonyms. Synonyms are used for security and convenience to:

• Mask the name and owner of an object

• Provide location transparency for remote objects of a distributed database

• Simplify SQL statements for database users

• The naming convention for synonyms is:

<synonym name>

Where <synonym name> exactly matches the name of the underlying object that the synonym is referencing. An exception to this is where the underlying object has been renamed in which case the synonym needs to be redefined and applications based on the synonym will continue to function without modification.

• Synonyms should have a minimum of 4 characters.

Example: VESSELS is a synonym for the table VESSELS

 

Tablespaces

• A tablespace name should reflect the contents of the tablespace. During a standard Oracle installation, special tablespaces are created and named as follows:

   

   

   

  TABLESPACE	CONTENTS
  TEMP	Sorting, grouping and indexing data
  RBS	Rollback data
  INDX	Indexes associated with data in USER tablespace
  USERS	Miscellaneous user objects and data
  SYSTEM	Data dictionary, including definitions of tables, views and stored procedures
  UNDOTBS01	Undo data

• Tablespace should be names according to function.

• The tablespace name should also indicate whether it contains table data or index data.

• Each file in a tablespace should have a sequential number that relates to when it was created (1 for the first file, 2 for the next and so forth).

• Tablespaces should be named connotatively with 8 or fewer characters.

• The word tablespace (or reminders of) should not be embedded in tablespace names e.g. TSPACE_A

• Partitioned tables should be stored in tablespace that are named for the partition they contain.

• The naming convention for tablespaces is as follows:

<object type> or <application name><object_type><n>

Where <object type> is the contents of the tablespace. This is applicable for TEMP, SYSTEM, USERS, UNDOTBS. <application name> is the short name of the application, <object type> is D (for data) or X (for index). <n> is optional and refers to the partition number. This is significant for partitioned tablespaces.

Example 1: SYSTEM

TEMP

Triggers

• Database trigger is a stored subprogram associated with a table. It automatically forces before or after an INSERT, UPDATE or DELETE statement affecting the table.

• A trigger is a stored PL/SQL block associated with a table, a schema, or database

• The naming convention for triggers is:

T<when><type>_<table name>

Where:

<table name> is the name of the table on which the trigger is based

<when> refers to whether the trigger is executed Before or After an insert, update, or delete. The value of <when> is ‘A’ for after and ‘B’ for before triggers.

<type> is the action (Insert, Update or Delete) performed by the trigger. The value of <type> is ‘I’ for insert, ‘U’ for update and ‘D’ for delete.

• Trigger names should have a minimum of 10 characters.

Examples: TBI_CLIENT_MSTR = A before insert trigger on client_mstr table.

TBIUD_IMPLEMENTATION_DTL = A before insert, update and delete trigger on implementation_dtl table.

 

Procedures

1. To ensure that the database follow the design standards from the initial stages of the Application Development cycle the following will be the responsibility of the Project Team Leader :

• Determine business rules that have to be defined in the database server.

• Create database objects for systems still under the development environment.

• Create scripts that will be used for the creation of database objects such as tables, indexes, stored procedures, triggers. These scripts will be used when the system under development will be transferred to production.

• Determine the approximate volume of production data on every table used by the system.

• Maintain (Grant / Revoke) the object access rights of the different users of their system in the development server.

• Coordinate with the DBA regarding the enrollment of users (developers) of the system. This should be done by filling up a “user enrollment form”.

1. Likewise, to ensure that database design standards are met in the Development and Production servers the following will be the responsibility of the Database Administrator

• Monitor, maintain and administer the Development and Production servers.

• Create the database objects in the production server from scripts.

• Create a separate tablespace in both the Development and Production servers for each system.

• Create login accounts for the different users per system

• Maintain (Grant / Revoke) the access rights of the different users of each system in the development and production servers.

• Establish User Profiles per System

• For standard access rights and easier maintenance of user rights, user profiles should be established.

• Establish a standard login program in granting/revoking access rights of users of the different systems.

 

 

1. Diagramming Guidelines

• Establish standard tool for the company (Jdev/ERWin) and make sure that designers/developers are using the same tool to allow exchange of information

• Diagrams should be exported to graphical outputs like JPEG/GIF especially if there is a need to allow others to view the diagrams at the same time restricting them to edit it.

• Digrams should be versioned and placed in a repository

• Create subject matters and place related tables inside when doing datamodels