December 23, 2013 at 22:22

Naming objects in Oracle. View from the outside"

“Old song about the main thing”


“Standards for naming database objects” and “code design rules” are not new topics. One way or another, all the development teams come to the issue of developing or borrowing such standards and rules. If you wish, you can find articles and presentations on this topic in the network, as well as examples and templates of various agreements. Many of them are certainly useful, some are almost perfect, if not for one small caveat: they are written by developers and for developers.

Unfortunately, in my subjective reality, developers are just a kind of abstraction. Such phantoms on the other side of the handset, from which thousands of kilometers and 3 time zones separate me. I have no direct access to their collective brain. Only the images generated by this brain are available - in the form of objects of an exploited system.

In principle, the desires for registration and naming of the “application” (application administrator / technologist) and the developer coincide 90 percent. But there are still some differences in the perception of the “reader” and “writer”, which I would like to talk about.

The purpose of this article: to develop a set of rules for naming database objects (I like the term Naming Conventions - NC , similar to Code Conventions ) for use by a team of software developers in the design of information systems based on Oracle DBMS, satisfying the following requirements:

  1. NC should be as full as possible, i.e. contain naming conventions for all types of database objects
  2. NC should be as short as possible. Ideal option: 1 A4 sheet.

Why Oracle Database? She is closer and dearer to me. And attempts to embrace the immensity with claims to universality are beyond my teeth and competencies. In this topic, I tried to summarize the materials of articles by Bill Coulam, Steven Feuerstein and Tom Kyte on this topic and my modest experience in the design, development and operation of various information systems.

Those who are too lazy to read about different approaches to naming and wade through arguments in defense of this or that approach can simply scroll through the article to the end, where I gave a link to my own "Oracle NC" poster. There you can find other useful links on the topic.

Are you here? Could you hold back and didn't start scrolling? Congratulations, you get a special bonus: you can download"Oracle NC" is a poster right here, without leaving this place. The fact is that I have already read my article (yes) and I warn you in advance: it is cumbersome and abounds with inset templates that are obscure at first glance. It will be much easier to perceive it, having at hand all the rules and examples on one sheet.

Everyone knows that the main thing in the tank, but not everyone can hold back ...


It would seem that the idea of ​​developing NC for an IT project lies on the surface. Compliance with the requirements of NC in the design and development is also not a godsend. However, my experience with solutions from leading Russian developers in the billing system market for telecommunications companies suggests that the culture of naming objects and designing source code is actually very low. In principle, all the solutions that I have been examined in my duty can be divided into four groups:

  1. Complete absence of signs of NC (2 products out of 12). A nightmare. Operating such a system is the same as assembling the puzzle face down. At the cost of tremendous effort, only individual threads of interconnections and logic can be pulled out of the ball. And each time, with a new problem, the ball has to be unraveled again.
  2. Using different NCs in one project (3 products out of 12). It’s immediately obvious that the developers heard about NC, developed their own style and learned to follow it. The problem is that there were too many developers and styles for one project. The operation of such solutions is hampered by the fact that the experience of studying a single module is useless in another. Knowing the part you can not get a reliable idea of ​​the whole.
  3. NC, which were clearly visible in the early versions, were buried under a pile of crutches and washed with a powerful pressure patch (6 products out of 12). The most common option. The same tank in which they could not restrain themselves.
  4. Clearly fixed NC in compliance with all requirements and restrictions throughout the life cycle of the system. (1 product out of 12). Here she is the dream of an applied man ...

I do not want to delve into the analysis of the reasons for p.p. 1-3, I am simply stating a fact. All I want is to help take a step towards the “dream” of paragraph 4. So, let's begin.

General recommendations


