Architectural Principles Standardized Columns
All PPDM 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. However, the EFFECTIVE and EXPIRY date columns are intended for business information and should not be absorbed by system or data loading functions.
Contents |
SOURCE
This row indicates the source from which the data was obtained. Note that this may be a business associate, an application, a service provider, original paper documents etc. Use common sense business rules when populating this information.
This column (with a constraint to R_SOURCE) should be added to every table, unless the work group determines that the SOURCE of the parent table will always equal the source of the child table.
Example: SEIS_LINE contains the SOURCE that determines source for SEIS_POINT
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.
Many users populate this value by trigger on INSERT and on UPDATE. Others populate this value by trigger only on UPDATE (in this method, the column value may be NULL, making certain types of query more difficult). An implementation must create a business rule about which method they will use and enforce consistency throughout their systems.
ROW CHANGED DATE
Should be added to every table in the data model. Usually the system date of the change.
Many users populate this value by trigger on INSERT and on UPDATE. Others populate this value by trigger only on UPDATE (in this method, the column value may be NULL, making certain types of query more difficult). An implementation must create a business rule about which method they will use and enforce consistency throughout their systems.
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.
Some members may choose to implement this column using a NOT NULL constraint. In this case, you may use an optional procedure provided by the PPDM Association that alters these columns. However, you should note that some companies cannot enforce a not null constraint of this type for legal or policy reason. Care should be taken to research the implications before implementing this script.
Many users populate this value by trigger on INSERT.
ROW CREATED DATE
Should be added to every table in the data model. Usually the system date that the data was added.
Some members may choose to implement this column using a NOT NULL constraint. In this case, you may use an optional procedure provided by the PPDM Association that alters these columns. However, you should note that some companies cannot enforce a not null constraint of this type for legal or policy reason. Care should be taken to research the implications before implementing this script.
Many users populate this value by trigger on INSERT.
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. If more than one remark column is needed, create a subordinate %_REMARK table.
EFFECTIVE DATE
Should be added to every table in the data model. Indicates the date that this data first came into effect from a business perspective. In business transactions, this date may be before or after the date the data is added to the database. For reference tables, this is often the date that a value is included for use (approved by the business).
Please note that this column is NOT intended to be used for information aboaut the technical implementation or management of the data in a table. It is a business value, and should be derived from business information. If a system date is to be used, the system date should be based on the needs of the business, rather than on dates that system transactions occur (even if those dates may turn out to be the same date).
EXPIRY DATE
Should be added to every table in the data model. Indicates the date that this data is no longer in effect. In business transactions, this date is often the date that the information becomes obsolete, invalid or is replaced, such as the date that a contract expires, or a reference value is not to be used any longer. Note that expiry dates may exist in the future.
Please note that this column is NOT intended to be used for information aboaut the technical implementation or management of the data in a table. It is a business value, and should be derived from business information. If a system date is to be used, the system date should be based on the needs of the business, rather than on dates that system transactions occur (even if those dates may turn out to be the same date).
ACTIVE IND
Should be added to every table in the data model. Indicates whether this row of data is currently valid. Setting this flag to N allows users to retain archival data but disallow its use in current application.
This column is implemented as varchar2(1). Note that a char format would be equally effective, but the destructive change to the model is such that the modeling committee has recommended against using the char data type.
PPDM GUID
Should be added to every table in support of GUID practices. An optional set of DDL is provided that will change the GUID columns to NOT NULL and add unique indexes to each. An additional set of DDL will create foreign key constraints. This column may be used in cases where a one part (singleton) surrogate identifier is needed.
ROW QUALITY
Should be added to every table in the data model. Indicates the quality of a row of data in a table. Information about every attribute in a row may be stored in other tables in PPDM.
Go to
- Architectural Principles NULL Data
- Architectural Principles Dates
- Architectural Principles Versioning
- Architectural Principles Vertical Tables
- Architectural Principles Sequence Control
- Architectural Principles Economics and Financial
- Architectural Principles Column Precision
- Architectural Principles Column Field Type
- Architectural Principles Standardized Columns
- Architectural Principles SQL View Definitions
- Architectural Principles Other SQL Definitions
