Geekly Articles each Day
📜 ⬆️ ⬇️

Object naming 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. Anyway, all development teams come to the issue of developing or borrowing such standards and rules. If desired, the network can find articles and presentations on this topic, 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 some kind of abstraction. Such phantoms on the other side of the handset, from which thousands of kilometers and 3 time zones separate me. I do not have direct access to their collective brain. Only images generated by this brain are available - in the form of objects of the operated system.

In principle, the wishes for the design and naming of the "applied" (application administrator / technologist) and the developer are 90 percent identical. 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 is to develop a set of rules for naming database objects (I like the term Naming Conventions - NC , similar to Code Conventions ) for use by the software development team in designing information systems based on Oracle DBMS that meets the following requirements:
')
  1. NC must be as full as possible, i.e. contain rules for naming all types of database objects
  2. NC should be as brief as possible. Ideal: 1 sheet of A4.

Why Oracle DBMS? She is closer and more familiar to me. And attempts to embrace the immense with claims of universality are not in my teeth and competencies. In this topic, I tried to summarize the materials of the articles by Bill Coulam, Steven Feuerstein and Tom Kyte on this topic and my modest experience in designing, developing and operating various information systems.

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

Are you here? Could restrain and did not begin to scroll? Congratulations, you get a special bonus: you can download the “Oracle NC” poster right here without leaving this place. The fact is that I have already read my article (yes and yes) and I warn you in advance: it is cumbersome and replete with tamper-patterns incomprehensible 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 problem. However, my experience with solutions from leading Russian developers in the market of billing systems for telecommunication companies suggests that the culture of naming objects and source code design is extremely low in practice. In principle, all of the solutions that were studied by me, can be divided into four groups:

  1. Complete absence of signs of NC (2 products from 12). A nightmare. To operate such a system is the same as building a puzzle picture down. At the cost of incredible efforts, only individual threads of interconnections and logic can be pulled out of the coil. And each time, with a new problem, you have to unravel the tangle again.
  2. Using different NC in one project (3 products out of 12). It is immediately obvious that the developers have heard about NC, have developed their own style and learned to follow it. The problem is that there were too many developers and styles for one project. Operation of such solutions is hampered by the fact that the experience of studying a separate module is useless in another. Knowing the part can not get a reliable idea of ​​the whole.
  3. NC, which are clearly traced in earlier versions, are buried under a pile of crutches and washed with a powerful pressure of patches (6 products out of 12). The most common option. The same tank, which could not restrain.
  4. Clearly fixed by the NC in compliance with all requirements and restrictions throughout the entire life cycle of the system. (1 product out of 12). Here it is the dream of an apprentice ...

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

General recommendations


To begin with, what should be remembered and what should be followed when developing for Oracle DBMS:

  1. Remember that object names in Oracle are limited to 30 characters in length. 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 number. No comments.
  3. When naming objects, use the same language. Desirable English. Avoid transliteration. Believe me, the table with the name ORDERS is perceived better than the table with the name ZAKAZ . And further. Very often in commercial systems one has 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 only Latin in the names! Of course, this is a recommendation for idiots, but I almost went crazy once, trying to select a table from SQLPLUS. And all because at the very end there was heaped character "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 to it before me.
  5. In the process of designing your system (immediately after the examination of the subject area), rewrite all identified entities into a separate file (table - glossary). Do not be lazy to rummage on the Internet - for the majority of your entities for a long time there are generally accepted and well-established English-language names. Fix them in the glossary. For other entities, make a good translation. Do the same for the intended abbreviations. Well, and then the most important thing: always use the same name for the same meaning elements.
  6. Avoid using Oracle reserved words as names (a list of all reserved words can be obtained by sampling from the V $ RESERVED_WORDS system view). By the way, some words from this Oracle representation will not allow to use. But there are those that are not directly prohibited to use, but it is better, nevertheless, not to do it.
  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.

    Small lyrical digression
    Frankly, 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

In the matter of naming tables, I almost completely share the point of view of Bill Colem. The standard he developed is exhaustive and almost perfect, both for the developer and for the “exploiter”. I will not give a complete translation here, I’ll dwell only on the fundamental points.

So, Colem offers the following universal form of naming the table (curly brackets include the required components, and "straight" brackets are optional):
 [_][_]{}[_]

