Biomaterials General Data Tables Measurable Data Tables Protocol/Investigator/User Tables RED Tables Subject Tables Substance Data Tables BTRIS_Applications ApplicationId ApplicationName LoweredApplicationName Description BTRIS_Investigator Investigator_uid Last_name First_name Middle_initial Display_name Suffix NIH_Login NED_id Degree Institute_acronym Institute_acronym_CONCEPT Branch_abb Branch_abb_CONCEPT Professional_designation Office_address Office_phone Building Room Quick_Address Appl_Source_CD Date_Created Date_Modified Last_Modified_Load_Audit_uid Protocol_number Role Use BTRIS_Notification Notification_ID Table_Name Data_Source Domain Message Notification_Row Date_Created BTRIS_Roles RoleId ApplicationId RoleName LoweredRoleName Description Investigator_RoleId BTRIS_Users UserId ApplicationId Investigator_uid UserName LoweredUserName MobileAlias IsAnonymous LastActivityDate Hide_Not_On_Protocol DataAccessStatus BTRIS_UsersInRoles RoleId UserId Protocol_uid Biomaterials UID Partition_Date_Key MRN Source_Subject_ID Source_Biomaterial_ID Class Class_CONCEPT Type Type_CONCEPT Source_Name Parent_Biomaterial_ID Specimen_Quality Specimen_Quality_CONCEPT Current_Status Current_Status_CONCEPT Current_Status_Date Notes Specimen_Date_Created Specimen_Date_Received Subject_Code External_ID Record_Create_Date Record_Modified_Date Appl_Source_CD Date_Created Date_Modified Last_Modified_Load_Audit_UID Pathological_Status Pathological_Status_CONCEPT Load_Batch_Info Biomaterials_EAV Entity_GUID Sequence Partition_date_key Attribute_CONCEPT Value Value_Type_CONCEPT Value_CONCEPT Date_Created Date_Modified Last_Modified_Load_Audit_uid Load_Batch_Info CRISUsers FSSUserID Concept_Search_String Search_String Search_String_Source Concept_GID Concept_Code Event_General Event_GUID Partition_date_key Subject_GUID Child_Flag Parent_GUID Sequence Event_Name Event_Name_CONCEPT Event_ID Provider Primary_Date_Time Appl_Source_CD Date_Created Date_Modified Load_Batch_Info Last_Modified_Load_Audit_uid Status Status_Concept Domain_Concept Event_Note Event_General_EAV Entity_GUID Sequence Partition_date_key Attribute_CONCEPT Value Value_Type_CONCEPT Value_CONCEPT Date_created Date_Modified Last_Modified_Load_Audit_uid Load_Batch_Info Event_Measurable Event_GUID Partition_date_key Subject_GUID Child_Flag Parent_GUID Sequence Event_Name Event_Name_CONCEPT Event_ID Provider Primary_Date_Time Appl_Source_CD Date_Created Date_Modified Load_Batch_Info Last_Modified_Load_Audit_uid DeIdentified_Flag Blinded_Flag Status Status_Concept Domain_Concept Event_Note Event_Measurable_EAV Entity_GUID Sequence Partition_date_key Attribute_CONCEPT Value Value_Type_CONCEPT Value_CONCEPT Date_Created Date_Modified Last_Modified_Load_Audit_uid Load_Batch_Info Event_Substance Event_GUID Partition_date_key Subject_GUID Child_Flag Parent_GUID Sequence Event_Name Event_Name_CONCEPT Event_ID Provider Primary_Date_Time Appl_Source_CD Date_Created Date_Modified Load_Batch_Info Last_Modified_Load_Audit_uid Status Status_Concept Domain_Concept Event_Note Event_Substance_EAV Entity_GUID Sequence Partition_date_key Attribute_CONCEPT Value Value_Type_CONCEPT Value_CONCEPT Date_created Date_Modified Last_Modified_Load_Audit_uid Load_Batch_Info Investigator Investigator_uid Last_name First_name Middle_initial Display_name Suffix NED_id Degree Institute_acronym Institute_acronym_CONCEPT Branch_abb Branch_abb_CONCEPT Professional_designation Office_address Office_phone Appl_Source_CD Date_created Date_modified Last_Modified_Load_Audit_uid NIH_Login Load_the_record Original_Investigator_UID Investigator_Role Investigator_uid Protocol_uid RoleId Access_level Start_date End_date Appl_Source_CD Date_created Date_modified Last_Modified_Load_Audit_uid Investigator_Role_Lookup Investigator_RoleId RoleName NCI_LabmatrixQueries ID QueryName QueryId Mode FileLocation URL Type IsActive NCI_Labmatrix_Query_Data ID Key Value Record_ID fieldName Event_Guid Value_Type_Concept Value_Concept Attribute_Concept Partition_Date_Key SetName fieldName_SetName NIAAA_Identification_Key NIK_ID TABLE_Name FORM_Name VARIABLE DESCRIPTION UNIT_OF_MEASUREMENT KEY_TO_VALUES Key Value OHSR_Report_Metadata Report_Id OHSR_User_Name OHSR_User_Email Data_Download_Date Report_Location OHSR_FName OHSR_LName OHSR_Phone OHSR_Room OHSR_StreetAddress OHSR_City OHSR_State OHSR_Zip OHSR_Institute OHSR_Branch OHSR_Description OHSR_Title OHSR_Certificate_Number OHSR_Certificate_Location OHSR_Certificate_Emailed Processed Report_Key Download_Count Download_Date Report_Path Observation_General Observation_GUID Partition_date_key Subject_GUID Child_Flag Parent_GUID Event_GUID Sequence Observation_Name Observation_Name_CONCEPT Observation_Value_Text Observation_Value_Numeric Observation_Value_CONCEPT Observation_Value_Name Unit_of_Measure Primary_Date_Time Appl_Source_CD Date_Created Date_Modified Last_Modified_Load_Audit_uid Load_Batch_Info Observation_Note Status Status_Concept Domain_Concept Observation_General_EAV Entity_GUID Sequence Partition_date_key Attribute_CONCEPT Value Value_Type_CONCEPT Value_CONCEPT Date_Created Date_Modified Last_Modified_Load_Audit_uid Load_Batch_Info Observation_Measurable Observation_GUID Partition_date_key Subject_GUID Child_Flag Parent_GUID Event_GUID Sequence Observation_Name Observation_Name_CONCEPT Observation_Value_Text Observation_Value_Numeric Observation_Value_CONCEPT Observation_Value_Name Unit_of_Measure Range Primary_Date_Time Appl_Source_CD Date_Created Date_Modified Last_Modified_Load_Audit_uid Load_Batch_Info DeIdentified_Flag Observation_Note Blinded_Flag Status Status_Concept Domain_Concept Observation_Measurable_EAV Entity_GUID Sequence Partition_date_key Attribute_CONCEPT Value Value_Type_CONCEPT Value_CONCEPT Date_Created Date_Modified Last_Modified_Load_Audit_uid Load_Batch_Info Observation_Substance Observation_GUID Partition_date_key Subject_GUID Child_Flag Parent_GUID Event_GUID Sequence Observation_Name Observation_Name_CONCEPT Observation_Value_Text Observation_Value_Numeric Observation_Value_CONCEPT Observation_Value_Name Unit_of_Measure Route Primary_Date_Time Appl_Source_CD Date_created Date_Modified Last_Modified_Load_Audit_uid Load_Batch_Info Observation_Note Status Status_Concept Domain_Concept Observation_Substance_EAV Entity_GUID Sequence Partition_date_key Attribute_CONCEPT Value Value_Type_CONCEPT Value_CONCEPT Date_created Date_Modified Last_Modified_Load_Audit_uid Load_Batch_Info Protocol Protocol_uid Protocol_number Protocol_title Abbreviated_title IRB_institute Is_Terminated IRB_institute_CONCEPT Accrual_institute Accrual_institute_CONCEPT Accrual_branch Accrual_branch_CONCEPT Principal_investigator_uid Medical_advisory_investigator_uid Research_contact_uid Protocol_coordinator_uid Web_contact_uid Precis Research_type Research_type_CONCEPT Research_phase Research_phase_CONCEPT Analysis_sex_gender Conflict_of_interest_two Studying_serious_condition Multi_site_collaboration Subjects_participating_at_NIH_CC Subjects_participating_other_sites Collaborative_site_type Collaborative_site_type_CONCEPT NIH_coordinating_site Coordinating_site Location_of_study Location_of_study_CONCEPT Supporting_site Study_type I_purpose I_purpose_CONCEPT I_purpose_other_text I_study_design I_study_design_CONCEPT I_masking I_masking_CONCEPT I_masking_subject I_masking_caregiver I_masking_investigator I_masking_outcomes_assessor I_control I_control_CONCEPT I_intervention_model I_intervention_model_CONCEPT I_study_classification I_study_classification_CONCEPT I_primary_time_fame I_primary_safety_issue I_secondary_time_frame I_secondary_safety_issue I_primary_measure I_secondary_measure O_purpose O_purpose_CONCEPT O_duration_of_sampling O_duration_of_sampling_CONCEPT O_selection_method O_selection_method_CONCEPT O_timing O_timing_CONCEPT Primary_outcome Secondary_outcome Accrual_status Accrual_status_CONCEPT Accrual_status_start Accrual_status_end IRB_accrual_cieling_NIH Accrued_since_last_review_NIH Cumulative_accrued_NIH IRB_accrual_ceiling_other Accrued_since_last_review_other Cumulative_accrued_other IRB_accrual_ceiling_total Accrued_since_last_review_total Cumulative_accrued_total Start_date_of_study End_date_of_study Encounter_type Encounter_type_CONCEPT Gender Gender_CONCEPT Healthy_volunteers Healthy_volunteers_currently_recruiting NIH_employees Protocol_permits_self_referral Adults_informed_consent Tech_transfer_agreement Lay_language Lay_language_update_date Brief_title Summary Study_minimum_age Study_maximum_age Eligibility Special_instructions Links FDA_regulated_intervention Section_801_clinical_trial Delayed_posting Commercial_entities Commercial_entities_CONCEPT Subject_to_FDA_regs_IND_IDE Ind_responsible_party Radiation_usage Radiation_usage_CONCEPT Radiation_safety_due_date Rsc_rad_auth_no Special_provisions Adjunct_pi_uid Lead_ai_uid Termination_data_termination_date Termination_data_ops_receipt_date Termination_data_ops_completion_date Termination_data_date_IRB_meet Termination_data_terminated_due_to_difficulty_recruiting Appl_Source_CD Date_created Date_modified Last_Modified_Load_Audit_uid Protrak_number Ops_Completion_Date Protocol_type Data_Reuse_Protocol Protocol_Subject Protocol_uid Subject_id Protocol_number Status Start_date Resolved_date Appl_Source_CD Date_created Date_modified Last_Modified_Load_Audit_uid Treatment_Arm Adverse_Event Outcome Attribution_Status Attribution_Precedence Protocol_Subject_Hist Protocol_uid Subject_id Sequence_Num Protocol_number Status Treatment_Arm Adverse_Event Outcome Attribution_Status Attribution_Precedence Start_date Resolved_date Appl_Source_CD Date_created Date_modified Last_Modified_Load_Audit_uid RED_Ancestor_Descendant_Identity Ancestor_Concept_GID Descendant_Concept_GID Ancestor_Concept Ancestor_Preferred_name Descendant_Concept Descendant_Preferred_name RED_Ancestor_Descendant_Identity_Data Ancestor_Concept_GID Descendant_Concept_GID Ancestor_Concept Ancestor_Preferred_name Descendant_Concept Descendant_Preferred_name RED_Concept_Path path_id code00 name00 code01 name01 code02 name02 code03 name03 code04 name04 code05 name05 code06 name06 code07 name07 code08 name08 code09 name09 code10 name10 code11 name11 code12 name12 code13 name13 code14 name14 code15 name15 code16 name16 code17 name17 code18 name18 code19 name19 code20 name20 code21 name21 code22 name22 code23 name23 code24 name24 code25 name25 code name synonyms RED_Concepts Concept_id Concept_code Concept_name Concept_kind Date_created RED_version RED_Displayable_AncestorDescendant Ancestor_Concept_GID Descendant_Concept_GID RED_Lookup RCL_ID Data_Source Domain Local_Code Concept_Code Concept_Name Concept_GID Concept_Property_ID Concept_Property_Type Extra_Code RED_Properties Concept_code Property_id Property_type Property_name Source_system Date_created RED_version RED_Relation Parent_Concept Child_Concept Parent_Preferred_name Child_Preferred_name RED_SearchDomain RED_Code Domain Short_Name RED_SearchTable RowID Concept_GID Concept_Code Search_Value Preferred_Name Display_Class Record_Count SYN Status_Lookup Value Description Subject UID MRN Encrypted_MRN First_name Encrypted_first_name Last_name Encrypted_last_name Middle_name Encrypted_middle_name Display_name Encrypted_display_name Religion Religion_CONCEPT Race Race_CONCEPT Gender Gender_CONCEPT Marital_status Marital_status_CONCEPT Language Language_CONCEPT Occupation Occupation_CONCEPT Ethnic_group Ethnic_group_CONCEPT Address_line1 Encrypted_address_line1 Address_line2 Encrypted_address_line2 Country_division_code City Encrypted_city Zip Encrypted_zip Country_code Phone_number Encrypted_phone_number Area_code Encrypted_area_code Birth_city Encrypted_birth_city Birth_country Birth_country_division_code Death_indicator Date_of_birth Encrypted_date_of_birth Date_of_death Encrypted_date_of_death APPL_Source_CD Date_created Date_modified External_UID Subject_ETL_ID Token Last_Modified_Load_Audit_uid Status Blood_Type Rh_Factor Is_VIP Death_Confidence Death_Source_CONCEPT Subject_Exceptions ID Subject_UID Last_name First_name Status Subject_Hist Subject_id Report_date MRN Encrypted_MRN First_name Encrypted_first_name Last_name Encrypted_last_name Middle_name Encrypted_middle_name Display_name Encrypted_display_name Religion Religion_CONCEPT Race Race_CONCEPT Gender Gender_CONCEPT Marital_status Marital_status_CONCEPT Language Language_CONCEPT Occupation Occupation_CONCEPT Ethnic_group Ethnic_group_CONCEPT Address_line1 Encrypted_address_line1 Address_line2 Encrypted_address_line2 Country_division_code City Encrypted_city Zip Encrypted_zip Country_code Phone_number Encrypted_phone_number Area_code Encrypted_area_code Birth_city Encrypted_birth_city Birth_country Birth_country_division_code Death_indicator Date_of_birth Encrypted_date_of_birth Date_of_death Encrypted_date_of_death APPL_Source_CD Date_created Date_modified External_UID Subject_ETL_ID Token Last_Modified_Load_Audit_uid Blood_Type Rh_Factor Status Death_Confidence Death_Source_CONCEPT Is_VIP Subject_IDs Row_ID Subject_UID Alternate_ID Assigning_Authority ID_Type ID_Status Start_Date End_Date Date_created Date_modified Last_Modified_Load_Audit_uid Subject_IDs_Hist Row_ID Report_Date Subject_UID Alternate_ID Assigning_Authority ID_Type ID_Status Start_Date End_Date Date_created Date_modified Last_Modified_Load_Audit_uid Investigator records are loaded into the BTRIS_Investigator table, if they have correct data , they are then loaded into the Investigator table. Records with missing data generate a notification that is loaded into the BTRIS_Notificiation table. BTRIS Production Database Project btris-prod-mod Layout dbo build using DbSchema

