Health Canada
www.hc-sc.gc.ca
Home
> Drugs & Health Products
> MedEffect Canada
> Adverse Reaction Database
Institutional links
-
Back to
-
Explore...
-
Transparency
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) |
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) |
