Columns

Column Name Rules

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

PRIMARY+MODIFIER+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_OWNER

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

  • X_COORDINATE

  • Y_COORDINATE

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.

  • 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, length, temperature, volume etc.

Example:

  • OIL_VOLUME

  • GAS_VOLUME

  • WATER_VOLUME

    • If a column name includes the name of a domain’s class word, then the column must be part of that domain.

Example:

  • All columns that end in DATE must be in the DATE domain.

  • All columns that end in PRESSURE must be in the PRESSURE domain.

Maximum of 30 characters is used for PDM table names, which will be compatible with Oracle database.

Column names are singular and in present tense.

Column names should proceed from general to specific, left to right.

Column 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.

  • 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.

Column Name Abbreviations

If the table name is too long, apply abbreviations.

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

Bad Example:

  • Is REC = RECOVERY, RECEIVER or RECORDED?

  • Is COMP = COMPLETED or COMPANY?

The abbreviations is using in PDM column names:

Abbreviation Full Name

ATTR

ATTRIBUTE

AVG

AVERAGE

BA

BUSINESS ASSOCIATE

BH

BOTTOMHOLE/BOTTOM_HOLE

BO

BUSINESS OBJECT

BOE

BARREL OF OIL EQUIVALENT

CAT

CATALOG

CRS

COORDINATE REFERENCE SYSTEM

CUM

CUMULATIVE

DESC

DESCRIPTION

DH

DOWNHOLE/DOWN HOLE

DIR

DIRECTIONAL

DRY

DRYNESS

ELEV

ELEVATION

EQUIP

EQUIPMENT

FL

FLOWLINE/FLOW LINE

_ID

IDENTIFIER

ID_

INSIDE DIAMETER OF

INFO

INFORMATION

INJ

INJECTION

KB

KELLY BUSH

LOC

LOCATION

MAX

MAXIMUM

MD

MEASURED DEPTH

NGL

NATURAL GAS LIQUID

NO

NUMBER

OBS

OBSERVE

OD

OUTSIDE DIAMETER

PRESS

PRESSURE

PROD

PRODUCTION/PRODUCT

R

REFERENCE

SEG

SEGMENT

SEP

SEPERATOR

SRVY

SURVEY

TD

TOTAL DEPTH

TEMP

TEMPERATURE

TVD

TRUE VERTICAL DEPTH

TZ

TIMEZONE

UWI

UNIQUE WELL IDENTIFIER

WH

WELLHEAD

Column Description

  • Every column has attribute name and display name.

  • 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.

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

Example:

  • INSIDE DIAMETER: Measurement of the inside diameter size of equipment.

Standardized Columns

All PDM tables will contain the following columns. Many of these columns are intended to provide audit tracking capabilities and to indicate which values are current and which are not.

Row Created By:

Should be added to every table in the data model. In many cases, this will be the system assigned userID of the person who changed the data, but business rules may dictate the use of other information.

Row Created Date:

Should be added to every table in the data model. Usually the system date that the data was added.

Row Changed By:

Should be added to every table in the data model. In many cases, this will be the system assigned userID of the person who changed the data, but business rules may dictate the use of other information.

Row Changed Date

Should be added to every table in the data model. Usually the system date of the change.

IS Active

Should be added to every table in the data model. Indicates whether this row of data is currently valid.

Remark

Should be added to every table in the data model, unless there is a %_REMARK subordinate table. Note that not more than one remark column should be added to any table.

Constraint

Primary Key Constraints

The primary key constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only one primary key, and this primary key can consist of single or multiple columns. In PDM, this primary key generally consists of single column.

Every table in the PDM Data Model contains a Primary Key.

  • The Primary key of a PDM table consists of a column that uniquely identify each new occurrence or row of data in the table.

  • The primary Keys In PDM may also be used to carry key identification values that are commonly queried down into subordinate tables

The primary key name:

  • Add a “_ID” as postfix to the table name to be the primary key name.

Example:

Table name: WELL

Primary key of this table: WELL_ID

The primary key label:

  • If the column is the primary key, in the meta table BUSINESS_OBJECT_ATTR'', the IS_PRIMARY_KEY'' value should be ``TRUE''.

PDM uses sequence for primary key generation policy. Default sequence name is seq_ + . It saves us not to read the inserted record again on unique key basis after insertion to get the generated primary key value.

  • The primary key is automatically generated in database. It is an auto-increment integer.

  • The primary key value is automatically generated when there is a new row of data.

  • The data type of the primary key is integer.

Unique Index

Unique indexes are indexes that help maintain data integrity by ensuring that no two row of data in a table have identical key values. The primary key will not accept NULL values whereas unique index can accept on NULL value. A table can have only one primary key whereas there can be multiple unique key on a table.

Unique indexes have two essential functions in support of this need:

  • Identify duplicates. In the Oil and Gas business, the combination of data values that is used to determine entries that are unique may vary geographically or over time.

