Best Practices for Oracle Database Naming Conventions to Understand Easily

What's in a name? That which we call a rose by any other name would smell as sweet.

--William Shakespeare

A naming convention is a convention for naming things. In database or Programming, it is a convention for naming attributes or objects. Naming standards are some rules or guidelines which help to work as a team faster. It most probably won't affect database performance although they will surely affect performance and level of understanding of designers, developers and support staff. With a consistent naming scheme they will understand data models better and produce SQL statements faster. And this will directly affect your project costs.

There are some naming standards described below for Oracle Database Design. They could probably be used not only in Oracle but in other RDBMS's as well (for example MySQL, PostgreSQL, SQLite) but you should check whether they haven't other widespread standards.

Why it is important 

It is very important to follow some naming standards and guidelines in the process of designing Oracle databases. It is even more important if you have a task to design an Oracle database containing hundreds of tables and thousands of other objects. If you have a project command of several people it is absolutely necessary to pick a naming convention, write it down and USE it. 

Human understanding:

Maintain standards so that everybody is in the same alignment and understanding easily.

Framework and Application understanding:

Maximum framework or development technology has CRUD operation which speeds up development. A well-defined naming scheme becomes more important when you adopt object relational mapping (ORM) technologies or automatic code generation. Naming convention is very important in this operation because all labels on the page automatically convert into human language if naming is standard. 

Common rules

1. Never use names with a leading numeric character. The first character in the name must be a letter. 
2. Only letters, numbers, and the underscore are allowed in names. Although Oracle allows $ and #, they are not necessary and may cause unexpected problems.
3. Always choose meaningful and specific names. but in the same time don't use long_names_describing_every_single_detail_of_particular_object.
4. Avoid using abbreviations unless the full name is excessively long.
5. Avoid long abbreviations. Abbreviations should be shorter than 5 characters.
6. Any abbreviations must be widely known and accepted.
7. Create a glossary with all accepted abbreviations.
8. Never use ORACLE keywords as names. A list of ORACLEs keywords may be found in the dictionary view V$RESERVED_WORDS.
9. Avoid adding redundant or meaningless prefixes and suffixes to identifiers. Example: CREATE TABLE emp_table.
10. Always use one spoken language (e.g. English, German, French) for all objects in your application.
11. Always use the same names for elements with the same meaning.
12. All names are in UPPERCASE. Or at least of no importance which case. Ignoring this rule usually leads to tables and columns very clumsy because all names must be included in double quotes.

Tables

1. Table names are in plural form, for example, persons, materials, addresses. 
2. If table name contains more than one word, they are separated with underscore in form {name1}_{name2}. Only the last one is in plural, for example person_addresses.
3. Table names should not contain spaces, words should be split_up_with_underscores.
4. The table name is limited to 30 bytes which should equal a 30 character name (try a DESC ALL_TABLES and note the size of the Table_Name column)
5. All tables have 3 or 4 character long aliases that are unique in a schema. Aliases are not directly used in the name of the table, but they are used to create column names. For example, persons - prs, materials - mat, addresses - adr.
6. (Optional) pros and cons to adding a prefix or suffix to identify tables: 
7. Sometimes it is useful to distinguish some logical parts of an application. Therefore prefixes are used in table names. For example, sec_users, sec_roles, sec_rights all are related to security subsystems.
8. If most access will be made via VIEWS then prefixing all the tables with T_ and all the views with V_ keeps things organised neatly, you will never accidentally query the wrong one.
9. Cons: Suppose, your naming convention is to have the '_TAB' suffix for all tables. According to that naming convention, the APPLICATIONS table would be called APPLICATIONS_TAB. If as time goes by, your application gets a second login, perhaps for auditing, or for security reasons. To avoid code changes, you will then have to create a View or Synonym that points at the original tables and is confusingly called APPLICATIONS_TAB.

Field Names

1. All tables have surrogate primary key column in form {alias}_id, which is the first column in the table. For example, for people table: ppl_id, for customers table: cust_id.
2. All columns are in singular. If you think you need a column name in plural think twice whether it is the right design? Usually it means you are including multiple values in the same column and that should be avoided.
3. All foreign key columns are in form {parent_table_singular_name}_id. For example, person_id if parent table is people table, customer_id if parent table is customers. 
4. If there is more than one foreign key column to another table then foreign key columns are named {alias1}_{alias2}_{meaningful_name}_id. For example, prs_adr_curr_id, prs_adr_prev_id.
5. If several tables contain columns with the same content use the same consistent column names. For example if you need some auditing info, then use {alias}_last_chg_time and {alias}_last_chg_user for all tables. Of course, you can choose your own column names but use them consistently. It is also relevant for some flag columns, for example {alias}_is_active and also columns containing describing info - use either notes, description, comments or whatever but only one of them for the same purpose. All consistencies help to understand the data model, all inconsistencies - prevent.