Architectural Principles Dates
Member data, particularly archival or historical data, is often incomplete and may be missing values that are present for new data. Even new data may be incomplete in some parts of the world. This is particularly a problem for date values in PPDM; it is common for dates to be missing or inexact (i.e. year or month and year only).
PPDM queries often require use of “search between dates” clauses. These queries are greatly benefited by the use of the complex data type DATE. However, when the date known is only approximate, creating dummy values in a date field in order to facilitate queries may present data integrity or veracity problems. Clearly, there are cases when both search functionality and accuracy of retrieval are needed.
Contents |
Modeling Guidelines
1. Consideration must be given to the impact on business queries if the DATE field may be NULL. NULL date fields negatively impact search between dates queries; on the other hand, populating columns with meaningless dummy data leads to mistrust by users. Recommendations for population procedures should be made during the modeling process.
2. PPDM offers two methods for modeling dates:
- DATE as a native database data type. All date values should be modeled in this form, to enable searches and indexing.
- DATE_DESC as a varchar2 (8) field. Where the date is inexact and it is desirable to indicate the level to which it is known, DATE_DESC may be used in addition to the DATE field.
Use of DATE fields in Primary Keys
1. PPDM recommends that DATE fields not be used as part of any Primary key. A unique component, such as an %_OBS_NO or %_ID field is preferable.
- In many cases, dates are not known when loading data into PPDM. If dates are part of a Primary key, this forces users to enter false (dummy) data into the PK. This introduces a level of uncertainty into the quality of the Primary Key fields – a highly undesirable situation.
Population of unknown dates
1. Policy should be to populate in a way that will include as much data in date searches as possible.
2. Note that various RDBMS products handle null date components differently. This can have a significant impact when you are integrating systems that use or have used different RDBMS products or versions of the same product. It’s very important to have and implement consistent business rules for dates.
3. PPDM recommends this business practice:
- If the month is unknown set to 01.
- If the day is unknown set to 01.
- If the year is unknown column must be NULL
- 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
