REFERENCE TABLES

From PPDM Wiki
Jump to: navigation, search

Why Reference Tables Matter

In the Oil and Gas industry, relational (and other) databases have been used for decades to help organize and structure data so it can be used in analysis, planning, operations and decision making. While databases are, in themselves, very powerful tools for structuring data and making it available for queries, they are only able to perform effectively if the data in the database is correct, consistent and appropriate. Databases that contain poor quality data are difficult to use.

Take for example a database that contains information about wells. It's important to know where each well is located, so the operating company can ensure that they are meeting the necessary regulatory requirements, filling in the proper forms and notifications, conducting the necessary operations, communicating with the right people and so on. That means the business user needs to search the database and find all of the wells that are in a specific area (say Orange County).

If your database is populated by many users over a long period of time, and the data in the database has come from more than one source or process, it is very likely that the COUNTY information in our example has not been entered consistently. (This is a very common situation in the Oil and Gas Industry. Projects that integrate data from multiple sources together often struggle to clean up values lists that contain "bad" data.) That means that some of your wells will be identified in:

  • Orange county
  • Orange County
  • Orange Cty
  • Orange
  • Ornage
  • Org Cty
  • and so on.

However, if you create a reference table to contain a list of valid counties, and only allow your users to select a county from the authorized list of values, it becomes possible to prevent "bad" data from getting into your database. In relational databases, this is typically done by creating a reference table to contain the list of approved values, and adding a Foreign Key to the reference table in the COUNTY column of the WELL table.

PPDM 3.8 contains over 600 such reference tables. Each table is intended to store lists of valid entries that are used in one or more business tables. Simple lists of values are contained in tables that are named "R_%" and contain lists of valid values that are used to support referential integrity (foreign keys) during data entry into business tables. If a value is not found in the reference table, it will not load into the business table where the reference table is used.

Some reference lists are more complex, and need to store additional descriptive information in order to be useful. These lists of values are contained in table sets that exhibit "reference behavior". The AREAS Module exhibits complex reference behavior. BUSINESS ASSOCIATES contains a parent table with a list of all BUSINESS_ASSOCIATE information, but is the parent table of several tables that fully describe each BUSINESS_ASSOCIATE. Modules that exhibit reference behavior are also called SUPPORT MODULES in PPDM 3.8.

This section of the documentation describes simple reference tables only.


About Reference tables

All PPDM reference tables contain basically the same set of columns:

  • PRIMARY KEY (may be more than one component)
  • ABBREVIATION
  • ACTIVE_IND
  • EFFECTIVE_DATE
  • EXPIRY_DATE
  • LONG_NAME
  • PPDM_GUID
  • REMARK
  • SHORT_NAME
  • SOURCE
  • ROW_CHANGED_BY
  • ROW_CHANGED_DATE
  • ROW_CREATED_BY
  • ROW_CREATED_DATE
  • ROW_QUALITY

these tables are used in PPDM 3.8

Retrieved from "http://www.ppdm.org/wiki/index.php/REFERENCE_TABLES"
Personal tools