Table BTRIS_Applications
ApplicationId uniqueidentifier NOT NULL DEFO newid()
ApplicationName nvarchar( 256 ) NOT NULL
LoweredApplicationName nvarchar( 256 ) NOT NULL
Description nvarchar( 256 )
Indexes
PK_BTRIS_App_ApplicationId primary key ( ApplicationId)
UQ_BTRIS_App_ApplicationName unique ( ApplicationName)
UQ_BTRIS_App_LoweredApplicationName unique ( LoweredApplicationName)


Table BTRIS_Investigator
Investigator_uid bigint NOT NULL AUTOINCREMENT
Last_name varchar( 40 ) NOT NULL
First_name varchar( 40 )
Middle_initial varchar( 40 )
Display_name varchar( 80 )
Suffix varchar( 10 )
NIH_Login varchar( 50 )
NED_id varchar( 20 )
Degree varchar( 10 )
Institute_acronym varchar( 10 )
Institute_acronym_CONCEPT varchar( 20 )
Branch_abb varchar( 10 )
Branch_abb_CONCEPT varchar( 20 )
Professional_designation varchar( 20 )
Office_address varchar( 400 )
Office_phone varchar( 15 )
Building varchar( 20 )
Room varchar( 20 )
Quick_Address varchar( 20 )
Appl_Source_CD varchar( 30 ) NOT NULL
Date_Created datetime NOT NULL
Date_Modified datetime
Last_Modified_Load_Audit_uid int
Protocol_number varchar( 20 )
Role varchar( 50 )
Use bit
Indexes
PK_BTRIS_Investigator_uid primary key ( Investigator_uid)


Table BTRIS_Notification
Notification_ID int NOT NULL AUTOINCREMENT
Table_Name varchar( 50 )
Data_Source varchar( 30 )
Domain varchar( 30 )
Message varchar( 8000 )
Notification_Row varchar( 8000 )
Date_Created datetime NOT NULL
Indexes
PK_Notification_ID primary key ( Notification_ID)


