Canada Vigilance Adverse Reaction Online Database - Data Structure

Although the Canada Vigilance Adverse Reaction Online Database is a relational database, there is a requirement to provide the data to users in a common format; therefore the data has been extracted into a flat file format. All files are dollar ($) sign delimited enclosed in "quotes".

The data extract is comprised of 11 files that contain raw data extracted from the Canada Vigilance Adverse Reaction Online Database. It does not include the scripts (programming) used to produce the online version of the Database.

The technical information below is provided for users who are familiar with working with databases or spreadsheets in ASCII format;

Reports.txt

Purpose: This table provides the information about reports and patients.
Attribute Logical Name Attribute Physical Name Mandatory Date Type (Length)
Adverse Reaction Report (AER) Number (6 digits) REPORT_ID Yes NUMBER
Adverse Reaction Report (AER) Number (9 digits) REPORT_NO Yes VARCHAR2(9)
Adverse Reaction Report (AER) Version Number VERSION_NO Yes NUMBER(3)
Latest Received Date DATRECEIVED No DATE
Initial Received Date DATINTRECEIVED No DATE
Market Authorisation Holder (MAH) Adverse Reaction Report (AER) Number MAH_NO No VARCHAR2(20)
Type of Report Code REPORT_TYPE_CODE No VARCHAR2(9)
Type of Report - English REPORT_TYPE_ENG No VARCHAR2(60)
Type of Report - French REPORT_TYPE_FR No VARCHAR2(60)
Gender Code GENDER_CODE No VARCHAR2(9)
Gender - English GENDER_ENG No VARCHAR2(60)
Gender - French GENDER_FR No VARCHAR2(60)
Age AGE No NUMBER(6,3)
Age in Years AGE_Y No NUMBER(6,3)
Age Unit - English AGE_UNIT_ENG No VARCHAR2(60)
Age Unit - French AGE_UNIT_FR No VARCHAR2(60)
Report Outcome Code OUTCOME_CODE No VARCHAR2(9)
Report Outcome - English OUTCOME_ENG No VARCHAR2(60)
Report Outcome - French OUTCOME_FR No VARCHAR2(60)
Weight WEIGHT No NUMBER(7,3)
Weight Unit - English WEIGHT_UNIT_ENG No VARCHAR2(60)
Weight Unit - French WEIGHT_UNIT_FR No VARCHAR2(60)
Height HEIGHT No NUMBER(7,3)
Height Unit - English HEIGHT_UNIT_ENG No VARCHAR2(60)
Height Unit - French HEIGHT_UNIT_FR No VARCHAR2(60)
Serious Adverse Reaction Code SERIOUSNESS_CODE No VARCHAR2(9)
Serious Adverse Reaction - English SERIOUSNESS_ENG No VARCHAR2(60)
Serious Adverse Reaction -French SERIOUSNESS_FR No VARCHAR2(60)
Death DEATH No VARCHAR2(9)
Disability DISABILITY No VARCHAR2(9)
Congenital Anomaly CONGENITAL_ANOMALY No VARCHAR2(9)
Life Threatening LIFE_THREATENING No VARCHAR2(9)
Hospitalization Required HOSP_REQUIRED No VARCHAR2(9)
Other Medically Important Condition OTHER_MEDICALLY_IMP_COND No VARCHAR2(9)
Reporter Type - English REPORTER_TYPE_ENG No VARCHAR2(60)
Reporter Type - French REPORTER_TYPE_FR No VARCHAR2(60)
Report Source Code SOURCE_CODE No VARCHAR2(9)
Report Source - English SOURCE_ENG No VARCHAR2(60)
Report Source - French SOURCE_FR No VARCHAR2(60)
E2B Safety Report ID Number
E2B_IMP_SAFETYREPORT_ID
No
VARCHAR2(100)
Authority Number
AUTHORITY_NUMB
No
VARCHAR2(100)
Company Number
COMPANY_NUMB 
No
VARCHAR2(100)

Drug_Product.txt

