Changes between Version 22 and Version 23 of i2b2 Database Structure

Timestamp:

02/21/11 10:26:00 (14 years ago)

Author:

jeff.lusted

Comment:

--

i2b2 Database Structure

@@ -78 +78 @@
 
 === CRC Cell: DDL for Table concept_dimension ===
+
 The following sentences are from the design document...[[br]]
 The concept_dimension table contains one row for each concept. Possible
@@ -103 +104 @@
    1. name_char: name of the concept.
 
-
+=== CRC Cell: DDL for Table observation_fact ===
+The fact table of the i2b2 star schema: represents
+the intersection of the dimension tables. Each row describes one observation about a
+patient made during a visit. Most queries in the i2b2 database require joining the
+observation_fact table with one or more dimension tables together.
+{{{
+-- ORACLE --
+CREATE TABLE OBSERVATION_FACT (
+        ENCOUNTER_NUM           NUMBER(38,0) NOT NULL,
+        PATIENT_NUM             NUMBER(38,0) NOT NULL,
+        CONCEPT_CD              VARCHAR2(50) NOT NULL,
+        PROVIDER_ID             VARCHAR2(50) NOT NULL,
+        START_DATE              DATE NOT NULL,
+        MODIFIER_CD             VARCHAR2(100) NOT NULL,
+        VALTYPE_CD              VARCHAR2(50) NULL,
+        TVAL_CHAR               VARCHAR2(255) NULL,
+        NVAL_NUM                NUMBER(18,5) NULL,
+        VALUEFLAG_CD            VARCHAR2(50) NULL,
+        QUANTITY_NUM            NUMBER(18,5) NULL,
+        INSTANCE_NUM            NUMBER(18,0) NULL,
+        UNITS_CD                VARCHAR2(50) NULL,
+        END_DATE                DATE NULL,
+        LOCATION_CD             VARCHAR2(50) NULL,
+        CONFIDENCE_NUM          NUMBER(18,5) NULL,
+        OBSERVATION_BLOB        CLOB NULL,
+        UPDATE_DATE             DATE NULL,
+        DOWNLOAD_DATE           DATE NULL,
+        IMPORT_DATE             DATE NULL,
+        SOURCESYSTEM_CD         VARCHAR2(50) NULL,
+        UPLOAD_ID               NUMBER(38,0) NULL, 
+    CONSTRAINT OBSERVATION_FACT_PK PRIMARY KEY(ENCOUNTER_NUM,CONCEPT_CD,PROVIDER_ID,START_DATE,MODIFIER_CD)
+) ;
+}}}