Server (Cells) Design
Space shortcuts
Space Tools

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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 will support supports custom columns apart from the required 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 four REQUIRED columnsone required column:

  1. 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.
Info Box
titleNote

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.

...

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

...

  • 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.