A short ChEMBL schema diagram

ChEMBL is a database of drug targets, molecules, and measurements of the activity of the molecules on targets. It has an online interface, but I’m more of a SQL person, and so I download the PostgreSQL or SQLite copies of the data.

There is a large and comprehensive schema diagram, supporting descriptions, and an FAQ. But for a quick dip in and out, I’ve made myself the following:

With that you can do queries to fetch all binding assay activities for a particular target with:

select
	td.tid,
	td.pref_name,
	x.standard_type,
	x.standard_relation,
	x.standard_value,
	x.standard_units,
	x.activity_comment,
	x.action_type,
	m.canonical_smiles
from
	target_dictionary td
join assays a on
	a.tid = td.tid
join activities x on
	x.assay_id = a.assay_id
join compound_structures m on
	m.molregno = x.molregno
where
	td.tid = 100956
	and a.assay_type = 'B'
	and a.confidence_score = 9
	and a.assay_organism = 'Homo sapiens'
	and x.standard_relation NOT NULL
;