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:
Additionally, the following columns are core fields that should be included if available:
BIRTH_DATE
DEATH_DATE
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.
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:
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 |