Server (Cells) Design
Space shortcuts
Space Tools
Skip to end of metadata
Go to start of metadata

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.)
Starting from version 1.6, this table will support custom columns apart from the required 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 columns:

  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.


  1. BIRTH_DATE
  • Can be null.
  • Contains the patient date of birth (if it exists).
  • Date-time field.


  1. DEATH_DATE
  • Can be null.
  • Contains the patient date of death (if it exists).
  • Date-time field.


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



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



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. An example of a patient table is shown below. In the example table, there are eight optional columns.

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



  • No labels