Purpose: This table provides the information about all drug names and their codes.
Attribute Logical Name Attribute Physical Name Mandatory Date Type (Length)
Drug Product Identifier DRUG_PRODUCT_ID Yes NUMBER(8)
Brand Name DRUGNAME No VARCHAR2(100)

Drug_Product_Ingredients.txt

Purpose: This table provides the information about the active ingredients associated with all drugs.
Attribute Logical Name Attribute Physical Name Mandatory Date Type (Length)
Drug Product - Active Ingredient Identifier DRUG_PRODUCT_INGREDIENT_ID Yes NUMBER(15)
Drug Product Identifier DRUG_PRODUCT_ID No NUMBER(8)
Brand Name DRUGNAME No VARCHAR2(100)
Active Ingredient Identifier ACTIVE_INGREDIENT_ID No NUMBER(8)
Active Ingredient Name ACTIVE_INGREDIENT_NAME No VARCHAR2(120)

Reactions.txt

Purpose: This table provides the information about the reaction terms associated with report.
Attribute Logical Name Attribute Physical Name Mandatory Date Type (Length)
Reaction Identifier REACTION_ID Yes NUMBER
Adverse Reaction Report (AER) Number (9 digits) REPORT_ID No NUMBER
Reaction Duration DURATION No NUMBER(9,3)
Reaction Duration Unit - English DURATION_UNIT_ENG No VARCHAR2(60)
Reaction Duration Unit - French DURATION_UNIT_FR No VARCHAR2(60)
Adverse Reaction Term - English PT_NAME_ENG No VARCHAR2(250)
Adverse Reaction Term - French PT_NAME_FR No VARCHAR2(250)
System Organ Class - English SOC_NAME_ENG No VARCHAR2(250)
System Organ Class - French SOC_NAME_FR No VARCHAR2(250)
MedDRA Version MEDDRA_VERSION No VARCHAR2(9)

Outcome_LX.txt

Purpose: This table provides the information about the outcome presentation text associated with the outcome code.
Attribute Logical Name Attribute Physical Name Mandatory Date Type (Length)
Outcome Identifier OUTCOME_LX_ID Yes NUMBER(4)
Report Outcome Code OUTCOME_CODE No VARCHAR2(3)
Report Outcome - English OUTCOME_EN No VARCHAR2(60)
Report Outcome - French OUTCOME_FR No VARCHAR2(60)

Gender_LX.txt

Purpose: This table provides the information about the gender presentation text associated with the gender code.
Attribute Logical Name Attribute Physical Name Mandatory Date Type (Length)
Gender Identifier GENDER_LX_ID Yes NUMBER(7)
Gender Code GENDER_CODE No VARCHAR2(9)
Gender - English GENDER_EN No VARCHAR2(60)
Gender - French GENDER_FR No VARCHAR2(60)

Report_Type_LX.txt

Purpose: This table provides the information about the report type presentation text associated with the report type code.
Attribute Logical Name Attribute Physical Name Mandatory Date Type (Length)
Report Type Identifier REPORT_TYPE_LX_ID Yes NUMBER(7)
Report Type Code REPORT_TYPE_CODE No VARCHAR2(9)
Report Type - English REPORT_TYPE_EN No VARCHAR2(60)
Report Type - French REPORT_TYPE_FR No VARCHAR2(60)

Seriousness_LX.txt

Purpose: This table provides the information about the report seriousness presentation text associated with the report seriousness code.
Attribute Logical Name Attribute Physical Name Mandatory Date Type (Length)
Serious Report Identifier SERIOUSNESS_LX_ID Yes NUMBER(7)
Serious Adverse Reaction Code SERIOUSNESS_CODE No VARCHAR2(9)
Serious Adverse Reaction - English SERIOUSNESS_EN No VARCHAR2(60)
Serious Adverse Reaction - French SERIOUSNESS_FR No VARCHAR2(60)

Source_LX.txt

Purpose: This table provides the information about the report source presentation text associated with the report source code.
Attribute Logical Name Attribute Physical Name Mandatory Date Type (Length)
Report Source Identifier SOURCE_LX_ID Yes NUMBER(7)
Report Source Code SOURCE_CODE No VARCHAR2(9)
Report Source - English SOURCE_EN No VARCHAR2(60)
Report Source - French SOURCE_FR No VARCHAR2(60)