Let's start with what should be remembered and what to follow when developing for Oracle DBMS:

  1. Remember that object names in Oracle are limited to 30 characters. Exhaustively. From myself I can add only a wish. If you do not want people working with your system through applications that do not support the "code hint" to go crazy, be prudent - try to keep the names of your objects as short as possible.
  2. Remember that object names cannot begin with a digit. No comments.
  3. Use the same language when naming objects. English, preferably. Avoid transliteration. Believe me, a table called ORDERS is better perceived than a table called ZAKAZ . And further. Very often in commercial systems you have to deal with transliteration of abbreviations. Avoid her too. USSR is clearer than SSSR , and USA is clearer than SSHA Somehow.
  4. Yes, I almost forgot. Use in Latin names only! Of course, this is a recommendation for idiots, but I almost went crazy once, trying to fetch from a table in SQLPLUS. And all because at the very end there was a symbol “e” in the Russian layout. In PL / SQL Developer, I did not have to type the name completely - the code hint worked. The funny thing is that the table has lived in this form for more than a month and no one has complained about it before me.
  5. In the process of designing your system (immediately after examining the subject area), rewrite all the identified entities in a separate file (table - glossary). Do not be too lazy to rummage around on the Internet - for most of your entities, there are a long time ago universally accepted and established English-language names. Fix them in the glossary. For other entities, make a good translation. Do the same for prospective abbreviations. Well, and then the most important thing: always use the same names for the same meaning elements.
  6. Avoid using Oracle reserved words as names (a list of all reserved words can be obtained by selecting from the system view V $ RESERVED_WORDS ). By the way, some words from the given representation of Oracle and will not allow to use. But there are those that are not expressly prohibited to use, but it’s better, after all, not to.
  7. Separate tokens in object names with an underscore. Remember that Oracle is not case sensitive, so your “camels” like MySuperPuperTableName will turn into completely unreadable MYSUPERPUPERTABLENAME in the dictionary .

    A slight digression
    Honestly, in Oracle you can specify a case-sensitive name for an object. For example, like this:

    create table "MyTable" (a number);

    In short, avoid such perversions.


Object Naming Rules


Tables

On the question of naming tables, I almost completely share the point of view of Bill Coleham. The standard he developed is comprehensive and practically ideal, both for the developer and for the “exploiter”. I will not give a full translation here, I will focus only on the fundamental points.

So, Coleham offers the following universal form of naming a table (curly braces include mandatory components, and “straight” brackets include optional ones):
[Модуль_][Группа_]{Наименование}[_Роль]

Under the Module (in terms of Colema - system group ) is meant the name of the subsystem within our database. Usually a 2-4 character reduction is used. For example, the tables of the “Tariff” module may have the prefix “TAR_”, and the tables of the Payment module the prefix “PAY_”. I’ll add on my own that if development is carried out in a “foreign” scheme, it is advisable to add an additional prefix to separate one’s and another’s objects. Usually I add the abbreviation of my organization to the prefix. Of course, this lengthens the names of objects, but it allows you to clearly select “your” objects in the project tree. If you are embarrassed by this approach, it is enough to add a single character before the module code ("local developers" usually prefer X or XX inheritance OEBS?!).

Groupused for the same purposes: it allows you to group entities that are logically connected (usually up to 20 objects in a group). It is also a reduction of 2-4 characters. Using system and logical groups allows not only grouping entities in the tree of objects, but also significantly simplifies the development and maintenance of the system as a whole. Indeed, the need to remember the names of specific objects disappears, just remember the abbreviations of the module and logical group, and then the code hint will help you easily find the object you need.

With Nameall clear. This is the actual name of the entity. Bill Coleham recommends the use of the singular, but personally I am closer and more familiar with the plural (Stephen Furstayn, hello!). Both Stephen and Bill advise avoiding abbreviations in entity names. Exceptions are words longer than 8 characters.

It is not always possible to express the purpose of the table in one word. In this case, some domestic developers out of habit use the rule, which I call myself the “Sales receipt rule”, when the word order goes from general to particular, from essence to properties. Those. “Toilet paper”, instead of “Toilet paper”, “Pickled cucumbers”, and “Tomato paste”. Unfortunately, in English names this most often looks horrible. Compare YELLOW_SUBMARINE and SUBMARINE_YELLOW. In this case, I see no reason to rely on a single template, but I recommend using the order that is more appropriate in a particular context.

