Architectural Principles Columns

Contents 1 Structure 1.1 Primary Words 1.2 Modifier Words 1.3 Class Words 2 Structure Rules 3 Name Component Rules 4 PPDM Class Words 5 Reserved Words 6 Primary Key Column length 7 Column Comments 8 Naming Process 9 Source as a column 10 Table name references in PPDM 11 Back to Architectural Principles Index

Structure

Column names are made up of a collection of words, each of which has particular significance. The name components are:

PRIMARY + MODIFIER(s) + CLASS WORD

Primary Words

The primary word is usually a noun describing the subject area of the name. Normally the subject area is implied by the context and table the column is in. If required, a subject area or partial table name may be used to explicitly state the column usage.

Example:

• WELL_NAME
• WELL_NUMBER

Modifier Words

The modifier word is used to provide additional description of the column being named. Modifiers are used to further describe the primary word, class word or both.

Example:

• CURRENT_STATUS
• FINAL_DRILL_DATE
• SURFACE_LATITUDE

Class Words

A class word identifies the type or category of data being described by the column name. This may be a general classification such as DATE or NUMBER or more specific such as TEMPERATURE or PRESSURE.

Example:

• SPUD_DATE
• TOP_DEPTH
• GROUND_ELEV

Structure Rules

• Maximum of 27 characters used for PPDM column names.
• Column names are singular and in present tense.
• Column names should proceed from general to specific, left to right.

Example:

• INVENTORY_CLOSE_BALANCE
• SEGMENT_LENGTH_UOM

Name Component Rules

• Names only contain alphanumeric characters and underscore.
• Names are not case sensitive.
• Complete names must not be in the Oracle or PL SQL reserved words list (see Appendix A for a list).
• Avoid the use of 'A', 'AN', 'AND', 'OF', 'OR', 'THE'.
• Be aware of and avoid possible confusion with abbreviated column names in the same or other modules, to avoid misinterpretation of column meaning.

Example:

• Is REC = RECOVERY, RECEIVER or RECORDED?
• Is COMP = COMPLETED or COMPANY?

• With few exceptions, all parts of a column name are either the full word or a PPDM recognized abbreviation.

Example:

• Use DIAMETER instead of DIA, DIAM, DMTR
• Use DEPTH instead of DEP, DPTH

• In a few instances, there may be no recognized full word, only an industry standard abbreviation or acronym.

Example:

• GOR used everywhere, gas oil ratio not allowed.
• DLS used instead of Dominion Land Survey
• UOM used instead of Unit of Measure
• OUOM used instead of Original Unit of Measure

• Where a column has an associated UOM or OUOM column, the name of the column should allow exact character string correspondence to the UOM or OUOM column.

Example:

• ACQTN_ SHOTPT_ INTERVAL
• ACQTN_ SHOTPT_ INTERVAL_ OUOM

• Columns present in multiple tables that have a common definition should be named consistently unless business confusion is created. If the choice is to abbreviate the column name then the name will be abbreviated in all occurrences. If the choice is not to abbreviate the column name then all occurrences will not be abbreviated.

• Columns that represent foreign keys in child tables should usually be given the same name as the column in the parent table unless there is more than one relationship or denormalization to the parent table. In this case, names that differentiate the relationships should be used.

Examples:

Occurrences of Business Associates are often named to refer to the role played by the Business Associate, such as Operator, Address for Service etc.

• OPERATOR_BA_ID
• CONTACT_BA_ID

The WELL table has two relationships to the NODE table.

• SURFACE_NODE_ID
• BASE_NODE_ID

The SEIS_LINE table has two relationships to the SEIS_POINT table

• FIRST_POINT_ID
• LAST_POINT_ID

PPDM Class Words

Column names contain class words, which are used to define the type of information kept in the columns by referencing data value concepts such as depth, elevation, latitude, longitude, length, temperature etc.

Certain class words have been defined and reserved, so that their use in the data model may be consistent in their meaning, structure and usage. In some cases, the format has also been defined, and a domain established to assist with managing how they are used. Here is a list of the Class Words in PPDM.

• The class word is usually the last component in the column name, except for columns containing _OUOM or _UOM. In these cases, the class word is usually second last in the column names.

Example:

• ENERGY_SWEEP_DURATION
• ENERGY_SWEEP_DURATION_OUOM

• Columns that represent units of measures will end in

• _UOM for storage unit of measure
• _OUOM for an original unit of measure.

• When a class word is used by itself it may be the full word used even if the abbreviation is acceptable for other column names.

Reserved Words

When naming columns, avoid using words that are reserved by other databases and functions. Here is a list of the Reserved Words we currently watch. This list grows frequently, and is difficult to keep up with. For new table and column names, we recommend that the name consist of at least two parts (AAA_BBB).

Primary Key Column length

• Primary key columns in reference tables are in the Case tool domain “TYPE”. They have a length and datatype of varchar2(20).

• Primary key columns in business tables that are named %_ID are in the Case tool domain “IDENTIFIER” and have a length and datatype of varchar2(20).

• Primary key columns in tables that are named %_SEQ_NO are in the Case tool domain “SEQ NO” and have a length and datatype of number(8,0).

• Primary key columns in tables that are named %_OBS_NO are in the Case tool domain “OBS NO” and have a length and datatype of number(8,0).

Column Comments

• Every column must have a complete description explaining what it is.

• In some cases, a few examples are helpful to explain how to use the column.

• Where comments are taken from existing glossary sources, the source of the comments will be attributed in the column comments.

• Column long names always appear as the first part of the column comments (in upper case), followed by a colon (:).

Example:

SOURCE: The source of this row of data, either an agency or organization, a software application, physical document, manual key entry or other origin.

Naming Process

• Construct business name (fully descriptive).

• If required choose appropriate primary word.
• Choose appropriate modifier(s).
• Choose appropriate class word.
• Avoid using business terminology that is not used consistently as this will serve to confuse implementers.

• Determine if name meets maximum length specifications (27 characters for column names).

• If too long, apply abbreviations. Be consistent in how groups of column names are applied, however. For example, column XXX and XXX_OUOM should have the same XXX components.

• Be consistent. Naming abbreviations or class words that have been used in past should be re-used when the context is similar. Remember that the objective is to ensure the model is as consistent and easy to understand as possible.

• It may be difficult to arrive at a name that pleases everyone in the work group. Use the column comments to clarify the meaning and usage of the column.

Source as a column

Most PPDM tables contain a reference to the SOURCE. Source is taken to mean the direct source of the data that was loaded into this row. If Site A (a data vendor) gets their data from Source Q (a government), and Site B (an O&G company) purchases their data from site A, then the SOURCE for A is Q, and the source for B is A.

A source may be a business associate, a project, an application or a published reference. The table referencing these sources has been added, so that more complete attribution of data may be supported.

• Where the SOURCE column originates in the host table (as a FK from R_SOURCE), the column should be called SOURCE.

• Where the SOURCE column comes from another table, or there is more than one SOURCE column, each SOURCE column should be prefixed to ensure the columns are not confused during use. (ie: PDEN_SOURCE and PROD_STRING_SOURCE)

Table name references in PPDM

Table names are used as references in the PPDM model, usually as a FK reference to PPDM_TABLE. Since users are permitted to add tables to the PPDM schema, according to the rules in section 11, the length of column references to table names have been extended from 24 characters to 30 characters to allow member extensions to be accommodates.

Back to Architectural Principles Index