Bert Swope
Database Table
The primary factors affecting the choices in the creation of Data Warehouse (DW) naming convention policy standards are the type of implementation, pattern of the implementation, and any preexisting conventions.
Type of implementation
The type of implementation will affect your naming convention choices. Basically, this boils down to, are you working with a Commercial-Off-The-Shelf (COTS) data warehouse or doing a custom build?
Commercial-Off-The-Shelf (COTS)
If it is a Commercial-Off-The-Shelf (COTS) warehouse, which you are modifying and or enhancing, then it is very strongly recommended that you conform to the naming conventions of the COTS product. However, you may want to add an identifier to the conventions to identify your custom object.
Using this information as an exemplar:
- FAV = Favinger, Inc. (Company Name – Custom Identifier)
- GlobalSales = Global Sales (Subject)
- MV = Materialized View (Object Type)
Suffix Pattern Naming Convention
<<Custom Identifier>>_<<Object Subject Name>>_<<Object Type>>
Example: FAV_GlobalSales_MV
Prefix Pattern Naming Convention
<<Object Type>>_<<Custom Identifier>>_<<Object Subject Name>>
Example: MV_FAV_GlobalSales
Custom Data Warehouse Build
If you are creating a custom data warehouse from scratch, then you have more flexibility to choose your naming convention. However, you will still need to take into account a few factors to achieve the maximum benefit from you naming conventions.
- What is the high level pattern of you design?
- Are there any preexisting naming conventions?
Data Warehouse Patterns
Your naming convention will need to take into account the overall intent and design pattern of the data warehouse, the objects and naming conventions of each pattern will vary, if for no other reason than the differences in the objects, their purpose, and the depth of their relationships.
High level Pattern of the Data Warehouse Implementation
The high level pattern of you design whether an Operational Data Store (ODS), Enterprise Data Warehouse (EDW), Data Mart (DM) or something else, will need to guide your naming convention, as the depth of logical and/or processing zone of each pattern will vary and have some industry generally accepted conventions.
Structural Pattern of the Data Warehouse Implementation
The structural pattern of your data warehouse design whether, Snowflake, 3rd Normal Form, or Star Schema, will need to guide your naming convention, as the depth of relationships each pattern will vary, have some industry generally accepted conventions, and will relate directly to you High level Data Warehouse pattern.
Preexisting Conventions
Often omitted factor of data warehouse naming conventions are the sources of preexisting conventions, which can have significant impacts both from an engineering and political point of view. The sources of these conventions can vary and may or may not be formally documented.
A common source naming convention conflict is with preexisting implementations, which may not even be document. However, system objects and consumers are familiar will be exposed to these conventions, will need to be taken into account when accessing impacts to systems, political culture, user training, and the creation of a standard convention for your data warehouse.
The Relational Database Management System (RDBMS) in which you intend to build the data warehouse may have generally accepted conventions, which consumers may be familiar and have a preconceived expectations whether expressed or intended).
Change Management
Whatever data warehouse naming convention you chose, the naming conventions along with the data warehouse design patterns assumptions, should be well documented and placed in a managed and readily accessible, change management (CM) repository.