Before designing your physical schemas it is highly recommended to take the time to develop naming conventions. Because table and view names are Immutable, and changing column names after the fact will cause issues with reports and business schemas, it is worth the time up front to design a set of conventions and a method of governance. Additionally, a set of standards will provide clarity and consistency to your schemas and minimize the risk of losing institutional knowledge from staff turnover. It can be helpful to think of three kinds of rules that will comprise your complete naming convention:
Within the physical schema, the overall goal of the naming standards (besides consistency) should be to easily identify the source of each object. Business friendly naming will typically occur at the Business Schema level.
We recommend that you be familiar with your data sources and these Incorta concepts before exploring this topic further:
These concepts apply to all releases of Incorta for both on-premise and cloud.
As a starting point you should review the name validation rules for physical schema table names:
(_) or Dollar sign
(_) and Dollar sign
($), cannot contain special characters, symbols, or spaces
It is also recommended to decide on a set of representation terms if columns will need to be renamed from their source system(s), although these typically will be utilized more often in your business schemas. This example list of representation terms and definitions is based on the Class Word list in the Guide on Data Entity Naming Conventions from NIST.
Amount – Monetary quantity.
Average – Numeric value representing an arithmetic mean.
Count – Non-monetary numeric value arrived at by counting.
Code – A system of valid symbols that substitute for longer values.
Date – Calendar date.
Measure – A record of the dimensions, capacity/amount (non-monetary) of an object.
Name – A designation for an object.
Number – A number associated with an object, used as an identifier.
Quantity – Non-monetary numeric value not arrived at by counting.
Rate – A quantity or amount considered in relation to another quantity or amount.
Text – An unformatted descriptive field.
Time – Time of day or duration.
Lastly, you should compile a list of all of the data sources you are going to be using, as well as any likely to be added in the future. Taking a holistic view now will help prevent contradictions and re-work down the road. With these foundational pieces in place, you can begin identifying your rules
Your first step should be identifying the semantic rules you wish to use. These are a set of guidelines for naming based on the components or attributes of the table or column.
Now that you have defined semantic rules, your syntax rules will specify the arrangement of these components. The most common usage is the class (dim, fact, etc.) is given the leftmost position in the name, with the type (table, alias, view, etc.) given the rightmost position. Between these two terms the basic table name will reside. For column names that are not retaining their original names, any qualifying terms should be placed before the representation term.
Lexical Rules are another aspect that often come into play more heavily in Business Schemas, but it is still critical to identify and document them now. These rules will determine the look and feel of your object names. Some factors to consider and potential examples for your lexical rules include:
If you are feeling overwhelmed by all of this, there are additional resources you can look to, including ISO 11179-5, Information technology – Specification and standardization of data elements, Part 5: Naming and identification principles for data elements, or the Guide on Data Entity Naming Conventions from NIST.
Outside of adhering to validation rules, there is no "right" or "wrong" when designing naming standards as long as they are comprehensive, consistent, clear and communicated. And with a well-designed set of naming conventions, you will be ready to move on to designing your schemas.