Each record in the PATIENT_DIMENSION table represents a patient in the database. The table includes demographic information such as gender, age, race, etc. Most attributes of the patient dimension table are discrete (i.e. Male / Female, Zip code, etc.) The PATIENT_DIMENSION table may have an unlimited number of optional columns and their data types and coding systems are specific to the local implementation. The default patient table is shown below.
PATIENT_DIMENSION | ||
PK | PATIENT_NUM | int |
VITAL_STATUS_CD | varchar(50) | |
BIRTH_DATE | datetime | |
DEATH_DATE | datetime | |
SEX_CD* | varchar(50) | |
AGE_IN_YEARS_NUM* | int | |
LANGUAGE_CD* | varchar(50) | |
RACE_CD | varchar(50) | |
MARITAL_STATUS_CD* | varchar(50) | |
RELIGION_CD* | varchar(50) | |
ZIP_CD* | varchar(10) | |
STATECITYZIP_PATH | varchar(700) | |
PATIENT_BLOB | text | |
UPDATE_DATE | datetime | |
DOWNLOAD_DATE | datetime | |
IMPORT_DATE | datetime | |
SOURCESYSTEM_CD | varchar(50) | |
UPLOAD_ID | int |
Starting from version 1.6, this table supports custom columns apart from the included ones. The PDO service will return the custom fields in the <param> tag within the <patient> element. Please refer to the section called CODE_LOOKUP Table for adding the descriptions to the custom fields.
The following table shows the rules for mapping the custom field's database type to the xml type.
Database to XML type mapping:
XML type | Oracle Type | PostgreSQL Type | SQL Server Type |
string | varchar, varchar2, char | varchar, text, char | nchar, text, char, ntext |
dateTime | date | timestamp | date, datetime |
int | int, number | int | int, bigint |
decimal | number (N,N), decimal, float | decimal | decimal |
The PATIENT_DIMENSION table has the following one required column:
- PATIENT_NUM
- It is the primary key for the table; therefore it cannot contain duplicates.
- Cannot be null.
- Holds a reference number for the patient within the data repository.
- Integer field.
Additionally, the following columns are core fields that should be included if available:
BIRTH_DATE
- Can be null.
- Contains the patient date of birth (if it exists).
- Date-time field.
DEATH_DATE
- Can be null.
- Contains the patient date of death (if it exists).
- Date-time field.
Note
The BIRTH_DATE and DEATH_DATE columns are not standardized to a specific time zone, a limitation that may need to be addressed in the future.
The rules for using the codes in the columns to perform queries are represented in the metadata. For example, the columns shown in the table example include a RACE_CD and a STATECITYZIP_CD.
- The codes from the RACE_CD column are enumerated values that may be grouped together to achieve a desired result. For instance, if there are four codes to represent a race of "white"; W, WHITE, WHT, and WHITE-HISPANIC then all four codes can be counted directly to determine the number of white-race patients in the database.
- The codes from the STATECITYZIP_CD are strings that represent hierarchical information. In the way, the string is queried from left to right in a string comparison to determine which patients are returned by the query. For example, if a code is MA\BOSTON\02114 and all the patient in BOSTON are desired, the string "MA\BOSTON*" (where * is a wildcard) would be queried.
VITAL_STATUS_CD is not used by the platform but could be utilized by an ontology, and there exists a vital status breakdown option. Therefore the following schema is recommended for VITAL_STATUS_CD, but is not required:
- Contains a code that represents the vital status of the patient and the precision of the vital status data.
- The code consists of two characters; the first one represents the validity of the DEATH_DATE and the second one is for the BIRTH_DATE.
- These values are:
KEY:
"*" means that a second character should be the birth date indicator (if exists)
"_" means that a first character should be the death date indicator (if exists)
Date Explained | Value | Description | |
Death date | N* | Living | corresponds to a null DEATH_DATE |
Death date | (null)* | Living | corresponds to a null DEATH_DATE |
Death date | U* | Unknown | corresponds to a null DEATH_DATE |
Death date | Z* | Deceased | corresponds to a null DEATH_DATE |
Death date | Y* | Deceased | DEATH_DATE accurate to day |
Death date | M* | Deceased | DEATH_DATE accurate to month |
Death date | X* | Deceased | DEATH_DATE accurate to year |
Death date | R* | Deceased | DEATH_DATE accurate to hour |
Death date | T* | Deceased | DEATH_DATE accurate to minute |
Death date | S* | Deceased | DEATH_DATE accurate to second |
Birth date | _L | Unknown | corresponds to a null BIRTH_DATE |
Birth date | _(null) | Known | BIRTH_DATE accurate to day |
Birth date | _D | Known | BIRTH_DATE accurate to day |
Birth date | _B | Known | BIRTH_DATE accurate to month |
Birth date | _F | Known | BIRTH_DATE accurate to year |
Birth date | _H | Known | BIRTH_DATE accurate to hour |
Birth date | _I | Known | BIRTH_DATE accurate to minute |
Birth date | _C | Known | BIRTH_DATE accurate to second |
- The codes for this field were determined arbitrarily as there was no standardized coding system for their representation.