Table BTRIS_Roles
RoleId uniqueidentifier NOT NULL DEFO newid()
ApplicationId uniqueidentifier NOT NULL
RoleName nvarchar( 256 ) NOT NULL
LoweredRoleName nvarchar( 256 ) NOT NULL
Description nvarchar( 256 )
Investigator_RoleId tinyint NOT NULL
Indexes
PK_BTRIS_Roles_RoleId primary key ( RoleId)
IDX_BTRIS_Roles_Investigator_RoleId ( Investigator_RoleId)
Foreign Keys
FK_BTRIS_Roles_Applications_ApplicationId ( ApplicationId ) ref BTRIS_Applications ( ApplicationId )
FK_BTRIS_Roles_Investigator_Role_Lookup_Investigator_RoleId ( Investigator_RoleId ) ref Investigator_Role_Lookup ( Investigator_RoleId )


Table BTRIS_Users
UserId uniqueidentifier NOT NULL DEFO newid()
ApplicationId uniqueidentifier NOT NULL
Investigator_uid bigint
UserName nvarchar( 256 ) NOT NULL
LoweredUserName nvarchar( 256 ) NOT NULL
MobileAlias nvarchar( 16 )
IsAnonymous bit NOT NULL DEFO 0
LastActivityDate datetime NOT NULL
Hide_Not_On_Protocol bit
DataAccessStatus varchar( 30 )
Indexes
PK_BTRIS_Users_UserId primary key ( UserId)
_dta_index_BTRIS_Users_c_11_1787153412__K2_K3 ( UserId, Investigator_uid)
Foreign Keys
FK_BTRIS_Users_App_ApplicationId ( ApplicationId ) ref BTRIS_Applications ( ApplicationId )
FK_BTRIS_Users_Investigator_uid ( Investigator_uid ) ref Investigator ( Investigator_uid )


Table BTRIS_UsersInRoles
RoleId uniqueidentifier NOT NULL
UserId uniqueidentifier NOT NULL
Protocol_uid bigint NOT NULL
Indexes
PK_BTRIS_UsersInRoles_UserID_RoleID_ProtocolUID primary key ( RoleId, UserId, Protocol_uid)
_dta_index_BTRIS_UsersInRoles_11_1899153811__K3 ( Protocol_uid)
Foreign Keys
FK_BTRIS_UsersInRoles_RoleId ( RoleId ) ref BTRIS_Roles ( RoleId )
FK_BTRIS_UsersInRoles_UserId ( UserId ) ref BTRIS_Users ( UserId )


Table Biomaterials
UID bigint NOT NULL
Partition_Date_Key int NOT NULL
MRN varchar( 30 )
Source_Subject_ID int
Source_Biomaterial_ID int
Class varchar( 30 )
Class_CONCEPT varchar( 20 )
Type varchar( 50 )
Type_CONCEPT varchar( 20 )
Source_Name varchar( 100 )
Parent_Biomaterial_ID int
Specimen_Quality varchar( 50 )
Specimen_Quality_CONCEPT varchar( 20 )
Current_Status varchar( 50 )
Current_Status_CONCEPT varchar( 20 )
Current_Status_Date datetime
Notes varchar
Specimen_Date_Created datetime
Specimen_Date_Received datetime
Subject_Code varchar( 50 )
External_ID uniqueidentifier
Record_Create_Date datetime
Record_Modified_Date datetime
Appl_Source_CD varchar( 30 ) NOT NULL
Date_Created datetime NOT NULL
Date_Modified datetime
Last_Modified_Load_Audit_UID int
Pathological_Status varchar( 50 )
Pathological_Status_CONCEPT varchar( 20 )
Load_Batch_Info varchar( 30 )
Indexes
PK_Biomaterials_UID_Partition_Date_Key primary key ( UID, Partition_Date_Key)


Table Biomaterials_EAV
Entity_GUID bigint NOT NULL
Sequence int NOT NULL
Partition_date_key int NOT NULL
Attribute_CONCEPT varchar( 30 ) NOT NULL
Value varchar NOT NULL
Value_Type_CONCEPT varchar( 30 ) NOT NULL
Value_CONCEPT varchar( 30 )
Date_Created datetime NOT NULL
Date_Modified datetime
Last_Modified_Load_Audit_uid int
Load_Batch_Info varchar( 30 )
Indexes
PK_Biomaterials_EAV_Entity_GUID_Sequence_Partition_Date primary key ( Entity_GUID, Sequence, Partition_date_key)
Foreign Keys
FK_Biomaterials_EAV_Biomaterials_GUID ( Entity_GUID , Partition_date_key ) ref Biomaterials ( UID , Partition_Date_Key )


Table CRISUsers
FSSUserID varchar( 30 ) NOT NULL
Indexes
PK_CRISUsers_FSSUserID primary key ( FSSUserID)


Table Concept_Search_String
Search_String varchar( 8000 )
Search_String_Source varchar( 300 )
Concept_GID bigint
Concept_Code varchar( 20 )
Indexes
IDX_ConceptSearchString_SearchStringConceptCode ( Search_String)


Table Event_General
Event_GUID bigint NOT NULL
Partition_date_key int NOT NULL DEFO 0
Subject_GUID bigint NOT NULL
Child_Flag bit
Parent_GUID bigint
Sequence int
Event_Name varchar
Event_Name_CONCEPT varchar( 30 ) NOT NULL
Event_ID varchar( 30 )
Provider varchar( 200 )
Primary_Date_Time datetime
Appl_Source_CD varchar( 30 ) NOT NULL
Date_Created datetime NOT NULL
Date_Modified datetime
Load_Batch_Info varchar( 30 )
Last_Modified_Load_Audit_uid int
Status varchar( 100 )
Status_Concept varchar( 30 )
Domain_Concept varchar( 30 )
Event_Note varchar
Indexes
PK_EventGeneral_EventGUID_PartitionDatekey primary key ( Event_GUID, Partition_date_key)
_dta_index_Event_General_11_990626572__K7_K11_K1_K16_6 ( Event_Name_CONCEPT, Appl_Source_CD, Event_GUID, Partition_date_key)
_dta_index_Event_General_11_990626572__K7_K11_K2_K1_K16_6 ( Event_Name_CONCEPT, Appl_Source_CD, Subject_GUID, Event_GUID, Partition_date_key)
IDX_EventGeneral_ApplSourceCD ( Appl_Source_CD)
IDX_EventGeneral_EventNameCONCEPT ( Event_Name_CONCEPT)
IDX_EventGeneral_LastModifiedLoadAuditUid ( Last_Modified_Load_Audit_uid)
IDX_EventGeneral_PrimaryDateTime ( Primary_Date_Time)
IDX_EventGeneral_SubjectGUID ( Subject_GUID)
Foreign Keys
FK_Event_General_Subject ( Subject_GUID ) ref Subject ( UID )


Table Event_General_EAV
Entity_GUID bigint NOT NULL
Sequence int NOT NULL
Partition_date_key int NOT NULL DEFO 0
Attribute_CONCEPT varchar( 30 ) NOT NULL
Value varchar NOT NULL
Value_Type_CONCEPT varchar( 30 ) NOT NULL
Value_CONCEPT varchar( 30 )
Date_created datetime NOT NULL
Date_Modified datetime
Last_Modified_Load_Audit_uid int
Load_Batch_Info varchar( 30 )
Indexes
PK_EventGeneralEAV_EntityGUID_Sequence_PartitionDateKey primary key ( Entity_GUID, Sequence, Partition_date_key)
IDX_EventGeneralEAV_AttributeCONCEPT ( Attribute_CONCEPT)
IDX_EventGeneralEAV_LastModifiedLoadAuditUid ( Last_Modified_Load_Audit_uid)
Foreign Keys
FK_EventGeneralEAV_EntityGUID_PartitionDateKey ( Entity_GUID , Partition_date_key ) ref Event_General ( Event_GUID , Partition_date_key )


Table Event_Measurable
Event_GUID bigint NOT NULL
Partition_date_key int NOT NULL DEFO 0
Subject_GUID bigint NOT NULL
Child_Flag bit
Parent_GUID bigint
Sequence int
Event_Name varchar
Event_Name_CONCEPT varchar( 30 ) NOT NULL
Event_ID varchar( 30 )
Provider varchar( 200 )
Primary_Date_Time datetime
Appl_Source_CD varchar( 30 ) NOT NULL
Date_Created datetime NOT NULL
Date_Modified datetime
Load_Batch_Info varchar( 30 )
Last_Modified_Load_Audit_uid int
DeIdentified_Flag bit DEFO 0
Blinded_Flag bit DEFO 0
Status varchar( 100 )
Status_Concept varchar( 30 )
Domain_Concept varchar( 30 )
Event_Note varchar
Indexes
PK_EventMeasurable_EventGUID_PartitionDatekey primary key ( Event_GUID, Partition_date_key)
_dta_index_Event_Measurable_11_1054626800__K1_K2_6 ( Event_GUID, Subject_GUID)
_dta_index_Event_Measurable_11_1054626800__K11_K2 ( Appl_Source_CD, Subject_GUID)
_dta_index_Event_Measurable_11_1054626800__K2_K18_K1_K16_6 ( Subject_GUID, Blinded_Flag, Event_GUID, Partition_date_key)
IDX_EventMeasurable_ApplSourceCD ( Appl_Source_CD)
IDX_EventMeasurable_EventNameCONCEPT ( Event_Name_CONCEPT)
IDX_EventMeasurable_LastModifiedLoadAuditUid ( Last_Modified_Load_Audit_uid)
IDX_EventMeasurable_PrimaryDateTime ( Primary_Date_Time)
IDX_EventMeasurable_SubjectGUID_EventNameConcept_EventGUID ( Subject_GUID, Event_Name_CONCEPT, Event_GUID)
Foreign Keys
FK_Event_Measurable_Subject ( Subject_GUID ) ref Subject ( UID )


