== i2b2 - DB Basics == You can get all of the following from the documentation, but here are pertinent things to where we are... === Structure === This is a very bare basics. At this stage we should be primarily interested in the observation_fact table and its relationship with the concept_dimension table within the CRC Cell. And then the correspondence with any ontology tables within the Ontology Cell. Interest in the patient_dimension, visit_dimension and other of the tables within the CRC are a little further down the line.[[BR]] There can be a number of ontology tables within the Ontology Cell. We know which are ontology tables by an entry in the table_access table.[[BR]] The !QueryTool uses the ontology tables within the Ontology Cell to format queries for submission against the tables within the CRC Cell. {{{ +-------------CRC Cell-------------+ +-----------Ontology Cell---------+ | | | | | +===================+ | | +=====================+ | | | concept_dimension |<<<=========>>>| ontology tables |>: | | +=========^=========+ | | +==========^==========+ : | | | | | | : | | | | | | : | | | | | | : | | +=========^=========+ | | +==========^==========+ : | | | observation_fact | | | | table_access | : | | +===V===========V===+ | | +=====================+ : | | | | | | : | | | | | +-----------------------------:---+ | +==========V========+ | | : | | patient_dimension | | | +------------+ : | +===================+ | |<<<<<<<<<<<| QUERY TOOL |<<<<<<<<: | | | +------------+ | +==========V======+ | | | visit_dimension | | | +=================+ | +----------------------------------+ }}} === Ontology Cell: DDL for Table ONYX === {{{ -- ORACLE -- CREATE TABLE ONYX ( "C_HLEVEL" NUMBER(22,0) NOT NULL, "C_FULLNAME" VARCHAR2(700) NOT NULL, "C_NAME" VARCHAR2(2000) NOT NULL, "C_SYNONYM_CD" CHAR(1) NOT NULL, "C_VISUALATTRIBUTES" CHAR(3) NOT NULL, "C_TOTALNUM" NUMBER(22,0) NULL, "C_BASECODE" VARCHAR2(50) NULL, "C_METADATAXML" CLOB NULL, "C_FACTTABLECOLUMN" VARCHAR2(50) NOT NULL, "C_TABLENAME" VARCHAR2(50) NOT NULL, "C_COLUMNNAME" VARCHAR2(50) NOT NULL, "C_COLUMNDATATYPE" VARCHAR2(50) NOT NULL, "C_OPERATOR" VARCHAR2(10) NOT NULL, "C_DIMCODE" VARCHAR2(700) NOT NULL, "C_COMMENT" CLOB NULL, "C_TOOLTIP" VARCHAR2(900) NULL, "UPDATE_DATE" DATE NOT NULL, "DOWNLOAD_DATE" DATE NULL, "IMPORT_DATE" DATE NULL, "SOURCESYSTEM_CD" VARCHAR2(50) NULL, "VALUETYPE_CD" VARCHAR2(50) NULL ) ; }}} '''C_HLEVEL''': hierarchical level in the tree. 0,1,2 etc[[br]] '''C_FULLNAME''': full path within the tree.[[br]] '''C_NAME''': in the demo system this is the last part of the path within the tree. Don't understand the size implications between C_FULLNAME and C_NAME.[[br]] '''C_SYNONYM_CD''': Y or N regarding a synonym[[br]] '''C_VISUALATTRIBUTES''': eg 'LA' would be an active leaf node. * 1st character: F = Folder C = Container M = Multiple L = Leaf * 2nd character: A = Active I = Inactive H = Hidden * 3rd character: E = editable