A role is essentially the purpose (type of destination) of a table in the system. Coleham singles out about two dozen roles, but for my taste some of them are redundant. I will give only those that I use:
  • _AUD - Tables that store the history of changes in the rows of the base table (journal tables). I met the suffixes _JN (log) and _HIST in different systems for this type of table . Itself used the suffix _AUDIT , now _JN . And _AUD is more familiar to me in the name of the trigger.
  • _LOG - application message commit tables (log tables)
  • _HIST - Archive tables into which outdated data are uploaded . For this type of table, I use the suffix _ARCH
  • _TYPE - reference tables, the rows of which are a list of unique values. Domestic developers often use the _REF suffix for directory tables . I also like the _REF suffix because the word _TYPE is very popular among developers and is often part of the Name component .
  • _GT - Global temporary tables. Stephen Furstine recommends using the suffix _TMP or _TEMP for them , but this, in my opinion, contradicts our mentality. The Russian programmer is used to using the _TEMP token to mark any temporary “garbage”, so _GT and only _GT .


In a separate class, Coleham identifies tables through which the many-to-many relationship is implemented. For such tables, he offers the following naming pattern:
[Модуль_]{Имя/Код таблицы 1_Имя/Код таблицы 2}

In most of the projects I came across, developers used “meaningful” names for such association tables. I myself use the template:
[Модуль_][Группа_]{Код таблицы 1_}{Код таблицы 2}

The table code in this case is the abbreviation of the table that participates in the link (2-4 characters). For example, a table storing student connections ( STUDENTS ) attending teacher lectures ( TEACHERS ) in this standard will be called STUD_TCHR . Yes, at first glance it looks repulsive, but over time you understand the convenience: at first glance you classify the table as a “bunch” (due to the use of codes / abbreviations instead of full names), you immediately see which entities are connected.

Columns (columns) of tables

Let's start with the restrictions on the total length of the name - try to keep within 15 characters (better - less). You will need a margin to the upper limit for the subsequent naming of constraints, indexes, and columns with a foreign key.
In my projects, I use the following template:
[Код таблицы_]{Наименование столбца}[_Роль]

The table code is the abbreviation of the table to which the column belongs (2-4 characters). Although I have designated this prefix optional, I use it for almost all columns. An exception is “service” columns that store the value of certain properties of the abstract record of any table, and not the properties of a particular object (for example, UPDATE_DATE , UPDATE_BY , etc.).

The name of the column speaks for itself. Separately, I want to say only about the rule the formation of the name for the foreign key - it consists of the code of the child table plus the full name of the parent primary key.

Role- optional suffix. Please note that this is a column value type, not a data type code for this column! Most often I use the following roles (value types):

  • _ID - object identifier based on a surrogate key
  • _NUM - a string field containing some number.
  • _CODE - string key of the entity (unique for reference tables).
  • _DESC - Summary of Entity
  • _YN - field of type CHAR (1), taking values ​​Y (es) or N (o)


Many consider the template (and, specifically, the prefix in the form of table code) redundant. However, being able to compare different approaches, I chose it for myself. I will give my reasons:
  1. More readable queries. By a column with a prefix, you immediately understand which table we are talking about. It's no secret that often developers are too lazy to qualify columns, so the name of a column with a prefix makes working with "foreign" queries easier.
  2. Diagnostics of exceptions (errors) is facilitated. Of course, most of them refer to constraints, not specific columns, but the name of the constraint is almost always based on the column name.
  3. The probability of a column name coinciding with reserved words from the system dictionary is reduced. This is especially true for such common names as NAME, ID, COMMENT and DATE. As a result, the developer is freed from the need to add other redundant character combinations to the name.
  4. In our company, it turned out that most of the client software used was developed based on Oracle Forms, where for any field, using the F1 button, you can see the name of the source column. The ability to instantly associate an object on a form with an object in the database helps a lot both with the initial acquaintance with the system and with further operation.


Limitations

Coleham recommends naming constraints using a prefix in the form of the full name of the table to which this restriction applies. I consider this naming unreasonable waste, especially given the general limit of 30 characters per name length. Therefore, I try, where possible, to use the code of the table instead of the full name. Thus, for the primary key we get:
[Модуль_][Группа_]{Код таблицы}{_PK}

Hereinafter, the module and Group restrictions prefixes are inherited from the table with which they are associated. This allows you to avoid violations of uniqueness in the formation of names in large systems, as well as convenient to group restrictions on the modules.

For a unique key built on one column:
[Модуль_][Группа_]{Шаблон столбца}{_UK}