Report_Links_LX.txt

Purpose: This table provides the information about the linked/duplicate reports presentation text associated with the code.
Attribute Logical Name Attribute Physical Name Mandatory Date Type (Length)
Report Link Identifier REPORT_LINK_ID Yes NUMBER
Adverse Reaction Report (AER) Number (6 digits) REPORT_ID Yes NUMBER
Record Type - English RECORD_TYPE_ENG No VARCHAR2(9)
Record Type - French RECORD_TYPE_FR No VARCHAR2(7)
Duplicate or Linked Adverse Reaction Report (AER) Number REPORT_LINK_NO No VARCHAR2(18)

Report_Drug.txt

Purpose: This table provides the information about drugs associated with specific reports.
Attribute Logical Name Attribute Physical Name Mandatory Date Type (Length)
Report Drug Identifier REPORT_DRUG_ID Yes NUMBER(15)
Adverse Reaction Report (AER) Number (6 digits) REPORT_ID No NUMBER(15)
Drug Product Identifier DRUG_PRODUCT_ID No NUMBER(8)
Brand Name DRUGNAME No VARCHAR2(100)
Health Product Role - English DRUGINVOLV_ENG No VARCHAR2(60)
Health Product Role - French DRUGINVOLV_FR No VARCHAR2(60)
Route of Administration - English ROUTEADMIN_ENG No VARCHAR2(60)
Route of Administration - French ROUTEADMIN_FR No VARCHAR2(60)
Dose UNIT_DOSE_QTY No NUMBER(20,9)
Dose Unit - English DOSE_UNIT_ENG No VARCHAR2(60)
Dose Unit - French DOSE_UNIT_FR No VARCHAR2(60)
Frequency FREQUENCY No NUMBER(3)
Frequency Time FREQ_TIME No NUMBER(10,5)
Frequency Time - English FREQUENCY_TIME_ENG No VARCHAR2(30)
Frequency Time - French FREQUENCY_TIME_FR No VARCHAR2(30)
Frequency Unit - English FREQ_TIME_UNIT_ENG No VARCHAR2(60)
Frequency Unit - French FREQ_TIME_UNIT_FR No VARCHAR2(60)
Therapy Duration THERAPY_DURATION No NUMBER(10,5)
Therapy Duration - English THERAPY_DURATION_UNIT_ENG No VARCHAR2(60)
Therapy Duration - French THERAPY_DURATION_UNIT_FR No VARCHAR2(60)
Dosage Form - English DOSAGEFORM_ENG No VARCHAR2(60)
Dosage Form - French DOSAGEFORM_FR No VARCHAR2(60)

Report_Drug_Indication.txt

Purpose: This table provides the information about indications associated with specific reports.
Attribute Logical Name Attribute Physical Name Mandatory Date Type (Length)
Report Drug Identifier REPORT_DRUG_ID Yes NUMBER(15)
Adverse Reaction Report (AER) Number (6 digits) REPORT_ID No NUMBER(15)
Drug Product Identifier DRUG_PRODUCT_ID No NUMBER(8)
Brand Name DRUGNAME No VARCHAR2(100)
Indication - English INDICATION_NAME_ENG No VARCHAR2(250)
Indication - French INDICATION_NAME_FR No VARCHAR2(250)

Literature_Reference.txt

Purpose: This table provides the information about literature references.
Attribute Logical Name Attribute Physical Name Mandatory Date Type (Length)
Adverse Reaction Report (AER) Number (6 digits) REPORT_ID Yes NUMBER
Adverse Reaction Report (AER) Number (9 digits) REPORT_NO Yes VARCHAR2(9)
Adverse Reaction Report (AER) Version Number VERSION_NO Yes NUMBER(3)
Literature Reference Sequence SEQ_LITERATURE No NUMBER(4)
Literature reference LITERATURE_REFERENCE No VARCHAR2(1000)

Page details

Date modified: