== 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
   ) ;
}}}
These are worth highlighting...[[br]]
'''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
'''C_TOTALNUM''': Not used, but suspect can be used in the latest version (for performance reasons?).[[br]]
'''C_BASECODE''': the term that describes the ontological concept.[[br]]
'''C_METADATAXML''': an optional field to store extra information about the concept in xml format. It is currently used to describe value metadata associated with a lab finding. For further details, see [http://trac.briccs.org.uk/wiki/i2b2%20-%20Documents Documents page].Jeff  has points to make about this.[[br]]
'''C_FACTTABLECOLUMN''': name of the column in the CRC cell observation_fact table that holds the concept code.[[br]]
'''C_TABLENAME''': name of the lookup table within the CRC cell that holds the concept code and concept path. In the top diagram this would be concept_dimension.[[br]]
'''C_COLUMNNAME''': the name of the column within the table described by C_TABLENAME that holds the concept path. In the top diagram this would be the column within the concept_dimension table, which in our case is called CONCEPT_PATH. See below. [[BR]]
'''C_COLUMNDATATYPE''': either ‘T’ for text or ‘N’ for numeric and describes the datatype of the concept. Don't understand this fully. Jeff has questions about this.[[br]]
'''C_OPERATOR''': the SQL operator used in the WHERE clause of the SQL query. It is usually either ‘LIKE’ or ‘=’.[[br]]
'''C_DIMCODE''': the path that lives in the dimension table and maps to the concept path.[[br]]

=== CRC Cell: DDL for Table concept_dimension ===
{{{
-- Oracle -- 
CREATE TABLE CONCEPT_DIMENSION ( 
	CONCEPT_CD          VARCHAR2(50)   NOT NULL,
	CONCEPT_PATH        VARCHAR2(700)  NOT NULL,
	NAME_CHAR           VARCHAR2(2000) NULL,
	CONCEPT_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 CONCEPT_DIMENSION_PK PRIMARY KEY(CONCEPT_PATH)
) ;	
}}}