For example, in well production tables, the combination of UWI and production date is always used to determine the record unique. That means for same well, there should not have two same production dates. Or for one same production date, the same well appears 2 times.

  • As indexes to identifying data appropriately for retrieval. It will increase the efficiency when retrieve data. Lookups on a unique index are generally very fast.

If you are inserting data that has more than one version, discuss the data with the business to determine an appropriate course of action.

  • Sometimes data genuinely has more than one version; in this case, it may be appropriate to select the most appropriate version of the data and load into database.

Unique Constraints

  • In PDM a unique constraints can be applied on a combination of multiple columns. In PDM it is called a composite unique constraint. Composite unique constraint name should be same for all the columns which fall under one composite unique constraint.

  • If you want API to verify/execute this unique constraint before data is inserted or updated to PDM then set verify to true in unique constraints table for this particular data.

Foreign Key Constraints

A foreign key is a key used to link two tables together. A foreign key is a field in one table that refers to the PRIMARY KEY in another table.

The foreign key constraint is used to prevent actions that would destroy links between tables.

The foreign key constraint also prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

All foreign key and unique key relationships must be defined in relationship metadata table. If a table has a foreign key relationship with another table then ``is_primary_key_relationship'' should be set to true against that particular column in relationship table.

NOTE1: When a table has foreign key constraints, both foreign key column and unique index columns of the parent table are needed.

Take WELL, WELLBORE tables as an example.

In WELL table, the WELL_ID is primary key, and UWI is unique index.

In WELLBORE table, the WELL_ID of well table is the foreign key of WELLBORE. Then in WELLBORE table, we both have WELL_ID and UWI columns as foreign keys. And the reason is:

  • The ID is not exposed in the user interface of the PDM application, because the ID is used to manage data but is not friendly for user to recognize it . When user edit data through user interface, the application will provide unique information as selection, which is easy and friendly for user to recognize.

  • The database allows some data redundancy but its data will be easier to query and avoid some table join. For these unique index, such as UWI, FIELD NAME, AREA NAME, TYPE NAME etc., they are commonly used information. User do not need to through joining the parent table to get these information anymore.

NOTE2: Before delete the value in the parent table, should delete the reference information in the child table first, then delete can be successful.

Check Constraints

Check constraints can be used to enforce the structural integrity of the data model, or to provide validation for certain kinds of values.

For the data type is Boolean, and named IS_%, PDM will check the value is TRUE'' OR FALSE''.

Data Types

Data_Type in database

The following data types are supported in PDM database in current version:

Data_Type Description Example

boolean

A Boolean data type can hold one of three possible values: true, false or null.

The data type of ” IS_ACTIVE” column in each table

bytea

A data type for digital information.

The WELL_TYPE_SYMBOL'' column in R_WELL_TYPE'' table.

character varying(n)

A data type for text. n is a positive integer. it can store strings up to n characters (not bytes) in length

The ``REMARK'' column in each table, and the data type is character varying(2000).

integer

A medium integer. Signed range is from -2147483648 to 2147483647. Unsigned range is from 0 to 4294967295. The size parameter specifies the maximum display width (which is 255)

The IDENTIFIER column of each table.

jsonb

The jsonb stores data in a decomposed binary form; that is, not as an ASCII/UTF-8 string, but as binary code.

The SEARCH_JSONB'' column in BO_SEARCH'' table, which is a specialized table of PDM.

numeric(precision, scale)

The precision is the total number of digits, while the scale is the number of digits in the fraction part. For example, the number 1234.567 has a precision of seven and a scale of three.

The OIL_VOLUME'' column in WELL_VOL_DAILY'' table.

smallint

Small integer ( SMALLINT ) is 2-byte signed integer that has a range from -32,768 to 32,767.

The LEVEL INDEX'' column in HIERARCHY'' table, which is a specialized table of PDM.

text

The text data type can store a string with unlimited length.

The FILE_NAME'' column in the FILE_INFO'' table,, which is a specialized table of PDM.

timestamp

Timestamp stores both date and time values.

The VOLUME_DATE'' column in WELL_VOL_DAILY'' table.

Data Control Type in User Interface

PDM application not only provides user a unified production data source, but also a friendly user interface to implement the data management. In the UI, there is data control_type in metadata management table. More information about the control_type, please refer to metadata chapter.

Data_Type Description

autoComplete

This data type is major for the data which is belongs to foreign key constraint data

calenderWithDate

Only date is shown in user interface, such as MM-DD-YYYY

calenderWithDateTime

Both date and time are shown in user interface, such as MM-DD-YYYY HH:MM:SS

checkbox

For the Boolean data, it is a check box in the UI. If checked, it means TRUE. If unchecked, it means FALSE.

input

This data type is major for character data that will show in user interface.

inputInt

This data type is major for integer data that will show in user interface.

inputNumeric

This data type is major for numerical data that will show in user interface.