By Module (in terms of Colema, the system group ) is meant the name of the subsystem within our database. A typical abbreviation of 2–4 characters is used. For example, the tables of the module "Tariff" may have the prefix "TAR_", and the tables of the Payment module prefix "PAY_". From myself I would add that if development is carried out in a “foreign” scheme, it is desirable to add an additional prefix to separate one’s own and those of others. I usually add the abbreviated name of my organization to the prefix. Of course, this lengthens the names of the objects, but allows you to clearly select "your" objects in the project tree. If you are embarrassed by this approach, it is enough to add one character in front of the module code (“local developers” usually prefer X or XX-legacy OEBS?!).

A group is used for the same purpose: it allows you to group entities that are logically related to each other (usually up to 20 objects in a group). It is also an abbreviation of 2-4 characters. The use of system and logical groups allows not only to group entities in the object tree, but also significantly simplifies the development and maintenance of the system as a whole. Indeed, the need to memorize the names of specific objects disappears, it is enough to remember the abbreviations of the module and the logical group, and then the code prompt will help you easily find the object you need.

With the name everything is clear. This is the actual name of the entity. Bill Colem recommends using a single number, but personally the plural is closer and more familiar to me (Stephen First, hello!). Both Stephen and Bill advise avoiding abbreviations in entity naming. Exceptions - words longer than 8 characters.

Not always the purpose of the table can be expressed in one word. In this case, some domestic developers, out of habit, use the rule that I myself call the “Sales receipt rule” when the word order goes from the general to the particular, from essence to properties. Those. “Toilet paper”, instead of “Toilet paper”, “Pickled cucumbers”, and “Tomato paste”. Unfortunately, in English-language names it most often looks awful. 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.

Role is essentially the purpose (type of assignment) of a table in the system. Colem allocates about two dozen roles, but for my taste, some of them are redundant. I will cite only those that I use:


In a separate class, Colem distinguishes the tables through which the many-to-many relationship is realized. For such tables, he suggests the following naming pattern:
 [_]{/  1_/  2}

In most of the projects I came across, for such tables-associations, developers used “meaningful” names. I myself use the template:
 [_][_]{  1_}{  2}

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

Columns (columns) of tables

Let's start with the limitations of the total length of the name - try to keep within 15 characters (better - less). Stock up to the upper limit you will need for the subsequent naming of constraints, indexes and columns with foreign key.
In my projects I use the following template:
 [ _]{ }[_]

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

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

Role is an optional suffix. Note that this is the type of the column value, not the data type code of this column! Most often I use the following roles (value types):



Many consider the pattern (and, specifically, the prefix in the form of a table code) redundant. However, being able to compare different approaches, I chose him for myself. I will give my reasons:
  1. More readable queries. By the column with the prefix you immediately understand which table is being referred to. It’s no secret that developers are often 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, rather than specific columns, but the name of the constraint is almost always based on the name of the column.
  3. The probability of coincidence of the column name with the reserved words from the system dictionary is reduced. This is especially true of such common names as NAME, ID, COMMENT and DATE. As a result, the developer is freed from the need to add other redundant combinations of characters to the name.
  4. In our company, it turned out that most of the client software used is based on Oracle Forms, where for any field on 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 with the initial acquaintance with the system and during further operation.


Restrictions

Colem recommends naming constraints using the prefix in the form of the full name of the table to which this restriction applies. I consider such a name to be unreasonable waste, especially considering the total limit of 30 characters for the length of the name. Therefore I try, where it is possible to use the Table code instead of the full name. Thus, for the primary key we get:
 [_][_]{ }{_PK}

