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.

Wahl von passenden Namen

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:

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.

Comments

No Comments

Related Posts

Multilanguage database applications using Oracle APEX

Author
  • Swathi Mary Ambati
Published

Hello Everyone! If you are a DBA/PLSQL guru but are not familiar with traditional web development, then Oracle APEX is one of the best option. In this context, if you are trying to build a multilanguage application with Oracle APEX then this article should give an overview and understanding of how the translation mechanism that is built-in to Oracle APEX works, and how you should handle it. It is based on Oracle Application Express 18.1 version.

Go to on-premise conferences again!

Author
  • Maik Becker
Published

The year is coming to an end in a couple of hours. Today is New Year’s Eve and I am sitting here in my holidays at the Baltic Sea, reading and evaluating some abstracts for the APEX Connect 2023 in Berlin, while waiting for the "big" party.

A Groovy DSL for the Creation of Test Data using JPA

Author
  • Daniel Behrwind
Published

With the automated integrative testing of software that works with a complex JPA data model, it is invariably the case that sooner or later, one will face the question of how it is possible to create semantically meaningful test data without great cost. This article shows how Groovy can be used in order to define a Domain Specific Language (DSL) that enables test data to be defined so that it is easily readable, modular and separate from the actual test code.ieren.

Gauge – a lightweight behavior-driven testing tool

Author
  • Malte Kreutzfeldt
Published

In this article, I am going to report on the test framework Gauge from the ThoughtWorks corporation. This is a tool which I met for the first time when I was writing automated end-to-end tests (E2E) for various web applications within a web platform. The purpose of the tests is to progressively check whether certain functions such as single sign-out, appropriate redirection to other pages or access authorisations to certain elements of a selected web application function as they should.