Architectural Principles Reference Tables

Reference tables provide a valid list of values for a particular column(s). These are sometimes referred to as look-up tables, code tables or decode tables.

Contents 1 Objective 2 Guidelines 3 Reference table use 4 Population of Reference Tables 5 Structure 6 Related Column Naming Conventions 7 Back to Architectural Principles Index

Objective

1. To encourage inter-operability through the usage of standard structures and population of reference tables while encouraging the use of referential integrity.
2. To provide a mechanism that uses disk space efficiently, which improves data quality and consistency and which facilitates simple and consistent data retrieval.
3. A reference table may be used to validate data input; it contains a set of authorized values for a particular field.

Guidelines

1. These tables are to be named using R_ as a prefix.
2. Reference entities will be implemented as separate tables rather than one large table with a code type column.
   1. Individual tables must be separately defined; this facilitates model uptake and comprehension.
   2. Relationships can be enforced using foreign key constraints (this cannot be done using one large code table with code type as part of the primary key).
3. Every reference table will include a foreign key relationship to the reference table R_SOURCE, to indicate the source of each value in the reference table.
4. Some reference tables have relationships to other reference tables (i.e. R_PROVINCE_STATE and R_COUNTRY).
5. Other entities in the model exhibit reference behavior but are not identified as reference entities. (For example BUSINESS ASSOCIATE, FORMATION, ELLIPSOID etc.) Generally, if a table is used for any purpose other than validation of entries, it should not be called a reference table.

Reference table use

1. R_CODE_VERSION enables members to handle multiple sources of reference values. Multiple sources must then be rationalized into a single set of values.
2. The meaning of a NULL value in PPDM must be considered (unavailable, not applicable, lost data, expected to arrive later ….). Reference table values should contain default values which are consistent in form and which articulate the differences between meanings.
3. Reference tables that are used to control the behavior of vertical tables contain a foreign key to the table PPDM_PROPERTY_SET. This reference may be used to control the behavior of different kinds of reference values.

Population of Reference Tables

The Association may, on occasion, input some selected sample entries into reference tables where the information has specific value to the model, or to understanding the model.

Structure

Table structure template to be used for reference tables is as follows. Note that all columns are controlled by domains in the CASE tool.

CREATE TABLE R_SAMPLE_TABLE(

sample_table_type VARCHAR(20) not null,

abbreviation VARCHAR(12),

active_ind VARCHAR(1),

effective_date DATE,

expiry_date DATE,

long_name VARCHAR(255),

PPDM_GUID VARCHAR(38),

short_name VARCHAR(30),

source VARCHAR(20),

remark VARCHAR(2000),

row_quality VARCHAR(20),

row_changed_by DATE,

row_changed_date VARCHAR(30),

row_created_by DATE

row_created_date VARCHAR(30)

CONSTRAINT r_sample_table_pk PRIMARY KEY (sample_table_type));

Related Column Naming Conventions

Columns representing the relationships to the reference tables are generally named after the reference table name except where common business usage provides an alternate name or a qualifier is required to explicitly define the column usage. For example, columns that reference BUSINESS_ASSOCIATE may be called OPERATOR, COMPANY, BROKER etc.

Back to Architectural Principles Index