I remind you that the column template here includes the table code . Thus, for the PRM_CODE column of the UTL_PARAMS_REF table , the unique key will be called UTL_PRM_CODE_UK

For a unique key built on several columns:
[Модуль_][Группа_]{Код таблицы_}{CОMP_UK}[_#]

COMP - in this case means COMPOSITE (composite key sign), # (serial number) is used if there are several unique composite keys (to be honest, I can’t come up with a sane example for this case).

Foreign key based on one column:
[Модуль_][Группа_]{Шаблон столбца}{_FK}

Since the full name of the foreign key column we contains codes subsidiary and the parent table , then the column PVL_PRM_ID table UTL_PARAM_VALUES foreign key will be called UTL_PVL_PRM_ID_FK (references a column PRM_ID table UTL_PARAMS_REF )

foreign key, built on several columns:
[Модуль_][Группа_]{Код таблицы_}{COMP_FK}[_#]

Column Level Constraint:
[Модуль_][Группа_]{Шаблон столбца}{_CK}

Table level limitation:
[Модуль_][Группа_]{Код таблицы_}{COMP_CK}[_#]

On the Internet, I often met heated discussions about the need to name constraints of type NOT NULL . Yes, I agree, lazily, but if you strictly adhere to the concept, then:
[Модуль_][Группа_]{Шаблон столбца}{_NN}

Indices

I usually divide indexes into three categories:
  1. Key-based indexes (primary and unique)
  2. Single Column Indexes
  3. Compound (based on multiple columns)

Key-based indexes (primary and unique) are named the same way as their corresponding restrictions:
[Модуль_][Группа_]{Код таблицы}{_PK}
[Модуль_][Группа_]{Шаблон столбца}{_UK}
[Модуль_][Группа_]{Код таблицы_}{CОMP_UK}[_#]

Indexes based on one column:
[Модуль_][Группа_]{Шаблон столбца}[_Роль]{_IDX}

Under The role in this template refers to a type modifier index. Colem recommends using the following modifiers:
  • L - local partitioned index
  • G - Global index on the partitioned table
  • B - Bitmap Index
  • F - Function Based Index
  • C - compressed index (compressed)
  • R - reverse key index (index with the reverse order of keys)
  • U - unique index

Indexes based on multiple columns. Coleham recommends the following form:
[Модуль_][Группа_]{Код таблицы}{_COMP}[_Роль]{_IDX}[#]

I consider the Colem pattern a special case and always try to list all the columns (if this does not violate the length limit) in the index name:
[Модуль_][Группа_]{Код таблицы_}{Список столбцов}[_Роль]{_IDX}

Why am I limited to the COMP modifier for restrictions, but try not to use it for indexes? The thing is that composite constraints are nevertheless the exception rather than the rule. There are usually not very many of them, and a message with an error about their violation is not very common. Compound indices are another matter. Firstly, there are just a lot of them. Secondly, there are often more than one per table. And thirdly, the developer and administrator of the application works with them constantly, checking query plans.

Triggers

In this article, I only consider DML triggers, because I believe that all other types are more in the responsibility of the DBA, not the developer. I call triggers by the following rules:
[Модуль_][Группа_]{Код таблицы}[_Цель/Роль]_{B|A|C (I|U|D)[S]}[_#]

Where the abbreviations B , A , C ( BEFORE , AFTER , COMPOUND ), determine the "moment" of the trigger; I , U , D ( INSERT , UPDATE , DELETE ) - response event; S ( STATEMENT ) - determine the "level" of operation.

In my projects, I distinguish two "typed" Triggers (roles):
  • AUDIT - triggers that commit changes to the main table in the journal (for example, UTL_PRM_AUDIT_AIUD )
  • INIT - “initializing” triggers responsible for generating surrogate keys and filling default values ​​for columns (for example, UTL_PRM_INIT_BI )


Representation

The rules for naming views are no different from the rules for naming tables. The only wish is to include in the name a sign that this object is just a representation. The approaches here may be different. I met this symptom in the form of a name prefix. For example, V_ or even V $ , like Oracle system views. Personally, I use suffixes:
  • $ V - for regular views
  • $ MV - for materialized

But I will not advise you. The dollar sign as a separator is a matter of habit. This is an “anchor” for my eyes, which allows me to distinguish a table from a view. I can’t take an objective look at these approaches; therefore, I have nothing against the “underscore” sign, like in Ferstayn (suffixes _V and _MW ).

Sequences

I distinguish sequences from other objects by the suffix _SEQ and recommend naming them by the following rule:
[Модуль_][Группа_]{Код таблицы | Полное наименование столбца | Цель}{_SEQ}

The table code (abbreviated name of the table is 2-4 characters) is used for sequences that serve to generate a surrogate primary key of the table.

The name of the column (and here, I recall, includes the table code) is used to generate the value of the column that is not included in the primary key. In fact, this is a degenerate case that I really do not use. If the sequence is not used to generate primary key values ​​- in the name I try to reflect the purpose of this sequence.

For example, I will call the sequence for generating the primary key of the INTERNET_LOGINS table ILG_SEQ , and the sequence for generating the login for a specific Internet account isLOGIN_SEQ .

Synonyms

Synonyms are referred to in the same way as the objects to which they refer.

Types

By type, I do not have a definitively formed opinion. I met different approaches to naming these objects, but I couldn’t fully decide which approach is closer to me. I will describe here those that do not cause negative reactions in me:
 [Модуль_][Группа_]{Наименование}[_Признак коллекции]T

This template is recommended by Coleam. Sign collection type oboznalsya symbol T . Thus, the type of a single object always has the suffix _T , the collection type is _TT . For example, UTL_PARAMETER_T , UTL_PARAMETER_TT .
[Модуль_][Группа_]{Наименование}[S]_TYP

Here S denotes the plural, and the suffix TYP qualifies the database object as a type. For example, UTL_PARAMETER_TYP , UTL_PARAMETERS_TYP . I like this approach least of all, because the sign of the collection is not highlighted and the eye does not cling to it.
[Модуль_][Группа_]{Наименование}_{OBJ | TAB}

In this notation, if the name of the database object ends in OBJ or TAB , then the object is a type ( TAB is a collection, OBJ is a single object). For example, UTL_PARAMETER_OBJ , UTL_PARAMETERS_TAB

Software modules

Rules for designing the code of program modules I would like to single out a separate article. Here's a template suggested by Coleam. For procedure packages, Bill uses the following rule:
[Модуль_][Группа_]{Цель/Назначение}[_Функцион. модификатор][_PKG]

In terms of NC Coleam, a Functional modifier (for me the term Subgroup is more understandable ) is used to separate some functions into a separate package for refactoring. Let's just say this is an additional level of the logical group. For example, the UTIL package contained functions for working with numbers and strings. It was split into two: UTIL_NUMBER and UTIL_STRING .

When developing in PL / SQL, a specialist constantly operates with functions and procedures of other packages. So that the code does not look bulky, I try to avoid unnecessary lengthening in the name of packages. Therefore, I use the suffix _PKG only in cases where the name of the package may coincide with the name of another object of the scheme.

For individual procedures and functions, Coleham recommends the following template:
[Действие_]{Цель/Назначение}

By action is meant the verb ( GET , SET , ASSIGN , RUN ), by goal is what needs to be done. For my part, I try not to use procedures and functions outside of packages at all during development. In addition, the same functions are often grouped by the objects with which they work, so I usually use the template
{Цель}[_Действие]

Thus, the code hint groups the procedures into objects: PARAM_GET , PARAM_SET , PARAM_CHECK , etc.

Conclusion


As promised, I quote a link to my own "Oracle NC" poster .
By no means do I impose my rules and standards on anyone and do not insist on their use. I just consider the presence of NC and the observance of its requirements in the team a good style - the "politeness" of the developer to who will work subsequently with the system.
Good luck with your projects.

PS The attentive reader certainly noticed my little deception. The first goal of the article was never achieved: not all types of Oracle DBMS objects are described in the article and are present in the NC-poster. Well, you have a chance to fix this defect. You can download the "source" of the NC poster and edit it to fit your goals and objectives.

The following source is used in the article:
  1. Steven Feuerstein's blog and its Naming Convention and Code Standards
  2. Oracle Naming Standard by Bill Coulam
  3. Tom Kyte's Blog Ask Tom ... SQL.RU
    Forum Content
Tags:

Also popular now: