DB Naming Conventions

1. Basic Naming Conventions

The rules for naming all database objects (including table, view, column and index names) will follow those basic naming rules imposed by Database. These rules are defined as follows:
• Use a maximum of thirty (30) characters
• Do not use quotation marks in names
• Comprise names using only letters, digits and the underscore (_)
• Use underscores (_) for visual clarity
• Begin names with a letter
• Do not duplicate an Database reserved word
• Do not duplicate the name of another database object
• Use nouns, rather than verbs
• Be descriptive, yet as short as possible
• Use standard abbreviations
• Be unambiguous

2. Object Naming Conventions Imposed by Database

Abbreviations should be used for any object name including PL/SQL program variables. Object names should be a maximum of thirty characters long with these exceptions:
• Names of databases are limited to eight (8) characters
• Names of database links can be as long as 128 characters
• Object names should not contain quotation marks
• Object names are not case-sensitive
• Names can only contain alphanumeric characters from the database character set and the characters _, $, and #. The use of $ and # is strongly discouraged. Names of database links can also contain periods (.) and at-signs (@)
• Object names should contain underscores (_) for visual clarity
• Object names must begin with a letter
• Object names must not duplicate a database reserved word
• **Synonym** Must not duplicate the name of another database object
• Object names should use nouns instead of verbs
• Object names should be as descriptive and as short as possible
• Object names should use standard abbreviations when required
• Object names should not be ambiguous

Object Type Naming Convention Example:
Sequences SEQ_
30 char max size. For table Customer, column customer_id sequence name would be SEQ_CUSTOMER_ID
Table Name Naming close to ORM mapping. Max 30 char.
Suitable name for easy understanding. CONSUMER_PROFILE (instead of saying just consumer define more about the nature of table for which its used. Consumer_profile will differ from consumer_address)
View Name V_
30 char max size. V_CONSUMER_PROFILE
V_CONSUMER_ADDRESS etc
Indexes PK/FK/NU/UK_
30 Char max size FK (foreign key), PK (primary key), NU (non unique key), UK (unique key)
A foreign key index on the Consumer_profile table: FK_consumer_profile
For a primary key index on consumer_profile table: PK_CONSUMER_PROFILE
Constraint names PK/FK_ A FK and PK Constraint on the TBL_SOURCE table will be FK__ and PK_ respectively.
Columns Each table can have a 4 char prefix and this prefix shall be attached to each column of the table. CONSUMER_PROFILE table can have the 4 char prefix as CONP. The column can be CONP_PROFILE_ID etc.
Schema Names 8 char to 10 char QDPTLDB. One schema for all portals.
Synonyms Same as the database object name for which synonym will be created. Consumer_profile for QDPTLDB.CONSUMER_PROFILE table.

3. Database Table Column Names

The column names must be unique, meaningful and readily understood by anyone working with the table in order to make the development and maintenance of these applications as easy as possible.
• Use descriptive names.
• Words should be separated by underscore.
• Be consistent in abbreviations and in the use of singular and plural forms.
Column Purpose Data Type Column Name Guidelines
To store date DATE XXX_…._TS
To store numeric number NUMBER XXX_…._NUM
To store character number VARCHAR XXX_…_V
To store character flag VARCHAR XXX_…._FLG
To store character codes VARCHAR XXX_…_CD
To store char description VARCHAR XXX_…_DESC
To store int INT XXX_…_INT
To store float FLOAT XXX_…_FLT
To store real REAL XXX_…_RL

N.B: Credits to my lead for sharing such wonderful information