Naming conventions
in Oracle databases

Naming conventions are an important part of coding conventions. Naming conventions are the rules for how “things” are named. In the case of a database, the “things” being named are schema objects such as tables, sequences, indexes, views, triggers as well as constraints. In a database it is essential to choose names with particular care.

If one thinks of a database with several data providers and consumers (usually including applications and interfaces to and from other databases), it is easy to imagine that objects cannot be easily be renamed. They should be given suitable names from the very beginning.

Compared to the naming of classes in object-oriented programming languages, the database developer also has to struggle with the restricted hierarchy of namespaces. While Oracle is only familiar with the schema for all object types and also the packages for PL/SQL code, in Java a freely-definable hierarchy of Java packages can be generated to which classes can be added.

The length of an identifier may also be restricted. Fortunately, in Oracle 12cR2, the 30-byte restriction for an identifier has been increased to 128 bytes.

Choice of suitable names

The domain knowledge should always be taken into account when choosing names. As a rule, the domain knowledge is reflected in the name of the relation (table name).
Not least due of the length restriction for identifiers, it is recommended to introduce a human readable name abbreviation (i.e. a mnemonic) for each table.
Triggers, indexes, sequences and constraints are, for example, assigned to tables. The way in which they are named should follow certain rules. The affiliation of such an object to a table should be obvious; as well as the object type and ultimately the technical execution.

Example:
All employees are included in the employee table (domain knowledge).
The employee table is given the abbreviation emp.
There is a sequence named emp_seq.
There is a trigger named emp_seq_tg.

Using our naming convention, we now know the following:

  • The emp_seq sequence and the emp_seq_tg trigger belong to the employee table, since they bear the abbreviation emp.
  • Because the sequence bears a name in accordance with our convention:
    • The employee.id technical key column is populated by the sequence.
    • The sequence starts with 1 and ends with 1018-1, it as well as employee.id, will fit in a 64-bit signed integer.
    • The sequence does not repeat.
  • Since the trigger also bears a name in accordance with our convention, we know that
    • This trigger populates the employee.id key column.
    • The trigger does nothing else. (Which is good!)

There is already a lot of implicit knowledge being conveyed by the naming conventions. We have also incorporated a few “best practices” into our naming conventions such as largely eliminating triggers and the data types for sequences and id columns.

Naming conventions must be alive

We have decided to publish our naming conventions under an open source license and want to discuss them publicly. They are available here:
http://github.com/triologygmbh/database-conventions/
The conventions described in this article are available there and include additional conventions in detail.

Share this article

Jan Niemann
Software Development
As a software developer Jan mainly works with UNIX and databases. He also advises clients on finding efficient solutions.