Hereinafter, the module and group prefixes of the constraints are inherited from the table with which they are associated. This allows you to avoid violations of the 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 template column we include 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:
 [_][_]{ _}{CMP_UK}[_#]

COMP - in this case means COMPOSITE (a sign of a composite key), # (sequence number) is used if there are several unique composite keys (honestly, I cannot think of a sane example for such a case).

Foreign key based on one column:
 [_][_]{ }{_FK}

Since the full name of the foreign key column contains the codes of the child and parent tables , then for the PVL_PRM_ID column of the UTL_PARAM_VALUES table the foreign key will be called UTL_PVL_PRM_ID_FK (refers to the PRM_ID column of the UTL_PARAMS_REF table)

Foreign key built on multiple columns:
 [_][_]{ _}{COMP_FK}[_#]

Column level constraint:
 [_][_]{ }{_CK}

Table level constraint:
 [_][_]{ _}{COMP_CK}[_#]

On the Internet, I have often met heated discussions about the need for a NOT NULL constraint naming convention. Yes, I agree, lazily, but if you strictly adhere to the concept, then:
 [_][_]{ }{_NN}

Indices

Indices I usually divide into three categories:
  1. Key-based indexes (primary and unique)
  2. Single column indices
  3. Compound (based on several columns)

Keys-based indices (primary and unique) are named the same as the corresponding restrictions:
 [_][_]{ }{_PK} [_][_]{ }{_UK} [_][_]{ _}{CMP_UK}[_#]

Single column indices:
 [_][_]{ }[_]{_IDX}

The role in this template is an index type modifier. Colem recommends using the following modifiers:

Multiple column indices. Colem recommends the following form:
 [_][_]{ }{_COMP}[_]{_IDX}[#]

I consider the Colem template as a special case and I 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 constraints, but I try not to use it for indexes? The fact is that compound constraints are still the exception rather than the rule. They are usually not very many, and the message with an error about their violation is not very common. Another thing is composite indexes. First, there are just a lot of them. Secondly, there are often more than one per table. And, thirdly, the developer and application administrator work with them constantly, checking the query plans.

Triggers

In this article, I consider only DML triggers, because I believe that all other types belong more to the area of ​​responsibility of the DBA, and not the developer. I call triggers according to the following rules:
 [_][_]{ }[_/]_{B|A|C (I|U|D)[S]}[_#]

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

In my projects, I single out two “typed” Goals (roles) of triggers:


Representation

View naming rules are no different from table naming rules. The only wish is to include in the name a sign that this object is a representation. Approaches here can be different. I met this attribute in the form of a prefix name. For example, V_ or even V $ , like Oracle system views. Personally, I use suffixes:

But I will not advise you. The dollar sign as a separator is a matter of habit. This is the “anchor” for my eyes, which allows us to distinguish the table from the view. Objectively, I can’t look at the data approach, so I don’t have anything against the “underscore” sign, like with Ferstein (suffixes _V and _MW ).

Sequences

I distinguish sequences among other objects with the _SEQ suffix and recommend naming them according to the following rule:
 [_][_]{  |    | }{_SEQ}

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

The name of the column (and here we recall, it 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 don't 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, the sequence for generating the primary key of the INTERNET_LOGINS table I would call ILG_SEQ , and the sequence for generating the login of a specific Internet account is LOGIN_SEQ .

Synonyms

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

Types

By type, I do not have a final opinion. I met different approaches to the naming of these objects, but I could not fully decide which approach was closer to me. I will describe here those that do not cause negative reactions in me:
  [_][_]{}[_ ]T

This template is recommended by Cowlem. The sign of the type collection is denoted by the symbol T. Thus, the type of a single object always has the suffix _T , the type of collection is _TT . For example, UTL_PARAMETER_T , UTL_PARAMETER_TT .
 [_][_]{}[S]_TYP

Here, S is a plural, and the TYP suffix qualifies a database object as a type. For example, UTL_PARAMETER_TYP , UTL_PARAMETERS_TYP . This is the approach I like least of all because the feature 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

I would like to single out a separate article for the code design of the program modules. Here is a template offered by Coleam. For treatment packages, Bill uses the following rule:
 [_][_]{/}[_. ][_PKG]

In terms of Coleham's NC, the Functional Modifier (for me, the term Subgroup is clearer) is used when allocating some functions into a separate package when refactoring. Let's just say this is an additional level of a logical group. For example, the UTIL package contained functions for working with numbers and strings. It was broken 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 package name. Therefore, the suffix _PKG is used only in cases where the package name may coincide with the name of another schema object.

For individual procedures and functions, Colem recommends the following template:
 [_]{/}

An action is a verb ( GET , SET , ASSIGN , RUN ), an objective is what needs to be done. For my part, I try not to use the procedures and functions outside the packages at all during development. In addition, the same functions I often have grouped by the objects with which they work, so I usually use the template
 {}[_]

Thus, the code prompter perfectly groups the procedures by objects: PARAM_GET , PARAM_SET , PARAM_CHECK , etc.

Conclusion


As promised, I provide a link to my own "Oracle NC" poster .
I, in no case, do not impose my rules and standards on anyone and do not insist on their use. I simply consider the presence of NC and compliance with its requirements in the team as a good style - the “politeness” of the developer to whoever will work with the system later.
Good luck with your projects.

PS 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 correct this defect. You can download the “source” of the NC-poster and edit it to fit your goals and objectives.

The article used the following source:
  1. Stephen Furstein's blog (Steven Feuerstein) and his Naming Convention and Code Standards
  2. Oracle Naming Standard Bill Colema (Bill Coulam)
  3. Tom Kyte's blog Ask Tom ...
    Materials Forum SQL.RU

Source: https://habr.com/ru/post/207102/

More articles:


All Articles