Database schema

Country data

Table "public.product_cases_per_country"

Column	Type	Collation	Nullable	Default
high level code	bigint
country	text
value	bigint

Table "public.substance_cases_per_country"

Column	Type	Collation	Nullable	Default
high level code	bigint
country	text
value	bigint

All line listings contain field names on the first row. These field names are mapped to convenient database table column names for better readability. This table contains the mapping of the fields to columns plus a description.

CSV field name	Database column name	Description
EU Local Number	`local_number`	The identifier of the individual case safety repport (ICSR)
Report Type	`report_type`	the value of this field is always "Spontanous"
EV Gateway Receipt Date	`report_date`	Date/time the report was issued
Primary Source Qualification	`qualification`	Can be "Healthcare Professional", "Non Healthcare Professional" or "Not Specified"
Primary Source Country for Regulatory Purposes	`country`	Can be "European Economic Area" "Non European Economic Area" or "Not Specified"
Literature Reference	table `import.___literature`
Patient Age Group	`age_group_lower_months` `age_group_upper_months`	split in lower and upper values in months as this is the lowest resolution present f.i. for children aged 0 to 2 months. Calculating the lower and upper age value allows measuring withing groups as the age groups are sometimes shifted.
Patient Age Group (as per reporter)	N/A	All rows in the CSV files contained the value "Not specified" so we skipped it
Parent Child Report	`is_child_report`	Can be `true` or `false`
Patient Sex	`sex`	Can be "Female" or "Male"
Reaction List PT (Duration – Outcome - Seriousness Criteria)	table `import.___reaction`
Suspect/interacting Drug List (Drug Char - Indication PT - Action taken - [Duration - Dose - Route])	table `import.___subject_drug_list`
Concomitant/Not Administered Drug List (Drug Char - Indication PT - Action taken - [Duration - Dose - Route])	table `import.___concomitant_drug_list`
ICSR Form	N/A	Skipped because it can be reconstructed from the `local_number`

Products

Table "import.products"

This table contains the product_id used in the other product related tables and queries and a human readable name.

Column	Type	Collation	Nullable	Default
product_id	bigint
name	text

Table "import.product"

Column	Type	Collation	Nullable	Default
product_id	integer
local_number	character varying
report_type	character varying
report_date	date
qualification	character varying
country	character varying
age_group_lower_months	smallint
age_group_upper_months	smallint
age_group_reporter	character varying
is_child_report	boolean
sex	character varying
serious	boolean

Table "import.product_reaction"

Column	Type	Collation	Nullable	Default
product_id	integer
local_number	character varying
report_date	date
reaction	character varying
duration	character varying
outcome	character varying
seriousness_criteria	character varying

Table "import.product_literature"

Column	Type	Collation	Nullable	Default
product_id	integer
local_number	character varying
report_date	date
literature	character varying

Table "import.product_concomitant_drug_list"

Column	Type	Collation	Nullable	Default
product_id	integer
local_number	character varying
report_date	date
drugs	character varying
characteristic	character varying
indication	character varying
action	character varying
duration	character varying
dose	character varying
route	character varying

Table "import.product_subject_drug_list"

Column	Type	Collation	Nullable	Default
product_id	integer
local_number	character varying
report_date	date
drugs	character varying
characteristic	character varying
indication	character varying
action	character varying
duration	character varying
dose	character varying
route	character varying

Substances

Table "import.substances"

This table contains the substance_id used in the other substance related tables and queries and a human readable name.

Column	Type	Collation	Nullable	Default
substance_id	bigint
name	text

Table "import.substance"

Column	Type	Collation	Nullable	Default
substance_id	integer
local_number	character varying
report_type	character varying
report_date	date
qualification	character varying
country	character varying
age_group_lower_months	smallint
age_group_upper_months	smallint
age_group_reporter	character varying
is_child_report	boolean
sex	character varying
serious	boolean

Table "import.substance_reaction"

Column	Type	Collation	Nullable	Default
substance_id	integer
local_number	character varying
report_date	date
reaction	character varying
duration	character varying
outcome	character varying
seriousness_criteria	character varying

Table "import.substance_literature"

Column	Type	Collation	Nullable	Default
substance_id	integer
local_number	character varying
report_date	date
literature	character varying

Table "import.substance_concomitant_drug_list"

Column	Type	Collation	Nullable	Default
substance_id	integer
local_number	character varying
report_date	date
drugs	character varying
characteristic	character varying
indication	character varying
action	character varying
duration	character varying
dose	character varying
route	character varying

Table "import.substance_subject_drug_list"

Column	Type	Collation	Nullable	Default
substance_id	integer
local_number	character varying
report_date	date
drugs	character varying
characteristic	character varying
indication	character varying
action	character varying
duration	character varying
dose	character varying
route	character varying

OpenEMA documentation