Table Event_Measurable_EAV
Entity_GUID bigint NOT NULL
Sequence int NOT NULL
Partition_date_key int NOT NULL DEFO 0
Attribute_CONCEPT varchar( 30 ) NOT NULL
Value varchar NOT NULL
Value_Type_CONCEPT varchar( 30 ) NOT NULL
Value_CONCEPT varchar( 30 )
Date_Created datetime NOT NULL
Date_Modified datetime
Last_Modified_Load_Audit_uid int
Load_Batch_Info varchar( 30 )
Indexes
PK_EventMeasurableEAV_EntityGUID_Sequence_PartitionDatekey primary key ( Entity_GUID, Sequence, Partition_date_key)
_dta_index_Event_Measurable_EAV_11_1246627484__K1 ( Entity_GUID)
IDX_EventMeasurableEAV_AttributeCONCEPT ( Attribute_CONCEPT)
IDX_EventMeasurableEAV_LastModifiedLoadAuditUid ( Last_Modified_Load_Audit_uid)
Foreign Keys
FK_Event_MeasurableEAV_EntityGUID_PartitionDateKey ( Entity_GUID , Partition_date_key ) ref Event_Measurable ( Event_GUID , Partition_date_key )


Table Event_Substance
Event_GUID bigint NOT NULL
Partition_date_key int NOT NULL DEFO 0
Subject_GUID bigint NOT NULL
Child_Flag bit
Parent_GUID bigint
Sequence int
Event_Name varchar
Event_Name_CONCEPT varchar( 30 ) NOT NULL
Event_ID varchar( 30 )
Provider varchar( 200 )
Primary_Date_Time datetime
Appl_Source_CD varchar( 30 ) NOT NULL
Date_Created datetime NOT NULL
Date_Modified datetime
Load_Batch_Info varchar( 30 )
Last_Modified_Load_Audit_uid int
Status varchar( 100 )
Status_Concept varchar( 30 )
Domain_Concept varchar( 30 )
Event_Note varchar
Indexes
PK_EventSubstance_EventGUID_PartitionDatekey primary key ( Event_GUID, Partition_date_key)
IDX_EventSubstance_ApplSourceCD ( Appl_Source_CD)
IDX_EventSubstance_EventNameConcept ( Event_Name_CONCEPT)
IDX_EventSubstance_LastModifiedLoadAuditUid ( Last_Modified_Load_Audit_uid)
IDX_EventSubstance_PrimaryDateTime ( Primary_Date_Time)
IDX_EventSubstance_SubjectGUID ( Subject_GUID)
Foreign Keys
FK_Event_Substance_Subject ( Subject_GUID ) ref Subject ( UID )


Table Event_Substance_EAV
Entity_GUID bigint NOT NULL
Sequence int NOT NULL
Partition_date_key int NOT NULL DEFO 0
Attribute_CONCEPT varchar( 30 ) NOT NULL
Value varchar NOT NULL
Value_Type_CONCEPT varchar( 30 ) NOT NULL
Value_CONCEPT varchar( 30 )
Date_created datetime NOT NULL
Date_Modified datetime
Last_Modified_Load_Audit_uid int
Load_Batch_Info varchar( 30 )
Indexes
PK_EventSubstanceEAV_EntityGUID_Sequence_PartitionDateKey primary key ( Entity_GUID, Sequence, Partition_date_key)
IDX_EventSubstanceEAV_AttributeCONCEPT ( Attribute_CONCEPT)
IDX_EventSubstanceEAV_LastModifiedLoadAuditUid ( Last_Modified_Load_Audit_uid)
Foreign Keys
FK_EventSubstanceEAV_EntityGUID_PartitionDateKey ( Entity_GUID , Partition_date_key ) ref Event_Substance ( Event_GUID , Partition_date_key )


Table Investigator
Investigator_uid bigint NOT NULL AUTOINCREMENT
Last_name varchar( 40 ) NOT NULL
First_name varchar( 40 )
Middle_initial varchar( 40 )
Display_name varchar( 80 )
Suffix varchar( 10 )
NED_id varchar( 20 )
Degree varchar( 10 )
Institute_acronym varchar( 10 )
Institute_acronym_CONCEPT varchar( 20 )
Branch_abb varchar( 10 )
Branch_abb_CONCEPT varchar( 20 )
Professional_designation varchar( 20 )
Office_address varchar( 400 )
Office_phone varchar( 15 )
Appl_Source_CD varchar( 30 ) NOT NULL
Date_created datetime NOT NULL
Date_modified datetime
Last_Modified_Load_Audit_uid int
NIH_Login varchar( 50 )
Load_the_record bit DEFO 1
Original_Investigator_UID bigint
Indexes
PK_Investigator_uid primary key ( Investigator_uid)
IDX_Investigator_NihLogin ( NIH_Login)


Table Investigator_Role
Investigator_uid bigint NOT NULL
Protocol_uid bigint NOT NULL
RoleId tinyint NOT NULL
Access_level varchar( 20 )
Start_date datetime
End_date datetime
Appl_Source_CD varchar( 30 ) NOT NULL
Date_created datetime NOT NULL
Date_modified datetime
Last_Modified_Load_Audit_uid int
Indexes
PK_Investigator_Role primary key ( Investigator_uid, Protocol_uid, RoleId)
_dta_index_Investigator_Role_ProtocolUid ( Protocol_uid)
Foreign Keys
FK_Investigator_Role_Investigator_uid ( Investigator_uid ) ref Investigator ( Investigator_uid )
FK_Investigator_Role_Investigator_Role_Lookup_RoleId ( RoleId ) ref Investigator_Role_Lookup ( Investigator_RoleId )
FK_Investigator_Role_Protocol_uid ( Protocol_uid ) ref Protocol ( Protocol_uid )


Table Investigator_Role_Lookup
Investigator_RoleId tinyint NOT NULL
RoleName varchar( 256 ) NOT NULL
Indexes
PK_Investigator_Role_Lookup primary key ( Investigator_RoleId)


Table NCI_LabmatrixQueries
ID uniqueidentifier NOT NULL
QueryName varchar( 150 ) NOT NULL
QueryId smallint NOT NULL
Mode varchar( 10 ) NOT NULL
FileLocation varchar( 300 )
URL varchar( 300 ) NOT NULL
Type varchar( 20 ) NOT NULL
IsActive bit NOT NULL
Indexes
PK_NCI_LabmatrixQueries primary key ( ID)


Table NCI_Labmatrix_Query_Data
ID int NOT NULL AUTOINCREMENT
Key varchar( 500 )
Value varchar( 1000 )
Record_ID bigint
fieldName varchar( 100 )
Event_Guid bigint
Value_Type_Concept varchar( 30 )
Value_Concept varchar( 30 )
Attribute_Concept varchar( 30 )
Partition_Date_Key int
SetName varchar( 255 )
fieldName_SetName varchar( 255 )


Table NIAAA_Identification_Key
NIK_ID int NOT NULL
TABLE_Name nvarchar( 255 )
FORM_Name nvarchar( 255 )
VARIABLE nvarchar( 255 )
DESCRIPTION nvarchar( 1073741823 )
UNIT_OF_MEASUREMENT nvarchar( 255 )
KEY_TO_VALUES nvarchar( 255 )
Key varchar( 255 )
Value varchar( 2000 )


Table OHSR_Report_Metadata
Report_Id bigint NOT NULL AUTOINCREMENT
OHSR_User_Name varchar( 50 ) NOT NULL
OHSR_User_Email varchar( 50 )
Data_Download_Date datetime NOT NULL
Report_Location varchar( 250 ) NOT NULL
OHSR_FName varchar( 250 )
OHSR_LName varchar( 250 ) NOT NULL
OHSR_Phone varchar( 50 )
OHSR_Room varchar( 50 )
OHSR_StreetAddress varchar( 150 )
OHSR_City varchar( 50 )
OHSR_State varchar( 50 )
OHSR_Zip varchar( 50 )
OHSR_Institute varchar( 50 ) NOT NULL
OHSR_Branch varchar( 50 ) NOT NULL
OHSR_Description varchar
OHSR_Title varchar( 250 )
OHSR_Certificate_Number varchar( 323 )
OHSR_Certificate_Location varchar( 250 )
OHSR_Certificate_Emailed bit NOT NULL DEFO 0
Processed bit NOT NULL DEFO 0
Report_Key varchar( 250 )
Download_Count int
Download_Date datetime
Report_Path varchar( 250 )
Indexes
PK_OHSR_Report_Metadata primary key ( Report_Id)


Table Observation_General
Observation_GUID bigint NOT NULL
Partition_date_key int NOT NULL DEFO 0
Subject_GUID bigint NOT NULL
Child_Flag bit
Parent_GUID bigint
Event_GUID bigint
Sequence int
Observation_Name varchar NOT NULL
Observation_Name_CONCEPT varchar( 30 ) NOT NULL
Observation_Value_Text varchar
Observation_Value_Numeric float
Observation_Value_CONCEPT varchar( 30 )
Observation_Value_Name varchar
Unit_of_Measure varchar( 20 )
Primary_Date_Time datetime
Appl_Source_CD varchar( 30 ) NOT NULL
Date_Created datetime NOT NULL
Date_Modified datetime
Last_Modified_Load_Audit_uid int
Load_Batch_Info varchar( 30 )
Observation_Note varchar
Status varchar( 100 )
Status_Concept varchar( 30 )
Domain_Concept varchar( 30 )
Indexes
PK_ObservationGeneral_ObservationGUID_PartitionDateKey primary key ( Observation_GUID, Partition_date_key)
_dta_index_Observation_General_11_1454628225__K15_K5_K1_K20_7_9_14_21 ( Appl_Source_CD, Event_GUID, Observation_GUID, Partition_date_key)
_dta_index_Observation_General_11_1454628225__K2_K1_K20_K15_K8_K14_7_9 ( Subject_GUID, Observation_GUID, Partition_date_key, Appl_Source_CD, Observation_Name_CONCEPT, Primary_Date_Time)
IDX_ObservationGeneral_ApplSourceCD ( Appl_Source_CD)
IDX_ObservationGeneral_EventGUID_SubjectGUID ( Event_GUID, Subject_GUID)
IDX_ObservationGeneral_LastModifiedLoadAuditUid ( Last_Modified_Load_Audit_uid)
IDX_ObservationGeneral_ObservationNameCONCEPT ( Observation_Name_CONCEPT)
IDX_ObservationGeneral_ObservationValueCONCEPT ( Observation_Value_CONCEPT)
IDX_ObservationGeneral_PrimaryDateTime ( Primary_Date_Time)
IDX_ObservationGeneral_Status ( Domain_Concept)
IDX_ObservationGeneral_Subject_GUID ( Subject_GUID)
Foreign Keys
FK_Observation_General_Event_General ( Event_GUID , Partition_date_key ) ref Event_General ( Event_GUID , Partition_date_key )
FK_Observation_General_Subject ( Subject_GUID ) ref Subject ( UID )


Table Observation_General_EAV
Entity_GUID bigint NOT NULL
Sequence int NOT NULL
Partition_date_key int NOT NULL DEFO 0
Attribute_CONCEPT varchar( 30 ) NOT NULL
Value varchar NOT NULL
Value_Type_CONCEPT varchar( 30 ) NOT NULL
Value_CONCEPT varchar( 30 )
Date_Created datetime NOT NULL
Date_Modified datetime
Last_Modified_Load_Audit_uid int
Load_Batch_Info varchar( 30 )
Indexes
PK_Observation_General_EAV_Entity_GUID_Sequence_Partition_Date primary key ( Entity_GUID, Sequence, Partition_date_key)
IDX_ObservationGeneralEAV_AttributeCONCEPT ( Attribute_CONCEPT)
IDX_ObservationGeneralEAV_LastModifiedLoadAuditUid ( Last_Modified_Load_Audit_uid)
Foreign Keys
FK_Observation_General_EAV_Observation_General_GUID ( Entity_GUID , Partition_date_key ) ref Observation_General ( Observation_GUID , Partition_date_key )


Table Observation_Measurable
Observation_GUID bigint NOT NULL
Partition_date_key int NOT NULL DEFO 0
Subject_GUID bigint NOT NULL
Child_Flag bit
Parent_GUID bigint
Event_GUID bigint
Sequence int
Observation_Name varchar NOT NULL
Observation_Name_CONCEPT varchar( 30 ) NOT NULL
Observation_Value_Text varchar
Observation_Value_Numeric float
Observation_Value_CONCEPT varchar( 30 )
Observation_Value_Name varchar
Unit_of_Measure varchar( 20 )
Range varchar( 200 )
Primary_Date_Time datetime
Appl_Source_CD varchar( 30 ) NOT NULL
Date_Created datetime NOT NULL
Date_Modified datetime
Last_Modified_Load_Audit_uid int
Load_Batch_Info varchar( 30 )
DeIdentified_Flag bit DEFO 0
Observation_Note varchar
Blinded_Flag bit DEFO 0
Status varchar( 100 )
Status_Concept varchar( 30 )
Domain_Concept varchar( 30 )
Indexes
PK_ObservationMeasurable_ObservationGUID_PartitionDateKey primary key ( Observation_GUID, Partition_date_key)
_dta_index_Observation_Measurable_11_1598628738__K16_K2 ( Appl_Source_CD, Subject_GUID)
_dta_index_Observation_Measurable_11_1598628738__K2_K1_K14_K13_K15_K16_K8_K5_7_9_10_12_23 ( Subject_GUID, Observation_GUID, Range, Unit_of_Measure, Primary_Date_Time, Appl_Source_CD, Observation_Name_CONCEPT, Event_GUID)
_dta_index_Observation_Measurable_11_1598628738__K2_K1_K21 ( Subject_GUID, Observation_GUID, Partition_date_key)
_dta_index_Observation_Measurable_11_1598628738__K8_K21_K1_K5_2_7_10_13_15 ( Observation_Name_CONCEPT, Partition_date_key, Observation_GUID, Event_GUID)
IDX_ObservationMeasurable_ApplSourceCD ( Appl_Source_CD)
IDX_ObservationMeasurable_EventGUID ( Event_GUID)
IDX_ObservationMeasurable_EventGUID_ObservationGUID_ParentGUID ( Event_GUID, Observation_GUID, Parent_GUID)
IDX_ObservationMeasurable_LastModifiedLoadAuditUid ( Last_Modified_Load_Audit_uid)
IDX_ObservationMeasurable_ObservationNameConcept ( Observation_Name_CONCEPT)
IDX_ObservationMeasurable_PrimaryDateTime ( Primary_Date_Time)
Foreign Keys
FK_Observation_Measurable_Event_Measurable ( Event_GUID , Partition_date_key ) ref Event_Measurable ( Event_GUID , Partition_date_key )
FK_Observation_Measurable_Subject ( Subject_GUID ) ref Subject ( UID )


Table Observation_Measurable_EAV
Entity_GUID bigint NOT NULL
Sequence int NOT NULL
Partition_date_key int NOT NULL DEFO 0
Attribute_CONCEPT varchar( 30 ) NOT NULL
Value varchar NOT NULL
Value_Type_CONCEPT varchar( 30 ) NOT NULL
Value_CONCEPT varchar( 30 )
Date_Created datetime NOT NULL
Date_Modified datetime
Last_Modified_Load_Audit_uid int
Load_Batch_Info varchar( 30 )
Indexes
PK_Observation_Measurable_EAV_Entity_GUID_Sequence_Partition_Date primary key ( Entity_GUID, Sequence, Partition_date_key)
_dta_index_Observation_Measurable_EAV_11_1678629023__K1 ( Entity_GUID)
IDX_ObservationMeasurableEAV_AttributeCONCEPT ( Attribute_CONCEPT)
IDX_ObservationMeasurableEAV_LastModifiedLoadAuditUid ( Last_Modified_Load_Audit_uid)
IDX_ObservationMeasurableEAV_ValueCONCEPT ( Value_CONCEPT)
Foreign Keys
FK_Observation_Measurable_EAV_Observation_Measurable_GUID ( Entity_GUID , Partition_date_key ) ref Observation_Measurable ( Observation_GUID , Partition_date_key )


Table Observation_Substance
Observation_GUID bigint NOT NULL
Partition_date_key int NOT NULL DEFO 0
Subject_GUID bigint NOT NULL
Child_Flag bit
Parent_GUID bigint
Event_GUID bigint
Sequence int
Observation_Name varchar NOT NULL
Observation_Name_CONCEPT varchar( 30 ) NOT NULL
Observation_Value_Text varchar
Observation_Value_Numeric float
Observation_Value_CONCEPT varchar( 30 )
Observation_Value_Name varchar
Unit_of_Measure varchar( 20 )
Route varchar( 30 )
Primary_Date_Time datetime
Appl_Source_CD varchar( 30 ) NOT NULL
Date_created datetime NOT NULL
Date_Modified datetime
Last_Modified_Load_Audit_uid int
Load_Batch_Info varchar( 30 )
Observation_Note varchar
Status varchar( 100 )
Status_Concept varchar( 30 )
Domain_Concept varchar( 30 )
Indexes
PK_ObservationSubstance_ObservationGUID_PartitionDateKey primary key ( Observation_GUID, Partition_date_key)
IDX_ObservationSubstance_ApplSourceCD ( Appl_Source_CD)
IDX_ObservationSubstance_EventGUID_SubjectGUID ( Event_GUID, Subject_GUID)
IDX_ObservationSubstance_LastModifiedLoadAuditUid ( Last_Modified_Load_Audit_uid)
IDX_ObservationSubstance_ObservationNameConcept ( Observation_Name_CONCEPT)
IDX_ObservationSubstance_PrimaryDateTime ( Primary_Date_Time)
Foreign Keys
FK_Observation_Substance_Event_Substance ( Event_GUID , Partition_date_key ) ref Event_Substance ( Event_GUID , Partition_date_key )
FK_Observation_Substance_Subject ( Subject_GUID ) ref Subject ( UID )


Table Observation_Substance_EAV
Entity_GUID bigint NOT NULL
Sequence int NOT NULL
Partition_date_key int NOT NULL DEFO 0
Attribute_CONCEPT varchar( 30 ) NOT NULL
Value varchar NOT NULL
Value_Type_CONCEPT varchar( 30 ) NOT NULL
Value_CONCEPT varchar( 30 )
Date_created datetime NOT NULL
Date_Modified datetime
Last_Modified_Load_Audit_uid int
Load_Batch_Info varchar( 30 )
Indexes
PK_Observation_Substance_EAV_Entity_GUID_Sequence_Partition_Date primary key ( Entity_GUID, Sequence, Partition_date_key)
IDX_ObservationSubstanceEAV_AttributeCONCEPT ( Attribute_CONCEPT)
IDX_ObservationSubstanceEAV_LastModifiedLoadAuditUid ( Last_Modified_Load_Audit_uid)
Foreign Keys
FK_Observation_Substance_EAV_Observation_Substance_GUID ( Entity_GUID , Partition_date_key ) ref Observation_Substance ( Observation_GUID , Partition_date_key )


Table Protocol
Protocol_uid bigint NOT NULL AUTOINCREMENT
Protocol_number varchar( 20 ) NOT NULL
Protocol_title varchar
Abbreviated_title varchar( 40 )
IRB_institute varchar( 50 )
Is_Terminated bit NOT NULL
IRB_institute_CONCEPT varchar( 20 )
Accrual_institute varchar( 50 )
Accrual_institute_CONCEPT varchar( 20 )
Accrual_branch varchar( 10 )
Accrual_branch_CONCEPT varchar( 20 )
Principal_investigator_uid bigint
Medical_advisory_investigator_uid bigint
Research_contact_uid bigint
Protocol_coordinator_uid bigint
Web_contact_uid bigint
Precis varchar
Research_type varchar( 40 )
Research_type_CONCEPT varchar( 20 )
Research_phase varchar( 10 )
Research_phase_CONCEPT varchar( 20 )
Analysis_sex_gender varchar( 10 )
Conflict_of_interest_two varchar( 10 )
Studying_serious_condition varchar( 10 )
Multi_site_collaboration varchar( 10 )
Subjects_participating_at_NIH_CC varchar( 10 )
Subjects_participating_other_sites varchar( 10 )
Collaborative_site_type varchar( 30 )
Collaborative_site_type_CONCEPT varchar( 20 )
NIH_coordinating_site varchar
Coordinating_site varchar
Location_of_study varchar( 20 )
Location_of_study_CONCEPT varchar( 20 )
Supporting_site varchar
Study_type varchar( 40 )
I_purpose varchar( 200 )
I_purpose_CONCEPT varchar( 20 )
I_purpose_other_text varchar( 200 )
I_study_design varchar( 200 )
I_study_design_CONCEPT varchar( 20 )
I_masking varchar( 200 )
I_masking_CONCEPT varchar( 20 )
I_masking_subject varchar( 10 )
I_masking_caregiver varchar( 10 )
I_masking_investigator varchar( 10 )
I_masking_outcomes_assessor varchar( 10 )
I_control varchar( 200 )
I_control_CONCEPT varchar( 20 )
I_intervention_model varchar( 20 )
I_intervention_model_CONCEPT varchar( 20 )
I_study_classification varchar( 40 )
I_study_classification_CONCEPT varchar( 20 )
I_primary_time_fame varchar
I_primary_safety_issue varchar( 10 )
I_secondary_time_frame varchar
I_secondary_safety_issue varchar( 10 )
I_primary_measure varchar
I_secondary_measure varchar
O_purpose varchar( 40 )
O_purpose_CONCEPT varchar( 20 )
O_duration_of_sampling varchar( 40 )
O_duration_of_sampling_CONCEPT varchar( 20 )
O_selection_method varchar( 40 )
O_selection_method_CONCEPT varchar( 20 )
O_timing varchar( 40 )
O_timing_CONCEPT varchar( 20 )
Primary_outcome varchar
Secondary_outcome varchar
Accrual_status varchar
Accrual_status_CONCEPT varchar( 20 )
Accrual_status_start datetime
Accrual_status_end datetime
IRB_accrual_cieling_NIH varchar( 10 )
Accrued_since_last_review_NIH varchar( 10 )
Cumulative_accrued_NIH varchar( 10 )
IRB_accrual_ceiling_other varchar( 10 )
Accrued_since_last_review_other varchar( 10 )
Cumulative_accrued_other varchar( 10 )
IRB_accrual_ceiling_total varchar( 10 )
Accrued_since_last_review_total varchar( 10 )
Cumulative_accrued_total varchar( 10 )
Start_date_of_study datetime
End_date_of_study datetime
Encounter_type varchar( 40 )
Encounter_type_CONCEPT varchar( 20 )
Gender varchar( 40 )
Gender_CONCEPT varchar( 20 )
Healthy_volunteers varchar( 10 )
Healthy_volunteers_currently_recruiting varchar( 10 )
NIH_employees varchar( 10 )
Protocol_permits_self_referral varchar( 10 )
Adults_informed_consent varchar( 10 )
Tech_transfer_agreement varchar
Lay_language varchar( 10 )
Lay_language_update_date varchar( 30 )
Brief_title varchar
Summary varchar
Study_minimum_age varchar( 10 )
Study_maximum_age varchar( 10 )
Eligibility varchar
Special_instructions varchar
Links varchar
FDA_regulated_intervention varchar( 10 )
Section_801_clinical_trial varchar( 10 )
Delayed_posting varchar( 10 )
Commercial_entities varchar
Commercial_entities_CONCEPT varchar( 20 )
Subject_to_FDA_regs_IND_IDE varchar( 10 )
Ind_responsible_party varchar
Radiation_usage varchar( 40 )
Radiation_usage_CONCEPT varchar( 20 )
Radiation_safety_due_date datetime
Rsc_rad_auth_no varchar( 20 )
Special_provisions varchar
Adjunct_pi_uid bigint
Lead_ai_uid bigint
Termination_data_termination_date datetime
Termination_data_ops_receipt_date datetime
Termination_data_ops_completion_date datetime
Termination_data_date_IRB_meet datetime
Termination_data_terminated_due_to_difficulty_recruiting varchar( 10 )
Appl_Source_CD varchar( 30 ) NOT NULL
Date_created datetime NOT NULL
Date_modified datetime
Last_Modified_Load_Audit_uid int
Protrak_number varchar( 20 )
Ops_Completion_Date datetime
Protocol_type varchar( 30 )
Data_Reuse_Protocol varchar( 20 )
Indexes
PK_Protocol_uid primary key ( Protocol_uid)
UQ_tblProtocol_Protocol_number unique ( Protocol_number)
_dta_index_Protocol_11_466100701__K1_K2_K6_129 ( Protocol_uid, Protocol_number, Is_Terminated)
_dta_index_Protocol_11_466100701__K2_K6_1_129 ( Protocol_number, Is_Terminated)
_dta_index_Protocol_ProtocolUidStartEndDate ( Protocol_uid, Ops_Completion_Date, Termination_data_termination_date)


Table Protocol_Subject
Protocol_uid bigint NOT NULL
Subject_id bigint NOT NULL
Protocol_number varchar( 20 ) NOT NULL
Status varchar( 20 ) NOT NULL
Start_date datetime
Resolved_date datetime
Appl_Source_CD varchar( 30 ) NOT NULL
Date_created datetime NOT NULL
Date_modified datetime
Last_Modified_Load_Audit_uid int
Treatment_Arm varchar( 256 )
Adverse_Event varchar( 256 )
Outcome varchar( 256 )
Attribution_Status varchar( 20 )
Attribution_Precedence smallint NOT NULL
Indexes
PK_Protocol_uid_SubjectID primary key ( Protocol_uid, Subject_id)
_dta_index_Protocol_Subject_11_1291151645__K14_2_3_5 ( Attribution_Status)
_dta_index_Protocol_Subject_11_1291151645__K14_K2_K3_K1 ( Attribution_Status, Subject_id, Protocol_number, Protocol_uid)
_dta_index_Protocol_Subject_11_1291151645__K3_K14_K2_K1_K5 ( Protocol_number, Attribution_Status, Subject_id, Protocol_uid, Start_date)
_dta_index_Protocol_Subject_11_1291151645__K3_K14_K5_2 ( Protocol_number, Attribution_Status, Start_date)
_dta_index_Protocol_Subject_SubjectId ( Subject_id)
IDX_dta_ProtocolSubject_ProtocolUid ( Protocol_uid)
IDX_ProtocolSubject_ProtocolNumber ( Protocol_number)
Foreign Keys
FK_Protocol_Subject_Protocol ( Protocol_uid ) ref Protocol ( Protocol_uid )
FK_Protocol_Subject_Subject ( Subject_id ) ref Subject ( UID )


Table Protocol_Subject_Hist
Protocol_uid bigint NOT NULL
Subject_id bigint NOT NULL
Sequence_Num smallint NOT NULL
Protocol_number varchar( 20 ) NOT NULL
Status varchar( 20 ) NOT NULL
Treatment_Arm varchar( 256 )
Adverse_Event varchar( 256 )
Outcome varchar( 256 )
Attribution_Status varchar( 20 )
Attribution_Precedence smallint NOT NULL
Start_date datetime
Resolved_date datetime
Appl_Source_CD varchar( 30 ) NOT NULL
Date_created datetime NOT NULL
Date_modified datetime
Last_Modified_Load_Audit_uid int
Indexes
PK_Protocol_uid_SubjectID_SequenceNum primary key ( Protocol_uid, Subject_id, Sequence_Num)
Foreign Keys
FK_Protocol_Subject_Hist_Protocol ( Protocol_uid ) ref Protocol ( Protocol_uid )
FK_Protocol_Subject_Hist_Subject ( Subject_id ) ref Subject ( UID )


Table RED_Ancestor_Descendant_Identity
Ancestor_Concept_GID bigint NOT NULL
Descendant_Concept_GID bigint NOT NULL
Ancestor_Concept varchar( 20 ) NOT NULL
Ancestor_Preferred_name varchar( 255 ) NOT NULL
Descendant_Concept varchar( 20 ) NOT NULL
Descendant_Preferred_name varchar( 255 ) NOT NULL
Indexes
PK_RED_Ancestor_Descendant_Identity primary key ( Ancestor_Concept_GID, Descendant_Concept_GID)
IDX_RED_Ancestor_Descendant_Identity_AncestorDescendantConceptCode ( Ancestor_Concept, Descendant_Concept)
IDX_RED_Ancestor_Descendant_Identity_AncestorDescendantPreferredName ( Ancestor_Preferred_name, Descendant_Preferred_name)
IDX_RED_Ancestor_Descendant_Identity_DescendantConceptCode ( Descendant_Concept)


Table RED_Ancestor_Descendant_Identity_Data
Ancestor_Concept_GID bigint NOT NULL
Descendant_Concept_GID bigint NOT NULL
Ancestor_Concept varchar( 20 ) NOT NULL
Ancestor_Preferred_name varchar( 255 ) NOT NULL
Descendant_Concept varchar( 20 ) NOT NULL
Descendant_Preferred_name varchar( 255 ) NOT NULL
Indexes
PK_RED_Ancestor_Descendant_Identity_Data primary key ( Ancestor_Concept_GID, Descendant_Concept_GID)
IDX_RED_AncestorDescendantIdentityData_DescendantConceptCode ( Descendant_Concept)
IDX_REDAncestorDescendantIdentityData_AncestorDescendantConceptCode ( Ancestor_Concept, Descendant_Concept)
IDX_REDAncestorDescendantIdentityData_AncestorDescendantPreferredName ( Ancestor_Preferred_name, Descendant_Preferred_name)


Table RED_Concept_Path
path_id bigint NOT NULL AUTOINCREMENT
code00 varchar( 20 )
name00 varchar( 255 )
code01 varchar( 20 )
name01 varchar( 255 )
code02 varchar( 20 )
name02 varchar( 255 )
code03 varchar( 20 )
name03 varchar( 255 )
code04 varchar( 20 )
name04 varchar( 255 )
code05 varchar( 20 )
name05 varchar( 255 )
code06 varchar( 20 )
name06 varchar( 255 )
code07 varchar( 20 )
name07 varchar( 255 )
code08 varchar( 20 )
name08 varchar( 255 )
code09 varchar( 20 )
name09 varchar( 255 )
code10 varchar( 20 )
name10 varchar( 255 )
code11 varchar( 20 )
name11 varchar( 255 )
code12 varchar( 20 )
name12 varchar( 255 )
code13 varchar( 20 )
name13 varchar( 255 )
code14 varchar( 20 )
name14 varchar( 255 )
code15 varchar( 20 )
name15 varchar( 255 )
code16 varchar( 20 )
name16 varchar( 255 )
code17 varchar( 20 )
name17 varchar( 255 )
code18 varchar( 20 )
name18 varchar( 255 )
code19 varchar( 20 )
name19 varchar( 255 )
code20 varchar( 20 )
name20 varchar( 255 )
code21 varchar( 20 )
name21 varchar( 255 )
code22 varchar( 20 )
name22 varchar( 255 )
code23 varchar( 20 )
name23 varchar( 255 )
code24 varchar( 20 )
name24 varchar( 255 )
code25 varchar( 20 )
name25 varchar( 255 )
code varchar( 20 )
name varchar( 255 )
synonyms varchar
Indexes
PK_RED_Concept_Path_path_id primary key ( path_id)
IDX_Code ( code)
IDX_Code00 ( code00)
IDX_Code01 ( code01)
IDX_Code02 ( code02)
IDX_Code03 ( code03)
IDX_Code04 ( code04)
IDX_Code05 ( code05)
IDX_Code06 ( code06)
IDX_Code07 ( code07)
IDX_Code08 ( code08)
IDX_Code09 ( code09)
IDX_Code10 ( code10)
IDX_Code11 ( code11)
IDX_Code12 ( code12)
IDX_Code13 ( code13)
IDX_Code14 ( code14)
IDX_Code15 ( code15)
IDX_Code16 ( code16)
IDX_Code17 ( code17)
IDX_Code18 ( code18)
IDX_Code19 ( code19)
IDX_Code20 ( code20)
IDX_Code21 ( code21)
IDX_Code22 ( code22)
IDX_Code23 ( code23)
IDX_Code24 ( code24)
IDX_Code25 ( code25)


Table RED_Concepts
Concept_id int NOT NULL
Concept_code varchar( 20 ) NOT NULL
Concept_name varchar( 255 ) NOT NULL
Concept_kind varchar( 255 )
Date_created datetime NOT NULL
RED_version varchar( 5 )
Indexes
PK_Concept_id primary key ( Concept_id)
IDX_RED_Concepts_ConceptCode_ConceptName ( Concept_code)


Table RED_Displayable_AncestorDescendant
Ancestor_Concept_GID bigint NOT NULL
Descendant_Concept_GID bigint NOT NULL
Indexes
IDX_RED_Displayable_AncestorDescendant_Descendant ( Ancestor_Concept_GID, Descendant_Concept_GID)


Table RED_Lookup
RCL_ID bigint NOT NULL
Data_Source varchar( 800 ) NOT NULL
Domain varchar( 800 ) NOT NULL
Local_Code varchar( 800 ) NOT NULL
Concept_Code varchar( 50 ) NOT NULL
Concept_Name varchar( 256 ) NOT NULL
Concept_GID bigint NOT NULL
Concept_Property_ID bigint NOT NULL
Concept_Property_Type char( 10 ) NOT NULL
Extra_Code varchar( 800 )
Indexes
PK_RED_Lookup_RCL_ID primary key ( RCL_ID)


Table RED_Properties
Concept_code varchar( 20 ) NOT NULL
Property_id int NOT NULL
Property_type varchar( 20 ) NOT NULL
Property_name varchar( 255 ) NOT NULL
Source_system varchar( 255 )
Date_created datetime NOT NULL
RED_version varchar( 5 )
Indexes
PK_Concept_code_Property primary key ( Concept_code, Property_id, Property_type)


Table RED_Relation
Parent_Concept varchar( 20 ) NOT NULL
Child_Concept varchar( 20 ) NOT NULL
Parent_Preferred_name varchar( 255 ) NOT NULL
Child_Preferred_name varchar( 255 ) NOT NULL
Indexes
PK_Parent_Child_Concept primary key ( Parent_Concept, Child_Concept)


Table RED_SearchDomain
RED_Code varchar( 20 ) NOT NULL
Domain varchar( 800 ) NOT NULL
Short_Name varchar( 50 )


Table RED_SearchTable
RowID bigint NOT NULL AUTOINCREMENT
Concept_GID bigint NOT NULL
Concept_Code varchar( 20 )
Search_Value varchar( 900 )
Preferred_Name varchar( 900 )
Display_Class bit
Record_Count bigint
SYN int NOT NULL
Indexes
PK_RED_SearchTeable_RowID primary key ( RowID)
IDX_RED_SearchTeable_SearchValue ( Search_Value)


Table Status_Lookup
Value int NOT NULL
Description varchar( 500 )
Indexes
PK_Status_Lookup_Value primary key ( Value)


Table Subject
UID bigint NOT NULL AUTOINCREMENT
MRN varchar( 30 ) NOT NULL
Encrypted_MRN varbinary( 256 ) NOT NULL
First_name varchar( 50 )
Encrypted_first_name varbinary( 256 )
Last_name varchar( 50 )
Encrypted_last_name varbinary( 256 )
Middle_name varchar( 50 )
Encrypted_middle_name varbinary( 256 )
Display_name varchar( 100 )
Encrypted_display_name varbinary( 256 )
Religion varchar( 30 )
Religion_CONCEPT varchar( 20 )
Race varchar( 30 )
Race_CONCEPT varchar( 20 )
Gender varchar( 30 )
Gender_CONCEPT varchar( 20 )
Marital_status varchar( 30 )
Marital_status_CONCEPT varchar( 20 )
Language varchar( 30 )
Language_CONCEPT varchar( 20 )
Occupation varchar( 30 )
Occupation_CONCEPT varchar( 20 )
Ethnic_group varchar( 30 )
Ethnic_group_CONCEPT varchar( 20 )
Address_line1 varchar( 255 )
Encrypted_address_line1 varbinary( 256 )
Address_line2 varchar( 255 )
Encrypted_address_line2 varbinary( 256 )
Country_division_code varchar( 30 )
City varchar( 30 )
Encrypted_city varbinary( 256 )
Zip varchar( 20 )
Encrypted_zip varbinary( 256 )
Country_code varchar( 20 )
Phone_number varchar( 30 )
Encrypted_phone_number varbinary( 256 )
Area_code varchar( 30 )
Encrypted_area_code varbinary( 256 )
Birth_city varchar( 30 )
Encrypted_birth_city varbinary( 256 )
Birth_country varchar( 30 )
Birth_country_division_code varchar( 30 )
Death_indicator bit
Date_of_birth datetime
Encrypted_date_of_birth varbinary( 256 )
Date_of_death datetime
Encrypted_date_of_death varbinary( 256 )
APPL_Source_CD varchar( 30 ) NOT NULL
Date_created datetime NOT NULL
Date_modified datetime
External_UID varchar( 30 )
Subject_ETL_ID varchar( 20 )
Token varchar( 20 )
Last_Modified_Load_Audit_uid int
Status int DEFO 0
Blood_Type varchar( 2 )
Rh_Factor char( 1 )
Is_VIP bit DEFO 0
Death_Confidence tinyint
Death_Source_CONCEPT varchar( 20 )
Indexes
PK_Subject_UID primary key ( UID)
UQ_Subject_MRN unique ( MRN)
_dta_index_Subject_11_2105058535__K1_K10_K2 ( UID, Display_name, MRN)
_dta_index_Subject_11_2105058535__K21_K1_K15_K25_K17_K19 ( Language_CONCEPT, UID, Race_CONCEPT, Ethnic_group_CONCEPT, Gender_CONCEPT, Marital_status_CONCEPT)
_dta_index_Subject_UID_MRN_DisplayName ( UID, MRN)
IDX_Subject_EthnicGroupCONCEPT ( Ethnic_group_CONCEPT)
IDX_Subject_GenderCONCEPT ( Gender_CONCEPT)
IDX_Subject_LanguageCONCEPT ( Language_CONCEPT)
IDX_Subject_MaritalStatusCONCEPT ( Marital_status_CONCEPT)
IDX_Subject_RaceCONCEPT ( Race_CONCEPT)
Foreign Keys
FK_Subject_StatusLookup_Status ( Status ) ref Status_Lookup ( Value )


Table Subject_Exceptions
ID int NOT NULL AUTOINCREMENT
Subject_UID bigint NOT NULL
Last_name varchar( 100 )
First_name varchar( 100 )
Status int
Indexes
PK_Subject_Exceptions_ID primary key ( ID)
Foreign Keys
FK_SubjectExceptions_StatusLookup_Status ( Status ) ref Status_Lookup ( Value )


Table Subject_Hist
Subject_id bigint NOT NULL
Report_date datetime NOT NULL
MRN varchar( 30 ) NOT NULL
Encrypted_MRN varbinary( 256 ) NOT NULL
First_name varchar( 50 )
Encrypted_first_name varbinary( 256 )
Last_name varchar( 50 )
Encrypted_last_name varbinary( 256 )
Middle_name varchar( 50 )
Encrypted_middle_name varbinary( 256 )
Display_name varchar( 100 )
Encrypted_display_name varbinary( 256 )
Religion varchar( 30 )
Religion_CONCEPT varchar( 20 )
Race varchar( 30 )
Race_CONCEPT varchar( 20 )
Gender varchar( 30 )
Gender_CONCEPT varchar( 20 )
Marital_status varchar( 30 )
Marital_status_CONCEPT varchar( 20 )
Language varchar( 30 )
Language_CONCEPT varchar( 20 )
Occupation varchar( 30 )
Occupation_CONCEPT varchar( 20 )
Ethnic_group varchar( 30 )
Ethnic_group_CONCEPT varchar( 20 )
Address_line1 varchar( 255 )
Encrypted_address_line1 varbinary( 256 )
Address_line2 varchar( 255 )
Encrypted_address_line2 varbinary( 256 )
Country_division_code varchar( 30 )
City varchar( 30 )
Encrypted_city varbinary( 256 )
Zip varchar( 20 )
Encrypted_zip varbinary( 256 )
Country_code varchar( 20 )
Phone_number varchar( 30 )
Encrypted_phone_number varbinary( 256 )
Area_code varchar( 30 )
Encrypted_area_code varbinary( 256 )
Birth_city varchar( 30 )
Encrypted_birth_city varbinary( 256 )
Birth_country varchar( 30 )
Birth_country_division_code varchar( 30 )
Death_indicator bit
Date_of_birth datetime
Encrypted_date_of_birth varbinary( 256 )
Date_of_death datetime
Encrypted_date_of_death varbinary( 256 )
APPL_Source_CD varchar( 30 ) NOT NULL
Date_created datetime NOT NULL
Date_modified datetime
External_UID varchar( 30 )
Subject_ETL_ID varchar( 20 )
Token varchar( 20 )
Last_Modified_Load_Audit_uid int
Blood_Type varchar( 2 )
Rh_Factor char( 1 )
Status int
Death_Confidence tinyint
Death_Source_CONCEPT varchar( 20 )
Is_VIP bit
Indexes
PK_SubjectID_ReportDate primary key ( Subject_id, Report_date)
Foreign Keys
FK_Subject_Hist_Subject ( Subject_id ) ref Subject ( UID )


Table Subject_IDs
Row_ID bigint NOT NULL AUTOINCREMENT
Subject_UID bigint NOT NULL
Alternate_ID varchar( 40 ) NOT NULL
Assigning_Authority varchar( 50 ) NOT NULL
ID_Type varchar( 30 ) NOT NULL
ID_Status varchar( 15 ) NOT NULL
Start_Date date( 10 )
End_Date date( 10 )
Date_created datetime NOT NULL
Date_modified datetime
Last_Modified_Load_Audit_uid int
Indexes
PK_Subject_IDs_Row_ID primary key ( Row_ID)
Foreign Keys
FK_Subject_IDs_Subject_UID ( Subject_UID ) ref Subject ( UID )


Table Subject_IDs_Hist
Row_ID bigint NOT NULL
Report_Date datetime NOT NULL
Subject_UID bigint NOT NULL
Alternate_ID varchar( 40 ) NOT NULL
Assigning_Authority varchar( 50 ) NOT NULL
ID_Type varchar( 30 ) NOT NULL
ID_Status varchar( 15 ) NOT NULL
Start_Date date( 10 )
End_Date date( 10 )
Date_created datetime NOT NULL
Date_modified datetime
Last_Modified_Load_Audit_uid int
Indexes
PK_SubjectIDsHist_RowID_ReportDate primary key ( Row_ID, Report_Date)
Foreign Keys
FK_SubjectIDsHist_Subject_UID ( Subject_UID ) ref Subject ( UID )